Issue with Macros and renaming files

[judeinajuke]

Hello all,

I'm relatively new to coding, and a lot of it kind of goes over my head so I apologize if I don't understand right away, and I also apologize if I don't make much sense trying to explain what my goal is.

I've set up some macros for my work, and they work relatively fine - however, in order for the macros to work, we have to rename the file we want to work with to what the macro is named.
We pull reports from our website and each report has the name with the date, and it will add a number at the end for each report pulled that day (1, 2, 3, so on so forth) - we pull these reports a couple times each day, for different tasks, so you may see why it can be a little confusing and frustrating to have to rename each file to the macro it's being used for.

One of my macros is titled "Rosters Macro". For this particular macro, it requires two workbooks - one we don't need to rename, as it's just a template file we utilize and never moves or changes, and the other workbook being the pulled report, "Course_Status_2023_10_27_1.csv", with the name changing each time a report is pulled. The macro utilizes the template and places it into the pulled report, and organizes the data how we'd like. However, I cannot find a way to make the macro work without renaming the report to "Rosters Macro" for each report I work on in the day. Code below.

Sub RostersMacro() ' ' RostersMacro Macro ' ' Rows("1:3").Select Selection.Delete Shift:=xlUp Columns("D:T").Select Selection.Delete Shift:=xlToLeft Columns("I:J").Select Selection.Delete Shift:=xlToLeft Columns("J:V").Select Selection.Delete Shift:=xlToLeft ActiveWindow.ScrollColumn = 3 ActiveWindow.ScrollColumn = 2 ActiveWindow.ScrollColumn = 1 Columns("G:G").Select With Selection .HorizontalAlignment = xlLeft .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Range("A1").Select ActiveWindow.SmallScroll Down:=12 Range("A1:K40").Select ActiveWorkbook.Worksheets("Rosters Macro").Sort.SortFields.Clear ActiveWorkbook.Worksheets("Rosters Macro").Sort.SortFields.Add2 Key:=Range( _ "A2:A29"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _ xlSortNormal With ActiveWorkbook.Worksheets("Rosters Macro").Sort .SetRange Range("A1:K29") .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With ActiveWorkbook.Worksheets("Rosters Macro").Sort.SortFields.Clear ActiveWorkbook.Worksheets("Rosters Macro").Sort.SortFields.Add2 Key:=Range( _ "A2:A30"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _ xlSortNormal ActiveWorkbook.Worksheets("Rosters Macro").Sort.SortFields.Add2 Key:=Range( _ "B2:B30"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _ xlSortNormal With ActiveWorkbook.Worksheets("Rosters Macro").Sort .SetRange Range("A1:K30") .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone With Selection.Borders(xlEdgeLeft) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With With Selection.Borders(xlEdgeTop) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With With Selection.Borders(xlEdgeBottom) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With With Selection.Borders(xlEdgeRight) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With With Selection.Borders(xlInsideVertical) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With With Selection.Borders(xlInsideHorizontal) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With Columns("G:H").Select Range("H1").Activate Columns("G:H").EntireColumn.AutoFit Columns("A:E").Select Range("E1").Activate Columns("A:E").EntireColumn.AutoFit Rows("1:1").Select Selection.Delete Shift:=xlUp Windows("Template.xlsx").Activate ActiveWindow.SmallScroll Down:=-9 Rows("1:9").Select Selection.Copy Windows("Rosters Macro.csv").Activate Selection.Insert Shift:=xlDown Windows("Template.xlsx").Activate ActiveWindow.SmallScroll Down:=6 Rows("22:27").Select Application.CutCopyMode = False Selection.Copy Windows("Rosters Macro.csv").Activate ActiveWindow.SmallScroll Down:=33 Range("A49").Select ActiveSheet.Paste End Sub

In this code, I have the .csv file renamed to Rosters Macro. Again, the goal is to not have to change the name of the .csv file

This is also an issue for me with macros I created that only use the pulled report itself without any templates. If I don't rename them, I'll get "Run-time error: "9" Subscript out of range".

I have a few macros I've created that somehow do NOT require I change the name, which are perfect. Example here:

Sub NewEnrollment() ' ' NewEnrollment Macro ' ' Rows("1:3").Select Selection.Delete Shift:=xlUp Columns("C:G").Select Selection.Delete Shift:=xlToLeft Columns("D:O").Select Selection.Delete Shift:=xlToLeft ActiveWindow.ScrollColumn = 2 ActiveWindow.ScrollColumn = 3 ActiveWindow.ScrollColumn = 4 ActiveWindow.ScrollColumn = 5 ActiveWindow.ScrollColumn = 6 ActiveWindow.ScrollColumn = 7 ActiveWindow.ScrollColumn = 6 Columns("I:J").Select Selection.Delete Shift:=xlToLeft Columns("J:O").Select Selection.Delete Shift:=xlToLeft Columns("K:O").Select Selection.Delete Shift:=xlToLeft ActiveWindow.ScrollColumn = 5 ActiveWindow.ScrollColumn = 4 ActiveWindow.ScrollColumn = 3 ActiveWindow.ScrollColumn = 2 ActiveWindow.ScrollColumn = 1 Columns("C:C").Select Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Range("C1").Select ActiveCell.FormulaR1C1 = "=RC[-2]&"" ""&RC[-1]" Range("C1").Select Selection.AutoFill Destination:=Range("C1:C40"), Type:=xlFillDefault Range("C1:C40").Select ActiveWindow.SmallScroll Down:=-18 Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Columns("A:B").Select Range("B1").Activate Application.CutCopyMode = False Selection.Delete Shift:=xlToLeft Columns("A:A").Select Columns("A:A").EntireColumn.AutoFit End Sub

Any help in understanding is greatly appreciated!

 

[kevin9999]

Hi @judeinajuke and welcome to the forum

This is going to take several back-and-forths to get it just right, but here's a start.
1. Where does the above code sit? Do you put it into the downloaded report every time & run it from there?
2. How many sheets are on the downloaded report?

I'm also going to 'clean' your code by getting rid of all those superfluous lines you always get when you record a macro. For future reference, when you post code please use the formatting tool on the menu above to format your code - it makes it much easier to read

 

[kevin9999]

Also, with your first code - what exactly are you trying to sort?

 

[judeinajuke]

Hi @judeinajuke and welcome to the forum

This is going to take several back-and-forths to get it just right, but here's a start.
1. Where does the above code sit? Do you put it into the downloaded report every time & run it from there?
2. How many sheets are on the downloaded report?

I'm also going to 'clean' your code by getting rid of all those superfluous lines you always get when you record a macro. For future reference, when you post code please use the formatting tool on the menu above to format your code - it makes it much easier to read

I'm not quite sure I understand what you mean by where it sits, I have it as an Excel macro saved in a folder that l open whenever I want the macro itself to run. There is only one sheet in each pulled report.

Will do, thank you! Sorry about that

As for what I'm trying to sort, the reports pull with a lot of unnecessary data and I'm rearranging it to fit into our template with the data and auto adjust the columns as well.

 

[kevin9999]

By 'where it sits' I mean what file has the module that contains the macro. You code as it is doesn't refer to a particular workbook, so I'm assuming that it is in the Rosters Macro file? Or is it in another file? You've said that the Template file never changes, therefore I would suggest keeping the macro in that file (saved as a macro-enabled workbook) and run it from there. I have written some code (below) that will ask the user to select the 'Macro Report' file - which once opened will have a variable name attached to it. That would overcome the problem of the report file's name changing every time.

As far as the sort goes, I meant what range are you trying to sort, by what column(s)? There seems to be a lost of overlap in your existing code as it stands.

Anyhow, please make a copy of your Template file & put the following code in a standard module to test it. It won't be right the first time (as previously mentioned) but it's a start. If you could provide a before-and-after example of your Rosters Macro file using the XL2BB add in that would make life so much simpler. Also a copy of your Template file.

Option Explicit
Sub Roster_Macro_New()
    Application.ScreenUpdating = False
    Dim wb1 As Workbook, wb2 As Workbook, Filename
    Set wb1 = ThisWorkbook
    
    Filename = Application.GetOpenFilename _
    (filefilter:="Excel files (*.csv),*.csv", MultiSelect:=False)
    If Filename = False Then Exit Sub
    Set wb2 = Workbooks.Open(Filename)

    Dim ws1 As Worksheet, ws2 As Worksheet, r As Range
    Set ws1 = wb1.Worksheets(1)
    Set ws2 = wb2.Worksheets(1)
    
    With ws2
        .Rows("1:3").Delete
        Set r = Union(.Columns("D:T"), .Columns("Z:AA"), .Columns("AC:AO"))
        r.Delete
        With .Columns("G")
            .HorizontalAlignment = xlLeft
            .VerticalAlignment = xlBottom
        End With
            .Columns("A:K").Sort Key1:=.Range("B1"), order1:=xlAscending, Header:=xlYes
        For Each r In .UsedRange
            r.BorderAround LineStyle:=xlContinuous, Weight:=xlThin
        Next r
        .Range("A:E").EntireColumn.AutoFit
        .Range("G:H").EntireColumn.AutoFit
        .Rows(1).Delete
    End With
    With ws1
        .Rows("1:9").Copy
        ws2.Rows(1).Insert
        .Rows("22:27").Copy ws2.Range("A49")
    End With
    Application.ScreenUpdating = True
End Sub

 

[judeinajuke]

My apologies for not fully grasping everything, like I mentioned in my first post I'm still very new to all this. My understanding is very early stages, so a lot of terminology goes over my head.

I keep all files separate, as it's easier to work with that way (and less confusing for my less tech savvy coworkers). I have a folder that contains all my macros, and then we have a separate folder for templates.

For this code that you've given me, do I need to add this in anywhere specific to my own? I made copies of the template and macro and put them in their own excel file, and replaced the code and ran it - it allowed me to select my downloaded report, however it doesn't sort my data and also cuts off a large portion of my template.

 

[kevin9999]

It sounds like you've made some progress. I really can't get this right for you without seeing the structures of both your Template and Roster files. If you can't use the XL2BB add-in, please share the files via Google Drive, Dropbox or similar file sharing platform.