Copy out a bunch of sheets and save using range from original sheet?

user_14329187 0
Viewed 39

I want to copy out this sheet in my workbook and save as a CSV using the value in cell C29 of sheet1 of the original sheet I copied it out off: Del Mar_Replines_20200831_Macro Build.xlsm. When I run this, I get a "subscript out of Range" error.

If I just paste the value in cell C29 as the file name the macro works. Cell C29 is an equation (unsure if that makes a difference).

Sheets("Balance Sheet").Copy
    
    ActiveWorkbook.SaveAs Filename:=Workbooks("Del Mar_Replines_20200831_Macro Build").Worksheets("Sheet1").Range("C29").Value
  
    ActiveWindow.Close
vba excel
1 Answers

This macro assumes that the two worksheets are located in Workbooks("Del Mar_Replines_20200831_Macro Build") and your macro is within the same workbook.

First: you need to ensure that the value in C29 does not contain any illegal characters that can't be in a filename.

With ThisWorkbook 'refers to the workbook that contains your macro
    .Sheets("Balance Sheet").Copy 'the sheet you want to copy

    'When you copy a sheet it becomes active. If you don't identify the save path, 
    'the active workbook will be saved to Excels default save location. 
    'The new saved workbook will be located in Excels default save location;
    'the file name will be the value from "ThisWorkbook.Sheets("Sheet1").(C29)",
    'with file extension ".CSV", and file format "6"(csv). 

    ActiveWorkbook.SaveAs .Sheets("Sheet1").Range("C29").Value & ".csv", FileFormat:=6

    ActiveWindow.Close SaveChanges:=False 'close the new workbook with no popup
End With
user_63886980
Related