Declare a global variable to refer to workbooks

[TheHack22]

Hi all,

I have these multiple VBA subs. See two examples below. I keep changing all my file names (Calculator for All Class 8.12.2021.xlsm) every day (by find and replace) when I run these subs.

Can I declare a global sub name once, and then refer to that name in all my subs and across all modules?
I know my VBA code below is not efficient. I don't know VBA. I would just copy and paste, play around with it to make it work.

Sub CLASS1_INTL()
Dim MyRange As Range
Windows("1_CLASS_INTL_Daily.csv").Activate
 With ActiveSheet.UsedRange
        Set MyRange = Range(.Cells(2, 1), .Cells(1, 1).Offset(.Rows.Count - 2, .Columns.Count - 1))
        MyRange.Select
        Selection.Copy
End With
    
    Windows("Calculator for All Class 8.12.2021.xlsm"). _
        Activate
    Sheets("CLASS 1 - INTL").Select
       
    Range("A2").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Windows("1_CLASS_INTL_Daily.csv").Close
End Sub


Sub CLASS2_INTL()
Dim MyRange As Range
 Windows("2_CLASS_INTL_Daily.csv").Activate
 
 With ActiveSheet.UsedRange
        Set MyRange = Range(.Cells(2, 1), .Cells(1, 1).Offset(.Rows.Count - 2, .Columns.Count - 1))
        MyRange.Select
        Selection.Copy
End With
    
    Windows("Calculator for All Class 8.12.2021.xlsm"). _
        Activate
    Sheets("CLASS 2 - INTL").Select
       
    Range("A2").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Windows("2_CLASS_INTL_Daily.csv").Close
End Sub

 

[mikerickson]

It might be easier for you to use a UDF rather than a global variable that has to be set before being used.

 

[Fluff]

You could do that like

Option Explicit
Const WBkName As String = "Calculator for All Class 8.12.2021.xlsm"
Sub CLASS1_INTL()
Dim MyRange As Range
Windows("1_CLASS_INTL_Daily.csv").Activate
 With ActiveSheet.UsedRange
        Set MyRange = Range(.Cells(2, 1), .Cells(1, 1).Offset(.Rows.Count - 2, .Columns.Count - 1))
        MyRange.Select
        Selection.Copy
End With
    
    Windows(WBkName).Activate
    Sheets("CLASS 1 - INTL").Select
       
    Range("A2").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Windows("1_CLASS_INTL_Daily.csv").Close
End Sub


Sub CLASS2_INTL()
Dim MyRange As Range
 Windows("2_CLASS_INTL_Daily.csv").Activate
 
 With ActiveSheet.UsedRange
        Set MyRange = Range(.Cells(2, 1), .Cells(1, 1).Offset(.Rows.Count - 2, .Columns.Count - 1))
        MyRange.Select
        Selection.Copy
End With
    
    Windows(WBkName).Activate
    Sheets("CLASS 2 - INTL").Select
       
    Range("A2").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Windows("2_CLASS_INTL_Daily.csv").Close
End Sub

 

[TheHack22]

You could do that like

Option Explicit
Const WBkName As String = "Calculator for All Class 8.12.2021.xlsm"
Sub CLASS1_INTL()
Dim MyRange As Range
Windows("1_CLASS_INTL_Daily.csv").Activate
 With ActiveSheet.UsedRange
        Set MyRange = Range(.Cells(2, 1), .Cells(1, 1).Offset(.Rows.Count - 2, .Columns.Count - 1))
        MyRange.Select
        Selection.Copy
End With
   
    Windows(WBkName).Activate
    Sheets("CLASS 1 - INTL").Select
      
    Range("A2").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Windows("1_CLASS_INTL_Daily.csv").Close
End Sub


Sub CLASS2_INTL()
Dim MyRange As Range
 Windows("2_CLASS_INTL_Daily.csv").Activate
 
 With ActiveSheet.UsedRange
        Set MyRange = Range(.Cells(2, 1), .Cells(1, 1).Offset(.Rows.Count - 2, .Columns.Count - 1))
        MyRange.Select
        Selection.Copy
End With
   
    Windows(WBkName).Activate
    Sheets("CLASS 2 - INTL").Select
      
    Range("A2").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Windows("2_CLASS_INTL_Daily.csv").Close
End Sub

@Fluff

Thanks very much for your prompt help. This works perfectly. You're the best.

Imran

 

[Fluff]

You're welcome & thanks for the feedback.