Copy Multiple Worksheet contents

BakerBaker

New Member
Joined
Feb 12, 2018
Messages
38
Office Version
  1. 2019
Platform
  1. Windows
Thanks in advance.
This is a copy of part of worksheet 1 which is a horse racing rating method:

Chelmsford City 16.45 (in cell A1)
HorsePoints
Satchville Flyer233
Irish Times177
Red Cossack (CAN)174
Kellington Kitty (USA)143
Pass The Cristal (IRE)138

<tbody>
</tbody>

On each day I can have up to 20 worksheets set out the same but with a different header in A1 (the next worksheet has Chelmsford City 17.15). I do have a macro which combines each worksheet into a single sheet called "Combined" but it will only insert cell A1 from the first sheet with all other A1 cells from other sheets being omitted thus:

Chelmsford City 16.45
HorsePoints
Satchville Flyer233
Irish Times177
Red Cossack (CAN)174
Kellington Kitty (USA)143
Pass The Cristal (IRE)138

<tbody>
</tbody>
HorsePoints
Blue Harmony247
Pindaric246
Swiss Cross242
Magicinthemaking (USA)200
Mochalov200

<tbody>
</tbody>

In the above example there should be "Chelmsford City 17.15" from worksheet 2, cell A1 in a row above "Horse" which would give a separation to each set of ratings

This is the macro I am using:

Sub Combine()
Dim J As Integer
On Error Resume Next
Sheets(1).Select
Worksheets.Add
Sheets(1).Name = "Combined"
Sheets(2).Activate
Range("A1").EntireRow.Select
Selection.Copy Destination:=Sheets(1).Range("A1")
For J = 2 To Sheets.Count
Sheets(J).Activate
Range("A1").Select
Selection.CurrentRegion.Select
Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select
Selection.Copy Destination:=Sheets(1).Range("A65536").End(xlUp)(2)
Next
End Sub

Any assistance in achieving my desired result would be appreciated.
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
What happens if you change the Offset row property from your code to zero?
Code:
Selection.Offset(0, 0).Resize(Selection.Rows.Count - 1).Select
 
Upvote 0
That does exactly what I want to achieve! Thank you very much for your prompt reply.
 
Upvote 0
How about
Code:
Sub BakerBaker()
   Dim Sht As Long
   
   Sheets.Add(Sheets(1)).Name = "Combined"
   For Sht = 2 To Sheets.Count
      Sheets(Sht).Range("A1").CurrentRegion.Copy Range("A" & Rows.Count).End(xlUp).Offset(1)
   Next Sht
   Range("A1").EntireRow.Delete
End Sub
 
Upvote 0
Thanks. Got a run-time error 1004. "That name is already taken. Try a different one." On debug - Sheets.Add(Sheets(1)).Name="Combined" is highlighted.
However, the previous answer worked OK and am using.
 
Upvote 0
Fair enough.
For anyone looking at this in the future, the error is because a sheet called Combined already exists.
 
Upvote 0
Thanks "Fluff". The existing "Combined" sheet was generated by the previous macro. Deleting that sheet and running your script actually produces a better result; I hadn't noticed, but the previous macro actually omits the very last row of each record. Thanks again.
 
Upvote 0
You're welcome & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,213,517
Messages
6,114,085
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top