Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Grabbing Data from multiple pages and having it on one "Total" excel tab

So I have 5 Total tabs (Total, P&L, Delta, Vega, Theta) for GC, GLD, and OG products. What I need is that numbers for the p&L, Delta, Vega, and Theta) for each product to be put on the total page for the corresponding Vol/P values (See attached spreadsheet). Really new to excel so I could really use some help. I assume this is a formula and/or macro. PLEASE HELP!!

For example,

I need cell "B8" on the PL tab to show up on the Total Tab in cell "B9" .....
I need cell "B9" on the PL tab to show up on the Total Tab in cell "B10"


Post your answer or comment

comments powered by Disqus
I'm having trouble figuring out how to simply take data from multiple
worksheets and list it in one "total" worksheet all within the same workbook.
I want to have this perform automatically as opposed to me using the
copy/paste function and do this manually. All the data will be in the same
layout from page to page. Any help would be appreciated.

I want to take data from multiple cells and display it as one. For
example, we'd have a cell which will have the persons first name and
another cell with the persons last name. Then, we'd have one cell that
would take the first and last name and display it in one cell. How do I
do this? Thanks in advance!

--
djarcadian
------------------------------------------------------------------------
djarcadian's Profile: http://www.excelforum.com/member.php...o&userid=15877
View this thread: http://www.excelforum.com/showthread...hreadid=546851

I want to take data from multiple cells and display it as one. For example, we'd have a cell which will have the persons first name and another cell with the persons last name. Then, we'd have one cell that would take the first and last name and display it in one cell. How do I do this? Thanks in advance!

Hello,
As a heads up I have some experience with Excel and virtually none with VBA.

I have 6 worksheets named Mon,Tues,Wed,Thurs,Fri,and Sat. I need to copy a range of data from each worksheet and compile it into a final worksheet named "DTTracker". The amount of data input into the days worksheets varies from day to day (one day there could be 15 rows of data and another day only 2), but I need to it compile one on top of the other in DTTracker. Any help is greatly appreciated.

I'm having trouble figuring out how to simply take data from multiple
worksheets and list it in one "total" worksheet all within the same workbook.
I want to have this perform automatically as opposed to me using the
copy/paste function and do this manually. All the data will be in the same
layout from page to page. Any help would be appreciated.

Hello all. I am working on a project where I will need to pull data from multiple sheets and place it on a common sheet for reporting purposes.

(I am attaching an example)

I have an input box that asks for a date.
Based on what date is entered, the following data should be copied from the account worksheets and placed on the "Recon" worksheet: the account number ("C2") and the balance (Column D).

For instance, if the user entered 09/12/2007, the account number and the balance in the D Column for the last entry for that date would be copied to the "Recon" worksheet.

I have prepared some code that will prefill the account number and then display the input box which follows below:


	VB:
	
 Recon() 
    Dim recondate As String 
    Dim anyWS As Worksheet 
    Const dSheet = "Recon" 
     
     
    copyToRow = 5 'initialize
     
    For Each anyWS In Worksheets 
        Select Case anyWS.Name 
        Case "Main", "Recon" 
        Case Else 
            Application.ScreenUpdating = False 
             
            Application.DisplayAlerts = False 
             
             'For Each Worksheet In anyWS
            Set rngToCopy = anyWS.Range("C2") 
            rngToCopy.Copy 
            Worksheets(dSheet).Range("A" & copyToRow).PasteSpecial Paste:=xlPasteValuesAndNumberFormats 
             'Worksheets(dSheet).Range("A" & copyToRow).PasteSpecial Paste:=xl.PasteFormats
            copyToRow = copyToRow + rngToCopy.Rows.Count 
             'Case Else
             'do nothing
        End Select 
    Next 
    Application.CutCopyMode = False 
    Worksheets(dSheet).Activate 
     
    recondate = Application.InputBox("Enter Date to Reconcile." & vbNewLine _ 
    & vbNewLine _ 
    & "Format should be MM/DD/YYYY", "Reconciliation Date", vbYesCancel) 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
