Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy and pasting specific sheet data to a master worksheet
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy and pasting specific sheet data to a master worksheet
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy and pasting specific sheet data to a master worksheet
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy and pasting specific sheet data to a master worksheet
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy specific data form sheets into master sheet | Excel Worksheet Functions | |||
Showing Specific Data from Master Sheet to Another Sheet | New Users to Excel | |||
copy data from master sheet | New Users to Excel | |||
copy data from master sheet | Excel Discussion (Misc queries) | |||
How do I copy specific information from a master sheet? | Excel Worksheet Functions |