copy worksheet to a different workbook

[Dave B]

I'm trying to copy the sheets in one workbook into another workbook
using VBA, but I get an error when I try

Sub MergeWorkbooks(strBook1 As String, strBook2 As String)

Dim ws As Worksheet
For Each ws In Workbooks(strBook2).Sheets
ws.Copy Before:=Workbooks(strBook1).Sheets(1)
Next ws

End Sub

Both workbooks are opened when I call them. Any ideas? Thanks.

 

[Dave B]

I figured it out ... the strings I was using for filenames included the
complete directory.... sorry.

 

[Rowan Drummond]

Hi Dave

How are you calling this macro, what is the error message you are
getting and what line of code is generating the error? This worked fine
for me:

Sub start()
Dim strBook1 As String
Dim strBook2 As String
strBook1 = "Book3.xls"
strBook2 = "File5.xls"
Call MergeWorkbooks(strBook1, strBook2)
End Sub


Sub MergeWorkbooks(strBook1 As String, strBook2 As String)

Dim ws As Worksheet
For Each ws In Workbooks(strBook2).Sheets
ws.Copy Before:=Workbooks(strBook1).Sheets(1)
Next ws

End Sub

Regards
Rowan