VBA Copies and Pastes Values to New Workbook and Saves to Subfolder of ThisWorkbook.Path

[KatiL]

I'm so close to having this code right but it keeps getting hung up on the SaveAs line. I had it working and then added the EntireColumn.AutoFit section. Though when I remove that section again, it still doesn't work. I must have changed something else but don't remember.... I hope someone else can see what I am not seeing. Including screenshot and text of the code below.

'Copy Data from ClothingReport tabs,
' paste Values Only in new File called (companyacronym)ClothingReport.xlsx,
' save new file to the \\wtsgoanywhere\iCoStore-CA\Ready to Import\ folder,
' new file name to match tab name with .xlsx extension
' Will save over existing file if one is already saved in that folder

Sub CopyPasteClothingReport_SRG()

Application.DisplayAlerts = False

'Define Ranges for Source and Destination
    Dim wbTarget As Workbook 'Target Workbook
    Dim rgSource As Range 'Range file and tab names
    Dim wk As Workbook
   
'add new workbook
    Workbooks.Add
    Set wbTarget = ActiveWorkbook
   
'Set rgSource = [current workbook].[worksheet(tab name)].[range(start:end)]
    Set rgSource = ThisWorkbook.Worksheets("SRGClothingReport").Range("A3:S1003")

'Set rgDestination = [workbook].[worksheet].[range]
    Set rgDestination = ActiveWorkbook.Worksheets("Sheet1").Range("A1")

'Copy Data from Source Range to Clipboard, then paste Values and Number Formatting but No Formulas in Destination Range
    rgSource.Copy
    rgDestination.PasteSpecial xlPasteValuesAndNumberFormats

'Adjust Columns to Auto fit to Contents
    Cells.Select
    Cells.EntireColumn.AutoFit

'Save New Workbook As Tab Name from Copied Data
    ActiveWorkbook.SaveAs filename:=ThisWorkbook.Path & Application.PathSeparator & "Ready to Import" & Application.PathSeparator & "SRGClothingReport.xlsx", FileFormat:=51
   
'Close active workbook
    ActiveWorkbook.Close

'Remove Dancing Ants from copy section
Application.CutCopyMode = False

Application.DisplayAlerts = True

End Sub

 

[KatiL]

Ohhh. I fixed it... partially. It couldn't save the file because I needed to deselect the cells after adjusting column widths. I added Cells(1, 1).Select and that allowed the file to save an initial copy of the cells.. however if I need to run the whole process again, it does not want to save over the existing file with the same filename. I thought turning off Application.DisplayAlerts was the solution to that. .. and of course turning them back on at the end.

New Code:

'Copy Data from ClothingReport tabs,
' paste Values Only in new File called (companyacronym)ClothingReport.xlsx,
' save new file to the \\wtsgoanywhere\iCoStore-CA\Ready to Import\ folder,
' new file name to match tab name with .xlsx extension
' Will save over existing file if one is already saved in that folder

Sub CopyPasteClothingReport_SRG()

Application.DisplayAlerts = False

'Define Ranges for Source and Destination
    Dim wbTarget As Workbook 'Target Workbook
    Dim rgSource As Range 'Range file and tab names
    Dim wk As Workbook
   
'add new workbook
    Workbooks.Add
    Set wbTarget = ActiveWorkbook
   
'Set rgSource = [current workbook].[worksheet(tab name)].[range(start:end)]
    Set rgSource = ThisWorkbook.Worksheets("SRGClothingReport").Range("A3:S1003")

'Set rgDestination = [workbook].[worksheet].[range]
    Set rgDestination = ActiveWorkbook.Worksheets("Sheet1").Range("A1")

'Copy Data from Source Range to Clipboard, then paste Values and Number Formatting but No Formulas in Destination Range
    rgSource.Copy
    rgDestination.PasteSpecial xlPasteValuesAndNumberFormats

'Adjust Columns to Auto fit to Contents
    Cells.Select
    Cells.EntireColumn.AutoFit
    Cells(1, 1).Select

'Save New Workbook As Tab Name from Copied Data
    ActiveWorkbook.SaveAs filename:=ThisWorkbook.Path & Application.PathSeparator & "Ready to Import" & Application.PathSeparator & "SRGClothingReport.xlsx", FileFormat:=51
   
'Close active workbook
    ActiveWorkbook.Close

'Remove Dancing Ants from copy section
    Application.CutCopyMode = False

Application.DisplayAlerts = True

End Sub

 

[KatiL]

Ooops.. I meant: "...save an initial copy of the new file", .. NOT: "save an initial copy of the cells.. "

 

[KatiL]

I'm adding more notes here in case someone is having a similar issue or is attempting to help.

After my last update, I walked away for an hour to work on some other things. Came back and tried again and the code worked! .... and then it didn't again. The short answer is, this is a OneDrive issue. OneDrive won't complete the sync/save therefore the VBA code is interrupted and will not move on to the next step to close the newly saved workbook. Since the template will be used on a Network Drive (not OneDrive which I was just using to build and test), I moved my test to the intended Network Drive and it is working great.

The long answer is:
When I came back after a break, I had deleted the old file from the destination folder. The code successfully saved the first copy of the file to the empty destination folder. Then I reran the code to test the save over function and it successfully saved over the file a couple of times. On the third or fourth try, it stopped working. It got hung up on that same line.

Debug says it is encountering a "1004" error (can't save over an open file) however the file is not open and has a new timestamp in File Explorer. Then I noticed that File Explorer is taking a long time to save the file, as shown in the Status column:

I tried to open the file and got an 0x8117017F error (OneDrive sync issue):

That's how I finally figured out that this is a OneDrive issue. OneDrive won't complete the sync/save therefore the VBA code is interrupted and will not move on to the next step to close the newly saved workbook. Since the template will be used on a Network Drive (not OneDrive which I was just using to build and test), I moved my test to the intended Network Drive and it is working great.