Copy and pasting specific sheet data to a master worksheet

[simora]

I have 9 worksheets that are formatted almost exactly the same, and in
the same workbook.(EXCEL XP ) I need to have a sheet 10 with copies
of sheets 1 -9 copied on to it, but I do not want the sheet totals from
each individual sheet copied to the final sheet ( Sheet 10)

The only difference in each sheet is the they terminate at different row
numbers before the summary. The summary on each sheet is at row 39 - 42
so some sheets have blank rows of data, but they all start at A5.

How do I go about copying these sheets to that TOTALS sheet ( Sheet #
10) so that each sheet is copied correctly with only 2 spaces between
the different individual sheet data.

--------------
Also, since each individual sheet is updated weekly, is there a way to
copy data from the previous week to do a year to date total on the
individual sheets,and also on the master sheet. The sheets are name
Sheet 1 - Sheet 9

Thanks

 

[Bunter_22]

Hi,

The code below worked fine on testing, one comment is "Need to change
last column" this is currently set to "L" but you just need to change
it to the column where your data ends.

As for the update weekly I wasn't sure whether you had meant a seperate
workbook or if the data is updated directly. Either way you should be
able to manipulate the code that I wrote for the copytomaster sub to do
the job. Any problems then let me know.

Sub CopyToMaster()

For i = 1 To ThisWorkbook.Worksheets.Count
Sheets(i).Select
If ActiveSheet.Name = "Totals" Then GoTo Nexti
Data1 = Cells(65536, 1).End(xlUp).End(xlUp).End(xlUp).Row
Range("A5:L" & Data1).Copy 'Need to change last column
Sheets("Totals").Select
If i = 1 Then
Data2 = Cells(65536, 1).End(xlUp).Row + 1
Else
Data2 = Cells(65536, 1).End(xlUp).Row + 3
End If
Range("A" & Data2).PasteSpecial xlPasteAll
Nexti:
Next i

End Sub

James

 

[Bunter_22]

One other thing that I forgot to mention was that my code is written to
ignore your sheet summaries was this how you wanted it? The code should
work fine as long as you have atleast one blank row between your data
and the sheet summaries.

Again any problems then please let me know.

James

 

[simora]

Hi James:

Thanks for the code. I'll try to get it to work.

As for the update weekly ; Every week, we'll start a new workbook
derived from the pervious week ( A Template ). I was thinking of a way
to reference a column from the past week, and have it inserted into the
current week so that I can update the balances.

Last weeks' balances need to be transferred to this week so that when
added to the current invoice totals, I can see the new current amount due.

If you can think of an easier way or a more elegant solution , I'm all
ears / (eyes )

Thanks

-----------------------------------------------------

 

[Bunter_22]

Hi Simora,

I sort of understand what you are trying to do but just have a couple
of questions as its difficult to visualise this type of thing. So you
have lists of invoices on each sheet or is it one sheet per invoice? I
see that your copying one column from the previous week but does this
mean it has the same amount of rows as the new week? When working out
the current invoice totals is this a further column along side the
previous? I suppose what I am trying to say is that I need more
information to get it to work, just in the case of copy this column and
insert on relevant sheet between these columns or something along those
lines. Alternatively email me the file and I will see what I can do.

James
(e-mail address removed)