CODEWITHSUNDEEP
excelvba
gsf338
gsf338

Reputation: 5

Moving worksheet to new workbook is slow

I have a fairly long macro. The last step is to move five new sheets to a new workbook. This is taking several minutes.

I've isolated this part.

Application.Calculation = xlManual

Dim TempSheets As Variant

TempSheets = Array(Sheets to be moved...)

Sheets(TempSheets).Move

Application.Calculation = xlAutomatic

It speeds up to take only 10 or so seconds when I turn off screen updating. But the new worksheet is nowhere to be found.

As an alternative idea, I tried naming a new worksheet first:

Dim newwb As Workbook

Set newwb = Workbooks.Add

But couldn't get the syntax right for moving the sheets to that new workbook without saving and giving it a name first. I tried:

Sheets(TempSheets).Move Before:=newwb.Sheets("Sheet1")

I could create a new workbook that would be visible with screen updating turned off using that Set newwb = Workbooks.add.

Upvotes: 0

Views: 597

Answers (2)

EarlyBird2
EarlyBird2

Reputation: 306

When dealing with a similar problem, I worked around it by

declaring the original workbook and the new one with concrete names, e.g. wbOrig and wbNew 2. saving the original workbook under the new one's name by using the .SaveAs method 3. setting the wbNew to be the copy just made 4. if necessary, deleting sheets not needed

Dim wbOrig As Workbook
Dim wbNew As Workbook
Dim strPath As String 'the path to your new wb
Dim strFileName As String  ' the name of your new wb

Set wbOrig = Workbooks("YourWorkbooksName.xlsm")
 wbOrig.SaveAs Filename:=strPath & strFileName, FileFormat:=xlOpenXMLWorkbookMacroEnabled
Set wbNew = Workbooks(strFileName)
' if necessary, you can delete all sheets you do not need here now

Maybe not the most elegant way, but effective and certainly taking less than 10 minutes to complete.

Upvotes: 0

Pᴇʜ
Pᴇʜ

Reputation: 57683

Dim NewWb As Workbook
Set NewWb = Application.Workbooks.Add

Dim TempSheets As Variant
TempSheets = Array("Sheet1", "Sheet2")

Application.ScreenUpdating = False
Application.Calculation = xlManual

Dim StartTmr As Double  'this is just to measure the time it needs
StartTmr = Timer

'move the sheets before the first sheet in the new workbook
ThisWorkbook.Worksheets(TempSheets).Move Before:=NewWb.Sheets(1)

Debug.Print "After Move: ", Timer - StartTmr  'time needed for moving

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

Debug.Print "After Calc: ", Timer - StartTmr  'time needed for moving and calculating

This perfectly works for me and within milliseconds. Check it out and check if the long time it takes is really the moving code or turning the calculation on again. check the output in the immediate window, it should be something like:

After Move:    0,265625 
After Calc:    0,296875

Upvotes: 1

Related Questions