| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple Sheet copying
Hi, I have 12 worksheets of data in one workbook.
I need to copy, paste special, values these all to another empty sheet. Each sheet needs to start where the last one finished - ie. sheet 1 has 1300 rows of data so sheet 2 should copy after it from row 1301. I know of course I can do this manually by copying and pasting each sheet one at a time but is there an excel tool that can do this? I dont know macro programming and I cant use macro recording because the number of rows changes in each of the 12 sheets every month. thanks to anyone that can help relieve me of this laborious task! Rob -- Rob Gaffney |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Multiple Sheet copying
Hi,
This copies the used range of every sheet to a sheet called summary. Change Summary to the name of your sheet for the compiled data. Right click any sheet tab, view code and paste this in and run it Sub servient() destsheet = "Summary" 'change to suit For x = 1 To Worksheets.Count If Sheets(x).Name = destsheet Then GoTo getmeout lastrow = Sheets(destsheet).Cells(Cells.Rows.Count, "A").End(xlUp).Row Sheets(x).UsedRange.Copy Destination:=Sheets(destsheet).Range("A" & lastrow + 1) getmeout: Next End Sub Mike "Gaffnr" wrote: Hi, I have 12 worksheets of data in one workbook. I need to copy, paste special, values these all to another empty sheet. Each sheet needs to start where the last one finished - ie. sheet 1 has 1300 rows of data so sheet 2 should copy after it from row 1301. I know of course I can do this manually by copying and pasting each sheet one at a time but is there an excel tool that can do this? I dont know macro programming and I cant use macro recording because the number of rows changes in each of the 12 sheets every month. thanks to anyone that can help relieve me of this laborious task! Rob -- Rob Gaffney |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Multiple Sheet copying
Hi Mike
Thanks for the code. Sadly, it does not work. I get a Compile Error : Syntax Error. -- Rob Gaffney "Mike H" wrote: Hi, This copies the used range of every sheet to a sheet called summary. Change Summary to the name of your sheet for the compiled data. Right click any sheet tab, view code and paste this in and run it Sub servient() destsheet = "Summary" 'change to suit For x = 1 To Worksheets.Count If Sheets(x).Name = destsheet Then GoTo getmeout lastrow = Sheets(destsheet).Cells(Cells.Rows.Count, "A").End(xlUp).Row Sheets(x).UsedRange.Copy Destination:=Sheets(destsheet).Range("A" & lastrow + 1) getmeout: Next End Sub Mike "Gaffnr" wrote: Hi, I have 12 worksheets of data in one workbook. I need to copy, paste special, values these all to another empty sheet. Each sheet needs to start where the last one finished - ie. sheet 1 has 1300 rows of data so sheet 2 should copy after it from row 1301. I know of course I can do this manually by copying and pasting each sheet one at a time but is there an excel tool that can do this? I dont know macro programming and I cant use macro recording because the number of rows changes in each of the 12 sheets every month. thanks to anyone that can help relieve me of this laborious task! Rob -- Rob Gaffney |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Multiple Sheet copying
Hi,
It line wrapped in posting Sheets(x).UsedRange.Copy Destination:=Sheets(destsheet).Range("A" & lastrow + 1) This bit is all one line Mike "Gaffnr" wrote: Hi Mike Thanks for the code. Sadly, it does not work. I get a Compile Error : Syntax Error. -- Rob Gaffney "Mike H" wrote: Hi, This copies the used range of every sheet to a sheet called summary. Change Summary to the name of your sheet for the compiled data. Right click any sheet tab, view code and paste this in and run it Sub servient() destsheet = "Summary" 'change to suit For x = 1 To Worksheets.Count If Sheets(x).Name = destsheet Then GoTo getmeout lastrow = Sheets(destsheet).Cells(Cells.Rows.Count, "A").End(xlUp).Row Sheets(x).UsedRange.Copy Destination:=Sheets(destsheet).Range("A" & lastrow + 1) getmeout: Next End Sub Mike "Gaffnr" wrote: Hi, I have 12 worksheets of data in one workbook. I need to copy, paste special, values these all to another empty sheet. Each sheet needs to start where the last one finished - ie. sheet 1 has 1300 rows of data so sheet 2 should copy after it from row 1301. I know of course I can do this manually by copying and pasting each sheet one at a time but is there an excel tool that can do this? I dont know macro programming and I cant use macro recording because the number of rows changes in each of the 12 sheets every month. thanks to anyone that can help relieve me of this laborious task! Rob -- Rob Gaffney |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Multiple Sheet copying
thanks mike - better but now getting a "runtime error 9 - subscript out of
range" error -- Rob Gaffney "Mike H" wrote: Hi, It line wrapped in posting Sheets(x).UsedRange.Copy Destination:=Sheets(destsheet).Range("A" & lastrow + 1) This bit is all one line Mike "Gaffnr" wrote: Hi Mike Thanks for the code. Sadly, it does not work. I get a Compile Error : Syntax Error. -- Rob Gaffney "Mike H" wrote: Hi, This copies the used range of every sheet to a sheet called summary. Change Summary to the name of your sheet for the compiled data. Right click any sheet tab, view code and paste this in and run it Sub servient() destsheet = "Summary" 'change to suit For x = 1 To Worksheets.Count If Sheets(x).Name = destsheet Then GoTo getmeout lastrow = Sheets(destsheet).Cells(Cells.Rows.Count, "A").End(xlUp).Row Sheets(x).UsedRange.Copy Destination:=Sheets(destsheet).Range("A" & lastrow + 1) getmeout: Next End Sub Mike "Gaffnr" wrote: Hi, I have 12 worksheets of data in one workbook. I need to copy, paste special, values these all to another empty sheet. Each sheet needs to start where the last one finished - ie. sheet 1 has 1300 rows of data so sheet 2 should copy after it from row 1301. I know of course I can do this manually by copying and pasting each sheet one at a time but is there an excel tool that can do this? I dont know macro programming and I cant use macro recording because the number of rows changes in each of the 12 sheets every month. thanks to anyone that can help relieve me of this laborious task! Rob -- Rob Gaffney |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Multiple Sheet copying
On which line? hve you got a sheet called Summary?
You need to have a named sheet to copy the data to and that name must appear in the code here destsheet = "Summary" Mike "Gaffnr" wrote: thanks mike - better but now getting a "runtime error 9 - subscript out of range" error -- Rob Gaffney "Mike H" wrote: Hi, It line wrapped in posting Sheets(x).UsedRange.Copy Destination:=Sheets(destsheet).Range("A" & lastrow + 1) This bit is all one line Mike "Gaffnr" wrote: Hi Mike Thanks for the code. Sadly, it does not work. I get a Compile Error : Syntax Error. -- Rob Gaffney "Mike H" wrote: Hi, This copies the used range of every sheet to a sheet called summary. Change Summary to the name of your sheet for the compiled data. Right click any sheet tab, view code and paste this in and run it Sub servient() destsheet = "Summary" 'change to suit For x = 1 To Worksheets.Count If Sheets(x).Name = destsheet Then GoTo getmeout lastrow = Sheets(destsheet).Cells(Cells.Rows.Count, "A").End(xlUp).Row Sheets(x).UsedRange.Copy Destination:=Sheets(destsheet).Range("A" & lastrow + 1) getmeout: Next End Sub Mike "Gaffnr" wrote: Hi, I have 12 worksheets of data in one workbook. I need to copy, paste special, values these all to another empty sheet. Each sheet needs to start where the last one finished - ie. sheet 1 has 1300 rows of data so sheet 2 should copy after it from row 1301. I know of course I can do this manually by copying and pasting each sheet one at a time but is there an excel tool that can do this? I dont know macro programming and I cant use macro recording because the number of rows changes in each of the 12 sheets every month. thanks to anyone that can help relieve me of this laborious task! Rob -- Rob Gaffney |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Multiple Sheet copying
Thanks mike, works like a dream.
Last question if i may. This has created 12 macros. Is there a way I can run them all at once? -- Rob Gaffney "Mike H" wrote: Hi, This copies the used range of every sheet to a sheet called summary. Change Summary to the name of your sheet for the compiled data. Right click any sheet tab, view code and paste this in and run it Sub servient() destsheet = "Summary" 'change to suit For x = 1 To Worksheets.Count If Sheets(x).Name = destsheet Then GoTo getmeout lastrow = Sheets(destsheet).Cells(Cells.Rows.Count, "A").End(xlUp).Row Sheets(x).UsedRange.Copy Destination:=Sheets(destsheet).Range("A" & lastrow + 1) getmeout: Next End Sub Mike "Gaffnr" wrote: Hi, I have 12 worksheets of data in one workbook. I need to copy, paste special, values these all to another empty sheet. Each sheet needs to start where the last one finished - ie. sheet 1 has 1300 rows of data so sheet 2 should copy after it from row 1301. I know of course I can do this manually by copying and pasting each sheet one at a time but is there an excel tool that can do this? I dont know macro programming and I cant use macro recording because the number of rows changes in each of the 12 sheets every month. thanks to anyone that can help relieve me of this laborious task! Rob -- Rob Gaffney |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Multiple Sheet copying
mike, ignore that last piece. I see it has created a copy of all 12.
you are a genius. thanks so much. Rob -- Rob Gaffney "Mike H" wrote: Hi, This copies the used range of every sheet to a sheet called summary. Change Summary to the name of your sheet for the compiled data. Right click any sheet tab, view code and paste this in and run it Sub servient() destsheet = "Summary" 'change to suit For x = 1 To Worksheets.Count If Sheets(x).Name = destsheet Then GoTo getmeout lastrow = Sheets(destsheet).Cells(Cells.Rows.Count, "A").End(xlUp).Row Sheets(x).UsedRange.Copy Destination:=Sheets(destsheet).Range("A" & lastrow + 1) getmeout: Next End Sub Mike "Gaffnr" wrote: Hi, I have 12 worksheets of data in one workbook. I need to copy, paste special, values these all to another empty sheet. Each sheet needs to start where the last one finished - ie. sheet 1 has 1300 rows of data so sheet 2 should copy after it from row 1301. I know of course I can do this manually by copying and pasting each sheet one at a time but is there an excel tool that can do this? I dont know macro programming and I cant use macro recording because the number of rows changes in each of the 12 sheets every month. thanks to anyone that can help relieve me of this laborious task! Rob -- Rob Gaffney |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Copying Data from Multiple Sheets to One sheet | Excel Worksheet Functions | |||
| need help please inserting multiple rows based on cell value then copying to data sheet | Excel Worksheet Functions | |||
| copying one cell in multiple sheets into a column on one sheet | Excel Worksheet Functions | |||
| Copying Cells From Multiple Worksheets to Create Summary Sheet | Excel Discussion (Misc queries) | |||
| Opening Multiple files and Copying the info all to one other sheet | Excel Discussion (Misc queries) | |||
Hybrid Mode
