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 · Answer 1 · 2017-01-16T22:07:29.790

0

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

edited Jan 16 '17 at 22:07

answered Jan 16 '17 at 05:48

MisterO

129
6

I still cannot get this to work. To put it in simple terms, I need to loop through lots of workbooks which I do not know the name of, I also do not know the name of the only tab in each one. I then need to sort the data by looking for column J and finding any 'no' entries in that column and only selecting these rows, then placing them all on one mastersheet. – Rich Stevens Jan 16 '17 at 08:29
Are the names in any logical order? ie business-listing-002-site-w, business-listing-003-site-w, ..004... etc.? – MisterO Jan 16 '17 at 14:36
They are actually yes, but I thought there would be a way to just open any file in the folder. Of course, I'll take any solution! The actual files are from "business-listing-000-w-site" to "business-listing-041-w-site", appreciate your help. – Rich Stevens Jan 16 '17 at 15:13

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

1 Answers1