Does anyone have a more reliable alternative to this VBA (save copy of sheet to dir)

[JumboCactuar]

Hi,

I have the following code which:
Adds a New Workbook (WB2)
Copies 2 Ranges to Sheet1 of New Workbook
Saves and Closes WB2

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

 

[CSmith]

Maybe save directly to HDD then offload move of file to the OS and don't wait on result?

 

[JumboCactuar]

Maybe save directly to HDD then offload move of file to the OS and don't wait on result?

a good idea, what would be the best way to go about this?

Save to C:/TEMP for example
Then have a scheduled task to move the files (unsure how I would do this part)

 

[CSmith]

So after saving something like:

Shell("Move " & chr(34) & "C:\Temp\" & WB2.FullName & chr(34) & "G:\SAVEDFILES\ & EXIT", vbMinimizedFocus )

 

[CSmith]

That window should close once file is moved, but if move to network folder fails it will most likely remain open

 

[JumboCactuar]

Thanks

i found this also which i will try - do you know how this will compare to the shell?

MyDir = "C:\SAVEDFILES\"
MyDir2 = "G:\SAVEDFILES\"

Set FSO = CreateObject("Scripting.Filesystemobject")

SourceFileName = MyDir & IDNO & ".xlsx
DestinFileName = MyDir2 & IDNO & ".xlsx

FSO.MoveFile Source:=SourceFileName, Destination:=DestinFileName

 

[CSmith]

That does not offload to the OS it uses VBA to perform the move using filesystem object...
You will need error checking/procedures to handle problems...

 

[JumboCactuar]

So after saving something like:

Shell "Move " & Chr(34) & "C:\Temp\" & WB2.FullName & Chr(34) & "G:\SAVEDFILES\ & EXIT", vbMinimizedFocus

this debugs for me with:
Method 'FullName' of Object '_Workbook' failed

i dont know if the bracket before "Move is needed but vba editor showing red for that line until i removed it

 

[CSmith]

Ahhh

try
IDNO & ".xlsx"
in place of WB2.FullName