My question is: How do I search through all the worksheets using the date returned from the input box, locate the balance (in Column D) in the row corresponding to the last entry of the date (since there may be multiple entries for that date per the attached example) and copy that to the "Recon" worksheet?

Thank you in advance for any advice/assistance...and I apologize if I have not explained this properly!

I have a folder full of uniformed web pages (name and design) and I was wondering if it's possible to create a macro that will open up each page, copy specific data from each page, and paste it into an Excel 2002 spreadsheet. Unfortunately, my skills in VBA are very limited at best and I'm not sure if this is doable or is it something I'll have to go into each page and copy the data. So, am I crazy or is this possible?

Hi

I have found the following code that i believe will fetch data from multiple sheets and combine it in to one named "Rollup". This data is fetched from sheets named as per the standard excel format. However my sheets are being imported with the name of the file which they have come from for example:

FR1-22.11.2009.xls
RA12-22.11.2009.xls

The constant that will remain will be the date throughout the sheets names as the number of actual sheets present will vary. How would the code below be edited to reflect this naming convention? assuming the data is to be collated in to "rollup" and how would it be edited to pick out a specific cell or number of cells? at the minute it looks as though it just copies the whole sheet whereas i am looking to copy just specific cells in to "rollup".

Sub Combine()
    Dim J As Integer

    On Error Resume Next
    Sheets(1).Select
    Worksheets.Add ' add a sheet in first place
    Sheets(1).Name = "Rollup"


    ' work through sheets
    For J = 2 To Sheets.Count ' from sheet 2 to last sheet
        Sheets(J).Activate ' make the sheet active
        Range("A1").Select
        Selection.CurrentRegion.Select ' select all cells in this sheets

        ' select all lines except title
        Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select

        ' copy cells selected in the new sheet on last line
        Selection.Copy Destination:=Sheets(1).Range("A65536").End(xlUp)(2)
    Next
End Sub
Thanks for any help

Hi,

I have an undefined number of worksheets that will be created throughout the month. I would like to pull data from a range and compile it on a summary page.

For the attached example - I would like to have all the information from ( A-F) from all worksheets added to a list in the summary page.

I used this marco created by someone, however it has some quirks that dont work for me. It includes the title from one sheet as well as the heading for each sheet. It would be great if they there could be single headings

Can someone guide me in the right direction?

I am trying to read the data from several sheets and post results on one sheet. (not using Piviot tables)
but each sheet needs to list the totals like a pivot table but it must be added using vb (this is my down fall) "Path to the dark side it is"

I have tried some VB scripting to do this (as you can see in my data uploded)
I have made notes in red to denote my problems.

Also I am having issues with passing Variables from a module to a form and from module to module. I have been playing around with this for days

"Help me Obi-Wan kenobi, your my only hope"

Looking to search a range of cells in Sheet1, find specific data and place it
on Sheet2. Does anyone have a way of doing this.

Range of cells in sheet one will be updated periodically and cell range will
grow with it.
--
tech1NJ

Came across this old thread, which was similar to what I needed, but
unfortunately the solutions recommended back then involved doing the
worksheet other ways.

So my question is: If I have a list on one worksheet, which is then copied
to a second worksheet, how do I ensure the second updates if I add new rows
or change the text to the first.

Kind Regards

A Taxed Mind

Previous thread starting Q.
Subject: Copy data into another worksheet and have it update automatically?
3/30/2006 7:55 AM PST

By: Lmarie6 In: microsoft.public.excel.misc

My boss sent me a workbook that at present contains only one sheet, however
he wants me to copy certain data (depending on column A) into a second sheet
and other data into a third sheet, but have it update on sheets 2 and 3 when
the data on sheet1 is changed, added, or deleted. Column A contains a
general classification, and he wants certain classifications on sheet2 and
other classifications on sheet3. There are no formulas, just data. It's
just a big list of materials and their locations and vendors, etc. Can this
be done?

