JumboCactuar
Well-known Member
- Joined
- Nov 16, 2016
- Messages
- 785
- Office Version
- 365
- Platform
- Windows
Hi,
I have the following code which:
Adds a New Workbook (WB2)
Copies 2 Ranges to Sheet1 of New Workbook
Saves and Closes WB2
Problems i have:
1. Sometimes the WB2.Close fails (Method SaveAs of _Object workbook failed) and leaves the new file open as Book1, Book2 etc...
2. Sometimes very slow as saving to Network Drive (unsure if this can be fixed) - even with screenupdating turned off, the excel application goes grey with the saving prompt
Appreciate any help, maybe theres an alternative way to achieve the same
I have the following code which:
Adds a New Workbook (WB2)
Copies 2 Ranges to Sheet1 of New Workbook
Saves and Closes WB2
VBA Code:
Private Sub SaveCopyofFile()
Dim WB1 As Workbook, WB2 As Workbook
Dim WS1 As Worksheet, WS2 As Worksheet, WS3 As Worksheet
IDNO = Sheets("Data").Range("A2").Value
LR = Sheets("Data").Range("A65000").End(xlUp).Row
Set WB1 = ThisWorkbook
Set WS1 = WB1.Sheets("Main")
Set WS3 = WB1.Sheets("Data")
Set WB2 = Workbooks.Add
Set WS2 = WB2.Sheets("Sheet1")
Application.EnableEvents = False
Application.ScreenUpdating = False
MyDir = "G:\SAVEDFILES\"
WS2.Range("A1:AZ" & LR).Value2 = WS1.Range("A1:AZ" & LR).Value2
WS2.Range("AA1:BG" & LR).Value2 = WS3.Range("AA1:BG" & LR).Value2
WB2.SaveAs MyDir & IDNO & ".xlsx"
WB2.Close
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub
Problems i have:
1. Sometimes the WB2.Close fails (Method SaveAs of _Object workbook failed) and leaves the new file open as Book1, Book2 etc...
2. Sometimes very slow as saving to Network Drive (unsure if this can be fixed) - even with screenupdating turned off, the excel application goes grey with the saving prompt
Appreciate any help, maybe theres an alternative way to achieve the same