CODEWITHSUNDEEP
vbaexcel
Mistysea
Mistysea

Reputation: 1

Copy multiple sheets to a different workbook

The following code is supposed to copy all worksheets (except last 2) from workbook called PCReport to a new workbook called Insp & date.

It falls over on the line

Workbooks(xWkb).Sheets(sheet.Name).Copy after:=Workbooks(tgtWkb).Sheets(x)

The error is runtime error 424, object required.

x = 1, total = 10, xWkb = "PCReport.xlsm", tgtWkb = "Insp25112015.xls"

Dim total As Integer
Dim NewWkb As Workbook
Dim xWs As Worksheet
Dim xWkb As String
Dim tgtWkb As String
Dim i As Integer

xWkb = "PCReport.xlsm"
Set NewWkb = Workbooks.Add
'ActiveWorkbook.SaveAs "C:\Users\Carol\Desktop\Insp" & Format(Date, "ddmmyyyy") & ".xls"
ActiveWorkbook.SaveAs "C:\Users\Carol.Hedges\Insp" & Format(Date, "ddmmyyyy") & ".xls"
tgtWkb = ActiveWorkbook.Name
total = Workbooks(xWkb).Worksheets.Count
i = 1
For x = 1 To total - 2
    Workbooks(xWkb).Sheets(sheet.Name).Copy after:=Workbooks(tgtWkb).Sheets(x)
    i = i + 1
Next

Upvotes: 0

Views: 2442

Answers (1)

Arslan Taşkın
Arslan Taşkın

Reputation: 136

This might help you, you don't need i as Paresh suggested, it might be useful when using while loop but not here:

Dim NewWkb As Workbook
Dim xWkb As Workbook
Dim x as Integer

Set xWkb = Workbooks("PCReport.xlsm")
Set NewWkb = Workbooks.Add
NewWkb.SaveAs "C:\Users\Carol.Hedges\Insp" & Format(Date, "ddmmyyyy") & ".xls"
For x = 1 To xWkb.Worksheets.Count - 2
    xWkb.Sheets(x).Copy after:=NewWkb.worksheets(NewWkb.worksheets.count)
Next x

Upvotes: 1

Related Questions