Help copying data from multiple worksheets to one worksheet.

[ba919]

I am an Internal Auditor for a major retailer. As a job requirement, I have to keep record of any and all projects I've worked on during each month. The company has a single Excel spreadsheet which I complete on a monthly basis. The boss, however, doesn't want me to send each month's spreadsheet of activities seperately though. He wants to see the Year to date list of activities(culmination). I have taken the company spreadsheet, and created an individual worksheet for each Month along with a "Year To Date" worksheet. My problem is I cant figure out how to make Excel copy each row of data from the Monthly worksheets and place on the "Year To Date" page.
Any help with this would greatly be appreciated.

[topswim]

From what I can gather it sounds like you just need to place an equal sign in the cell on the End of Year sheet, go to the monthly sheet that corresponds to the information you need and click on that cell and hit the enter key. Let me know if you need additional or your trying to do something different than this.

Topswim

[ This Message was edited by: topswim on 2002-05-18 07:09 ]

[ba919]

I was looking for a technique in which all I had to do would be to enter the data on the Monthly worksheets,and that data would update to the Year to Date sheet. Thus avoiding excessive keystrokes. I found a link where someone had a similar question. http://www.mrexcel.com/board/viewtop...c=8404&forum=2 The only difference is that I want to enter the data on the 12 worksheets and have it copy to the Main worksheet. Do you think the same code will work? Hope this was clear. Thank You for your response.

[Brian]

See:
http://www.mrexcel.com/board/viewtop...c=8715&forum=2

If you want to keep the months on individual sheets, then you can use fill the below table with the following equation.
C3: = =IF(ISBLANK(INDIRECT($A3&"!"&C$2&$B3)),"-",INDIRECT($A3&"!"&C$2&$B3))

Summary Sheet:
COLA______COLB_____COLC___
1:Month___Row______Data____Data
2Sheet)_(Row)____A_______B
3:Jan_____2________=eqn____=eqn
4:Jan_____3
5:Jan_____4
6:Jan_____5
7:Feb_____6
:Feb_____7
:Feb_____8
:Feb_____9
:Mar_____10
...

This will leave you with blank rows (I use "-" instead of "" to differentiate a cell with a formula from a missung formula), that you can then hide using an autofilter, manually hide, or delete.

To add links to each month sheet, also see
http://www.mrexcel.com/board/viewtop...c=8739&forum=2

[ba919]

Im sorry, I dont understand what you mean when you say I "can use fill the below table with the following equation."
Where do I enter this equation?

[Brian]

Enter the equation in C3
hover your mouse over the black square in the lower left corner, when it changes to a blackcross, then click and hold as you drag accross as many columns as you have.

Then do the same thing to fill the equation down as many rows as you need.

(copy and paste the cel also works)

Brian

[Brian]

Also See http://www.mrexcel.com/board/viewtop...937&forum=2&11

[Nimrod]

How many rows of information do you want from each monthly sheet ?
Is there any gaps in the rows of information from the month sheets ?
Do you just need a set of rows copied from source sheets and appended to the bottom of the summary sheet ... if so this code would make a summary of all source sheets to one target sheet. Note in the code that the Target name is "Sheet16". Just change that to what ever target sheet name you want .
If you want to clear the Target sheet before starting process then delete the single quote from the line 'Sheets("Sheet16").ClearContents

Sub CopyRanges()
' ALL SHEETS SOURCE AND TARGET IN ONE BOOK
'Sheets("Sheet16").ClearContents
TargSheet = "Sheet16"
For Each SrcWs In Worksheets
    With SrcWs
     TargNewBot = 0
    If Not (.Name = TargSheet) Then
        SrcTopRow = .Range("A:A").End(xlUp).Row
        SrcBotRow = .Cells(65536, 1).End(xlUp).Row
        TargBotRow = Sheets(TargSheet).Cells(65536, 1).End(xlUp).Row + 1
        TargNewBot = TargBotRow + (SrcBotRow - SrcTopRow)
        .Rows(SrcTopRow & ":" & SrcBotRow).Copy
        ActiveSheet.Paste Destination:=Worksheets(TargSheet).Range(TargBotRow & ":" & TargNewBot)
    End If
    End With
Next SrcWs
End Sub