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

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

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

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

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

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

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

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

Multiple Sheet copying
 

We got there in the end, glad I could help

"Gaffnr" wrote:

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