Hello,
I would like to pars out data from multiple clients and display it on a second sheet in a row format with the following line. I am not sure if I would need to create a macro for this or if I can stick to formulas.

Account Number Date Total Equity Total Buys Total Sells Gross Profit/Loss Commission Clearing Fees NFA Fees Total Fees Net Profit/Loss Previous Balance

The original data can be found in this form:

ACCOUNT NUMBER: 5555555
STATEMENT DATE: DEC 25, 2011
BUSINESS INTRODUCED BY

JOHN SMITH
FUNNY LANE
CA, 91455
PAGE 2
TRADE SETTL AT BUY SELL CONTRACT DESCRIPTION EX TRADE PRICE CC DEBIT/CREDIT
12/25/2011 01 1 13 US
12/25/2011 01 1 13 US
12/25/2011 01 13 US
12/25/2011 01 13 US
12/25/2011 01 1 13 US
01 6* 6* GROSS PROFIT OR LOSS US 100.00DR
01 TOTAL US 100.00DR
** USD SEGREGATED **
BEGINNING BALANCE 2,000.00
COMMISSION 15.00DR
CLEARING & EXCHANGE FEES 14.00DR
NFA FEES .50DR
TOTAL FEES 14.50DR
GROSS PROFIT OR LOSS 100.00DR
NET PROFIT/LOSS FROM TRADES 129.50DR
CASH TRANSACTIONS 0.00
ENDING ACCOUNT BALANCE 870.50
TOTAL EQUITY 870.50

…and this is what I need to collect for one client, multiple clients should be added to the each subsequent row:
Account Number Date Total Equity Total Buys Total Sells Gross Profit/Loss Commission Clearing Fees NFA Fees Total Fees Net Profit/Loss Previous Balance
5555555 12/25/2011 870.5 6 6 100.00DR 15.00DR 14.00DR 0.50DR 14.50DR 129.50DR 2000
Using formulas, I figured I can reference “commission” and select data from row D to get the data into sheet 2 however if the item in sheet 1 is on row 86, the data will show in sheet 2 on row 86 rather than row 2.

Sticking with formulas, I figured I would need the following:
-A way to look for a specific word in column A in Sheet 1 and send it to the next line available in sheet 2. Then reference the number found in column X of the same row
-A way to find the next iteration of a word above or below the specific word found in the first item and reference a number found in column y of the same row
As I mentioned I don’t know if it would be better to use a macro for this or not but any suggestions would be appreciated.

Thanks,
Laurent

Private Sub CommandButton4_Click()

ListBox2.RowSource = "R11:R15"

End Sub

Hi,
With this Code and pressing a button i get the data shown on a list box
within a userform from the active worksheet. However, I need it to add the
data from multiple worksheets and show it too me on the userform!

Thanks

Hello everyone,
I have a files that are saved as txt files but open up in excel. Is there a way to create a VBA script that takes specific columns of data from each file and put it in one file. I have attached a sample file. Here is the script I have been working on but it has not worked at all.

Sub Test()
MyPath = "C:Documents and SettingsLimE01My Documentstest"
MyFileName = MyPath & "CP001.txt"
Do While MyFileName <> ""
    Workbooks.Open MyPath & MyFileName
    For Each Sheet In Sheets
        ThisWorkbook.Sheets(1).Cells(65536, 1).End(x1Up).Offset(1, 0) = MyFileName
        Sheet.Range("A8:A68").Copy Destination:=ThisWorkbook.Sheets(1).Cells(65536, 1).End(x1Up).Offset(0, 1)
    Next Sheet
    ActiveWorkbook.Close
    MyFileName = Dir()
Loop
End Sub
Here are the range of data I need. A2:A68 and E2:E68. The files have the same format and are numbered sequentially.

Thank you!

How to collect data from a column and copy it to clipboard with single spaces?

