Collating date from a number of worksheets into one

[Guest]

Hi Guys,

Im trying to do something that i think should be really simple. I have a
spreadsheet with 4 worksheets. I want to collate all the information from
the 2nd, 3rd and 4th worksheet into the first worksheet. Is there an easy way
i can do this with VB code or a macro? Selecting all the information into a
recordset and then putting it in the first worksheet? Any help would be
appreciated.

Thanks

 

[Ron de Bruin]

Try this
http://www.rondebruin.nl/copy2.htm

 

[Guest]

Thanks for the Link Rob. Just one quick question...rather than creating a
new worksheet to import the data into, I want to put all the data into a
worksheet that already exists. How would i go about doing this?

Thanks

 

[Ron de Bruin]

Use this then
If the sheet master not exist it will be created else in clear all cells on it before it merge all the data

Sub Test3()
Dim sh As Worksheet
Dim DestSh As Worksheet
Dim Last As Long

On Error Resume Next
If Len(ThisWorkbook.Worksheets.Item("Master").Name) = 0 Then
On Error GoTo 0
Application.ScreenUpdating = False
Set DestSh = ThisWorkbook.Worksheets.Add
DestSh.Name = "Master"
Else
Set DestSh = Sheets("Master")
End If

DestSh.Cells.Clear

For Each sh In ThisWorkbook.Worksheets


If sh.Name <> DestSh.Name Then
Last = LastRow(DestSh)

sh.UsedRange.Copy DestSh.Cells(Last + 1, "A")
'Instead of this line you can use the code below to copy only the values
'or use the PasteSpecial option to paste the format also.


'With sh.UsedRange
'DestSh.Cells(Last + 1, "A").Resize(.Rows.Count, _
'.Columns.Count).Value = .Value
'End With


'sh.UsedRange.Copy
'With DestSh.Cells(Last + 1, "A")
' .PasteSpecial xlPasteValues, , False, False
' .PasteSpecial xlPasteFormats, , False, False
' Application.CutCopyMode = False
'End With

End If
Next
DestSh.Cells(1).Select
Application.ScreenUpdating = True
End Sub