I have a workbook that has four worksheets that all have identical
columns. At the moment I am copying and pasting the data into a 5th
master worksheets each time the individual worksheets are updated.

I want to automate this process via a macro.

The number of rows in each worksheet vary from each other and may vary
in each specific worksheet from update to update (i.e. today worksheet
one may have 10 rows and tomorrow it may have 12). The data I want to
copy commences at row 11.

What would be the best way of doing this?


---
Message posted from http://www.ExcelForum.com/

I found the code below at http://www.rondebruin.nl/copy2.htm#rows

But I'm getting an "Expected array" error at *LastRow(DestSh)*

Am I on the right track with this code?


PHP code
-------------------

Sub copy_data()

Dim sh As Worksheet
Dim DestSh As Worksheet
Dim shLast As Long
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 = Worksheets.Add
DestSh.Name = "Master"
For Each sh In ThisWorkbook.Worksheets
If sh.Name < DestSh.Name Then
Last = LastRow(DestSh)
shLast = LastRow(sh)

sh.Range(sh.Rows(11), sh.Rows(shLast)).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.Range(sh.Rows(3), sh.Rows(shLast))
'DestSh.Cells(Last + 1, "A").Resize(.Rows.Count, _
'.Columns.Count).Value = .Value
'End With


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

End If
Next
Cells(1).Select
Application.ScreenUpdating = True
Else
MsgBox "The sheet Master already exist"
End If
End Sub

-------------------


--
Message posted from http://www.ExcelForum.com

Hi john

Am I on the right track with this code?

Yes but you forgot to copy the function LastRow


--
Regards Ron de Bruin
http://www.rondebruin.nl


"john_t_h " wrote in message ...
I found the code below at http://www.rondebruin.nl/copy2.htm#rows

But I'm getting an "Expected array" error at *LastRow(DestSh)*

Am I on the right track with this code?


Formula:
--------------------

Sub copy_data()

Dim sh As Worksheet
Dim DestSh As Worksheet
Dim shLast As Long
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 = Worksheets.Add
DestSh.Name = "Master"
For Each sh In ThisWorkbook.Worksheets
If sh.Name < DestSh.Name Then
Last = LastRow(DestSh)
shLast = LastRow(sh)

sh.Range(sh.Rows(11), sh.Rows(shLast)).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.Range(sh.Rows(3), sh.Rows(shLast))
'DestSh.Cells(Last + 1, "A").Resize(.Rows.Count, _
'.Columns.Count).Value = .Value
'End With


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

End If
Next
Cells(1).Select
Application.ScreenUpdating = True
Else
MsgBox "The sheet Master already exist"
End If
End Sub

--------------------



---
Message posted from http://www.ExcelForum.com/