PDA

View Full Version : Error copying multiple worksheets using VBA



simora
09-02-2017, 08:12 PM
I'm trying to copy 2 sheets from the active workbook to a new workbook.

This code gives an error, ' Method Copy of object 'Sheets' failed '

but a new workbook "Book1" is created, but I can't see it or get to it.




ChDir "c:\Temp1"

b = MySht ' b is the sheet name of the current month
Sheets(Array("Sheet1", b)).Copy [ This code gives an error here | ' Method Copy of object 'Sheets' failed '
Set wb = ActiveWorkbook
With wb
.SaveAs fileName:="C:\Temp1\" & Sheets("Sheet1").Range("AX1").Text & " " & " The Closing Sheet" & ".xls"

.Close False
End With



I'm using office 2007. Is it true that you CANNOT copy a sheet with code inside of it ?

Even when I delete the worksheet code, it doesn't work.
The code previously worked when I only copied Sheet1

simora
09-02-2017, 10:45 PM
Think I might have figured it out.
If I remove all of the Drawing Objects, the sheets copy as intended.

I'll try it on some more sheets to see if that is precisely the problem.

mdmackillop
09-03-2017, 10:20 AM
Try

b = MySht.Name

simora
09-16-2017, 05:42 PM
Apologies mdmackillop (http://www.vbaexpress.com/forum/member.php?87-mdmackillop):

Didn't see your post.

When I removed the drawing objects, everything worked just fine.

Does anyone have a method for copying multiple sheets to a new workbook if the sheets contain TextBoxes & other Drawing Objects.

This process works if the sheets does not contain such objects;


strSaveName = Worksheets("Sheet2").Range("a1").Value

' copy sheets to new workbook
Sheets(Array("Sheet1", "Sheet3")).Copy ' [ This code gives an error here | ' Method Copy of object 'Sheets' failed '
ActiveWorkbook.SaveAs strSaveName


However, if any of the sheets have objects in them, the copy process does not work

Any ideas ?