For eg. then I press Button in B3 cell, I want, that result with single spaces: 3 7 6 5 till the end of sheet was copied to clipboard.

I also attached original excel file.

Appreciate any help

A B C 1 Diameter ∅6 ∅8 2 Area 1,41 2.51 3 [Button for macro] [Button for macro] 4 3 5 4 6 7 7 8 8 9 6 10 11 5 5 ... ... ... ...

I am trying to copy data in multiple cells and paste it into one cell - is
this possible and if so how do I do it?

Thanks,

hello, again. im a newbie using excel to hold a database of our clients (auto body shop). one workbook has 50 worksheet (each worksheet is an individual client). added a seperate worksheet (renamed totals) at the end of the workbook to summarize info of the total 50 worksheets within the workbook, such as name, costs, etc. need to pull cell data from each worksheet and place it on the TOTAL worksheet. besides typing everything manually, what formula can i use to grab specific cell data from EACH worksheet and place it on the TOTAL worksheet.

any help is appreciated, thanks!

I am very new to excel VBA. I have a unique problem of copying data from multiple files.
Here it goes, I have a master file named "master.xlsm" and in that file I have a worksheet named "MyData". In that worksheet, I have column G that contains the file names upto row 20(last available data row). One example of a file name is Myfile1.xls. Each of these files have several worksheets, but the names of worksheets that needs to be opened (and data copied) are written in column H. So for example Myfile1.xls and its corresponding worksheet to be used (say "saving1") sits side by side in column G and H. All the files are in one folder.
I can write simple macros, but this seems diffcult to me. I need to copy a range of data from the relevant worksheets and consolidate it in a single worksheet in the master file. The data has to be copied below each sets of data from the worksheets in the order of column H.
As I am very new to VBA, I will appreciate, if the code has some comments that helps me understand it.
Thanks very much

Good day,

First, I'd just like to comment that this forum has really been helpful to me in providing assistance. The information provided has really educated me in excel as well as VBA Macros (though I still have a lot of things to learn).

I've been trying to search the forum about this; however, I have been unsuccessful. I know this has been asked before and I saw a couple of posts related to it; however, it wasn't exactly the format I wanted it to be, I hope you can help me out.

I'd like to clarify that I do not have any VBA Knowledge. I've survived on using the codes I've seen here and apply it to the data that I have.

My goal is to copy data from multiple sheets with the same data structure into a single or master sheet. This is the code that I have to do so:


	VB:
	
 Summarize() 
    Dim ws As Worksheet 
    Dim lastRng As Range 
    Application.ScreenUpdating = False 'speed up code
     
    ThisWorkbook.Sheets("Summary").Rows("2:65536").ClearContents 'clear
     
    For Each ws In ThisWorkbook.Worksheets 
        Set lastRng = ThisWorkbook.Sheets("Summary").Range("A65536").End(xlUp).Offset(1, 0) 
         
        Select Case ws.Name 
        Case "Summary" 'exlude
             'do nothing
        Case Else 
            ws.Activate 
             
             'copy data from individual sheets
            Range("A2", Range("D65536").End(xlUp)).Copy lastRng.Offset(0, 1) 
             
             'add sheet name before data
            For i = 0 To Range("A2", Range("A65536").End(xlUp)).Count - 1 
                lastRng.Offset(i, 0) = ws.Name 
            Next 
             
        End Select 
    Next 
    Application.CutCopyMode = False 'clear clipboard
    Application.ScreenUpdating = True 
    Sheets("Summary").Activate 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
At the same time, I'd like to put a timestamp on the mastersheet to track when the said change were made by using this code on my mastersheet.


	VB:
	
 Range) 
     
    If Target.Cells.Count > 1 Then Exit Sub 
     
    If Not Intersect(Target, Range("A2:A100")) Is Nothing Then 
         
        With Target(1, 6) 
             
            .Value = Now 
             
            .EntireColumn.AutoFit 
             
        End With 
         
    End If 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Everything seems to work fine with a single exception. Every time I update the mastersheet with the consolidation code, it refreshes the whole data including the date when earlier data was encoded. Would it be possible to append the data in such a way that the mastersheet only adds the latest cells from the other sheets. I'm using Excel 2010. I included my sample file for your reference.

