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

KatiL

New Member
Joined
Aug 30, 2022
Messages
11
Office Version
  1. 365
Platform
  1. Windows
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.

1661869319532.png



VBA 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

'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
 
Last edited by a moderator:

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
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:

VBA 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
 
Upvote 0
Ooops.. I meant: "...save an initial copy of the new file", .. NOT: "save an initial copy of the cells.. "
 
Upvote 0
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:
1661876095281.png

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

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.
 
Upvote 0
Solution

Forum statistics

Threads
1,212,927
Messages
6,110,731
Members
448,294
Latest member
jmjmjmjmjmjm

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top