Open, rename and run same excel macro on multiple excel files

Question

I have about 50 Excel sheets in one folder, on my MacBook - (/Users/myusername/Desktop/Tidy/folder")

I want to perform the following Macro on them all:

Sub SmartCopy()
Dim s1 As Worksheet, s2 As Worksheet
Dim N As Long, i As Long, j As Long
Set s1 = Sheets("s1")
Set s2 = Sheets("s2")
N = s1.Cells(Rows.Count, "Y").End(xlUp).Row
j = 1
For i = 1 To N
    If s1.Cells(i, "Y").Value = "No" Then
    Else
        s1.Cells(i, "Y").EntireRow.Copy s2.Cells(j, 1)
        j = j + 1
    End If
Next i

End Sub

I am struggling to get the sheets to open, almost like the filepath won't be recognised, also each sheet is named like this:

business-listing-002-w-site.csv

with one tab:

business-listing-002-w-site.csv

So I also need to either 1) rename the sheet each time 2) have the macro just open the only sheet in the workbook.

I want to copy all data from all workbooks into one master. I did try to add my Macro and adapt this one but just can't get it to run at all.

link to another post

MisterO MisterO · Accepted Answer · 2017-01-16T05:48:29

You need to define the workbook (file), not just the sheet(tab).

Dim filePath as String
Dim sheetStart as String
Dim count as Integer
Dim sheetEnd as string
Dim thisSheet as Worksheet
Dim wb1 as Workbook
Dim ws1 as Worksheet


filePath = "/Users/myusername/Desktop/Tidy/folder/"
sheetStart = "business-listing-"
sheetEnd = "-w-site"
Set thisSheet as ThisWorkbook.Worksheets("Sheet1")


For count = 1 to 44 'the range of sheets you have
    Set wb1 = Workbooks.Open(filePath & sheetStart & format(count, "000") & sheetEnd & ".csv")
    Set ws1 = wb1.Worksheets(sheetStart & format(count, "000") & sheetEnd)

    'move the ranges you want from ws1 to thisSheet

    wb1.close
next count

each time the code loops, it will change the filename being opened and the sheet that it is looking for.

I assume you either know or can find how to copy a range from ws1 to the next available row of thisSheet based on the original code you provided.

edited with improved code based on comments

Open, rename and run same excel macro on multiple excel files

1 Answers