Please let me know your thoughts on this if its possible. Any help is very much appreciated.

Hi all,

I'm new to VBA so bare with me. What I need to do is access data from multiple user workbooks which will have the format "name_position" and a sheet named "won" where the data to be copied will be. I then need to paste this to a Workbook named "ALeads" on a worksheet called "leads" However the the data will be updated each month and so copying all the data and pasting will cause a lot of dupes. I would like to check for duplicates in a column named "Company" on the main workbook "Aleads" and avoid pasting if it already exists. Another possible solution is to make the selection based around date but this is going to change month by month, unless there is an option box that could allow the input of the month required (a field that we have in all data sets), before the macro ran. Any help much appreciated,

Thanks,

Tom

I need to move data from "2010" page to page "sheet 1 (2)" so I don't have to double chart.
I want to be able to retrieve data in columns w, x, y, z, ac, and ad, on page 2010 and place them respectively in coloumns I - N on the correct row. (the row that matches the right name)
I dont' know how to do this-I don't know if it complicates things that 2010 has breaks in the data (each month has totals and are broken up. ) can anyone help?
I believe I have xcel 2003 if it matters.
thanks a head of time
Krista

I am using this formula and works great:

=IF(ISBLANK('[Apple Worksheet.xlsx]Apple Worksheet'!B3),"",'[Apple Worksheet.xlsx]Apple Worksheet'!B3)

So it is taking info from apple worksheet/ apple worksheet page cell b3 and put it in this cell on a different page.

I need to have it take from apple Worksheet: apple worksheet sheet AND Apple Worksheet: Juice sheet. Can I even have it do that since it automatically takes information when I put it into the other sheets.

What if there is information in apple worksheet sheet cell b3 and also in the Juice sheet cell b3. Could I have it enter it below on the line.

Hi there,

This was originally going to be a simple Select Case question, but upon introducing my problem I realise that there is likely a much better way of attacking my "big picture".

I know I will likely get told off for the code given that it is basically a series of loops and will be time consuming because of this. However, this is a one-time code that I need to run on this spreadsheet so I can get the data into a "correct" format for future use.

Currently, I have hundreds of sheets that are simialr to the attached TA0632TEST and TA0632TEST2 sheets, each with a differing amount of columns based on the number of work days for that particular month. The code only needs to be performed on sheets starting with the initials of an employee (shown in select case in code).

I've been thinking of better ways to have data entered/managed and had thought it best to have all raw data in a single sheet which can then be filtered/looked up/pivot tabled/custom viewed etc. and all the other fun stuff like how it should be done :-).

I am open to other ideas about how best to get this historic data into a managable format.

The problem I am running into at the moment is that where the Select Case is checking if the sheet name starts with the initials and project number, it doesn't seem to recognise if it is correct (and therefore perform the actions).

I have stepped through the code and when I use the immediate window to manually check:


	VB:
	
 "TA0632*" 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I get True as an answer, but the code goes on to the next case as though it is false.

Here is the code I have so far, which I'm sure can be trimmed down loads:


	VB:
	
 
Dim currCell As Range 
Dim c As Long 
Dim r As Long 
Dim rng 
Dim ws As Worksheet 
Dim skp As String 
Dim LastColumn As Integer 
 
Sub Breakdown() 
    Dim t 
    t = Timer 
     
    For Each ws In ThisWorkbook.Sheets 
        Debug.Print "Current sheet is " & ws.Name 
        CheckSheet 
        If skp = "Skip" Then Goto SkipGetRange 
        GetRange 
        CheckAndTransferValues 
SkipGetRange: 
    Next ws 
    Debug.Print "This macro took " & Format(Round(Timer - t, 2), "00:00:00.00") & " seconds to run." 
