CODEWITHSUNDEEP
vbaexcelvariables
Marcin87
Marcin87

Reputation: 3

Extracting sheets to seprate files / variable sheet names / loop

I have a Spreadsheet with around 60 sheets, every week they are the same + sometimes there is a new sheet.

I am trying to make macro that would export sheets to a separate file specified in Excel file. Colum C contains sheet names and column E contains file path.

This is the code I have made so far and I struggle to use variable sheet name from cell and saving files

Sub SavingweekEnd()
    Dim ABC As String

    Application.DisplayAlerts = False
    ChDir _
    "S:\    Workbooks.Open Filename:= _
    "S:\BD1.xls"
ActiveWindow.ScrollWorkbookTabs Position:=xlLast

    x = 5
    For x = 5 To Range("h1").Value

    ABC = Cells(x, 3).Value

    Sheets(ABC).Select
    Sheets(ABC).Copy

    ActiveWorkbook.SaveAs Filename:=Cells(x, 5).Value, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
    ReadOnlyRecommended:=False, CreateBackup:=False
    ActiveWindow.Close

    Next x

    ActiveWorkbook.Save
    ActiveWindow.Close
End Sub

Upvotes: 0

Views: 94

Answers (1)

Siddharth Rout
Siddharth Rout

Reputation: 149335

Your cell objects are not fully qualified, so they will always refer to the ActiveSheet and the ActiveSheet many not be the sheet you may want to work with. It may not even be in the workbook you are expecting it to be in.

Is this what you are trying?

My Assumptions

  1. Range("H1").Value has a valid number
  2. Cells(i, 3).Value has a valid sheet name
  3. Column E has full filepath and names. If not, then amend thisws.Cells(i, 5).Value accordingly

Untested

Sub SavingMonthEndCostsheetsSJPUT()
    Dim thisWB As Workbook, thatWb As Workbook
    Dim thisws As Worksheet
    Dim ShtName As String

    Dim i As Long

    Set thisWB = ThisWorkbook
    '~~> Change this to the relevant sheet name
    Set thisws = thisWB.Sheets("Sheet1")

    For i = 5 To thisws.Range("H1").Value
        ShtName = thisws.Cells(i, 3).Value

        thisWB.Sheets(ShtName).Copy

        Set thatWb = ActiveWorkbook

        thatWb.SaveAs Filename:=thisws.Cells(i, 5).Value, FileFormat:=xlNormal

        DoEvents

        thatWb.Close (False)

        DoEvents
    Next i
End Sub

Upvotes: 1

Related Questions