Varaible for file name or this file pathname

[ratdogexcel]

How could i get the below code to work with a variable as the file name?

Or could I use something like "thisfile" to determine where to import to.

Dim SV5 as String

SV5 = My File.xlsm

Do While fileName <> ""
    Workbooks.Open (directory & fileName)
        
    For Each sheet In Workbooks(fileName).Worksheets
        total = Workbooks("exportedCSV.csv").Worksheets.Count
        Workbooks(fileName).Worksheets(sheet.Name).Copy _
        After:=Workbooks("My File.xlsm").Worksheets(total)
        'After:=Workbooks("SV5").Worksheets(total)
        
    Next sheet
       
    Workbooks(fileName).Close
    fileName = Dir()
Loop

thanks in advance

[HaHoBe]

Hi, ratdogexcel,

ThisWorkbook could be used to rely on the workbook with the code:

Dim wbThis As Workbook

Set wbThis = ThisWorkbook

Do While Filename <> ""
    Workbooks.Open (directory & Filename)
        
    For Each Sheet In Workbooks(Filename).Worksheets
        Total = Workbooks("exportedCSV.csv").Worksheets.Count
        Workbooks(Filename).Worksheets(Sheet.Name).Copy _
            After:=wbThis.Worksheets(wbThis.Worksheets.Count)
        'After:=Workbooks("SV5").Worksheets(total)
        
    Next Sheet
       
    Workbooks(Filename).Close
    Filename = Dir()
Loop

You should rename Sheet to a different name.

Ciao,
Holger

[ratdogexcel]

HaHoBe

that works a treat. thanks

Out of interest........... Why should I rename my sheet?

[HaHoBe]

Hi, ratdogexcel,

I for my part try to use different names for variables and leave the VBA Keywords alone because using them inside a procedure may interfere with their normal usage as the local variable sort of overwrites the VBA meaning for that procedure.

Ciao,
Holger

[ratdogexcel]

oh so you are talking about this name

Workbooks("exportedCSV.csv").Worksheets.Count

[HaHoBe]

Hi, ratdogexcel,

not exactly, I was after

For Each sheet In Workbooks(fileName).Worksheets

Ciao,
Holger