End Sub 
 
Sub CheckSheet() 
    Debug.Print ws.Name 
    Select Case ws.Name 
    Case ws.Name Like "MS0632*" 
        Exit Sub 
    Case ws.Name Like "NC0632*" 
        Exit Sub 
    Case ws.Name Like "TA0632*" 
        Exit Sub 
    Case ws.Name Like "ZM0632*" 
        Exit Sub 
    Case ws.Name Like "AM0632*" 
        Exit Sub 
    Case ws.Name Like "AP0632*" 
        Exit Sub 
    Case ws.Name Like "CF0632*" 
        Exit Sub 
    Case ws.Name Like "HT0632*" 
        Exit Sub 
    Case ws.Name Like "BO0632*" 
        Exit Sub 
    Case Else 
        skp = "Skip" 
    End Select 
End Sub 
 
Sub GetRange() 
    With ws 
        If WorksheetFunction.CountA(Cells) > 0 Then 
             'Search for any entry, by searching backwards by Columns.
            LastColumn = .Cells.Find(What:="TOTALS", After:=[A2], SearchOrder:=xlByColumns,
SearchDirection:=xlPrevious).Column 
        End If 
         ' Offset to exclude the TOTALS column from the range
        rng = .Range(Cells(2, LastColumn).Offset(0, -1), .Range("A65536").End(xlUp).Offset(-1, 0)).Address 
        .Range(rng).SpecialCells(xlCellTypeBlanks) = "Blank" 
    End With 
End Sub 
 
Sub CheckAndTransferValues() 
    For Each currCell In Worksheets(ws.Name).Range(rng) 
        Select Case currCell 
        Case currCell.Row = 1 ' If current cell is on row 1, skip to next cell
            Goto NextCurrCell 
        Case currCell.Row = 2 ' If current cell is on row 2, skip to next cell
            Goto NextCurrCell 
        Case currCell.Column = 1 ' If current cell is in column 1, skip to next cell
            Goto NextCurrCell 
             
            Select Case currCell.Value 
            Case Is = "Blank" ' If cell contains the word "Blank"
                Goto NextCurrCell ' Move to the next cell
            Case Is = 0 ' If cell = 0
                Goto NextCurrCell ' Move to the next cell
            Case Is = "" ' If cell is empty
                Goto NextCurrCell ' Move to the next cell
            Case Else 
                c = currCell.Column - 1 ' Set the c value for the column offset
                r = currCell.Row - 2 ' Set the r value for the row offset
                 
                 ' Grab task name from current sheet and copy it to the "Output" Sheet
                Worksheets(ws.Name).currCell.Offset(0, -c).Copy 
                Sheets("Output").Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues 
                 ' Grab date from current sheet and copy it to the "Output" Sheet
                Worksheets(ws.Name).currCell.Offset(-r, 0).Copy 
                Sheets("Output").Range("A65536").End(xlUp).Offset(0, 1).PasteSpecial xlPasteValues 
                 ' Grab number of hours from current sheet and copy it to the "Output" Sheet
                Worksheets(ws.Name).currCell.Copy 
                Sheets("Output").Range("A65536").End(xlUp).Offset(0, 2).PasteSpecial xlPasteValues 
                 ' Get staff name and rate from the current sheet and transfer to the "Output" sheet
                Worksheets(ws.Name).Range("A1").Copy ' Cell containing staff name
                Sheets("Output").Range("A65536").End(xlUp).Offset(0, 3).PasteSpecial xlPasteValues 
                 'Rate
                Worksheets(ws.Name).Range("A2").Copy ' Cell containing staff rate
                Sheets("Output").Range("A65536").End(xlUp).Offset(0, 4).PasteSpecial xlPasteValues 
            End Select 
NextCurrCell: 
        Next currCell 
    End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

I appreciate any thoughts as to the best method, or advise as to why my select case is not working as expected.

Kind regards,
Averil Pretty


No luck finding an answer? You could always try Google.