Global Variable: Declare MasterWB as ThisWorkbook instead of a name "Master Data ...."

Imran_IsshackNY

Board Regular
Joined
Feb 3, 2021
Messages
83
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
Hi All,

I have this VBA code below to copy and paste data between a Master File(ThisWorkBook) and another file.

How do I declare this line "Const WBkName As String = "Master Data Calculator.xlsm"
by using ThisWorkbook syntax so, I don't have to worry about my Master Data Calculator naming convention anymore?
Ideally, I would like "WBkName = ThisWorkBook". Is this possible?

Typically, I would add today's date to the filename, so I would have to manually change my syntax to reflect the Master WB file name.

1631896109862.png


Option Explicit
Const WBkName As String = "Master Data Calculator.xlsm"
Const WBkNameRollUp As String = "Master Data Roll Up - Template.xlsx"
Sub SaveToRelativePath()
Dim relativePath As String

Workbooks.Open ("C:\PowerBI Hardrive\Master Data Roll Up - Template.xlsx")
relativePath = ThisWorkbook.Path & Application.PathSeparator & ActiveWorkbook.Name
ActiveWorkbook.SaveAs Filename:=relativePath
End Sub
Sub CLASS_1_USA()
'
Windows(WBkName).Activate
Sheets("CLASS 1 - US").Activate

Range("A2").Select
Selection.End(xlToRight).Select
Selection.End(xlToRight).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy

Application.WindowState = xlNormal

Windows(WBkNameRollUp).Activate
Worksheets("Data").Range("A2").PasteSpecial Paste:=xlPasteValues

Application.CutCopyMode = False

End Sub
Sub CLASS_2_USA()
Windows(WBkName).Activate
Sheets("CLASS 2 - US").Activate

Range("A2").Select
Selection.End(xlToRight).Select
Selection.End(xlToRight).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy

Windows(WBkNameRollUp).Activate
Worksheets("Data").Activate
Range("A1").Select
Range("A1").End(xlDown).Offset(1, 0).Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Windows(WBkName).Activate

End Sub
Sub CLASS_3_USA()
Windows(WBkName).Activate
Sheets("CLASS 3 - US").Activate

Range("A2").Select
Selection.End(xlToRight).Select
Selection.End(xlToRight).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy

Windows(WBkNameRollUp).Activate
Worksheets("Data").Activate
Range("A1").Select
Range("A1").End(xlDown).Offset(1, 0).Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Windows(WBkName).Activate


End Sub
Sub CLASS_4_USA()
Windows(WBkName).Activate
Sheets("CLASS 4 - US").Activate

Range("A2").Select
Selection.End(xlToRight).Select
Selection.End(xlToRight).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy

Windows(WBkNameRollUp).Activate
Worksheets("Data").Activate
Range("A1").Select
Range("A1").End(xlDown).Offset(1, 0).Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Windows(WBkName).Activate


End Sub
Sub CLASS_6_USA()
Windows(WBkName).Activate
Sheets("CLASS 6 - US").Activate

Range("A2").Select
Selection.End(xlToRight).Select
Selection.End(xlToRight).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy

Windows(WBkNameRollUp).Activate
Worksheets("Data").Activate
Range("A1").Select
Range("A1").End(xlDown).Offset(1, 0).Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Windows(WBkName).Activate


End Sub
Sub CLASS_1_INTL()
Windows(WBkName).Activate
Sheets("CLASS 1 - INTL").Activate

Range("A2").Select
Selection.End(xlToRight).Select
Selection.End(xlToRight).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy

Windows(WBkNameRollUp).Activate
Worksheets("Data").Activate
Range("A1").Select
Range("A1").End(xlDown).Offset(1, 0).Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Windows(WBkName).Activate


End Sub
Sub CLASS_2_INTL()
Windows(WBkName).Activate
Sheets("CLASS 2 - INTL").Activate

Range("A2").Select
Selection.End(xlToRight).Select
Selection.End(xlToRight).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy

Windows(WBkNameRollUp).Activate
Worksheets("Data").Activate
Range("A1").Select
Range("A1").End(xlDown).Offset(1, 0).Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Windows(WBkName).Activate


End Sub
Sub CLASS_3_INTL()
Windows(WBkName).Activate
Sheets("CLASS 3 - INTL").Activate

Range("A2").Select
Selection.End(xlToRight).Select
Selection.End(xlToRight).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy

Windows(WBkNameRollUp).Activate
Worksheets("Data").Activate
Range("A1").Select
Range("A1").End(xlDown).Offset(1, 0).Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Windows(WBkName).Activate


End Sub
Sub CLASS_4_INTL()
Windows(WBkName).Activate
Sheets("CLASS 4 - INTL").Activate

Range("A2").Select
Selection.End(xlToRight).Select
Selection.End(xlToRight).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy

Windows(WBkNameRollUp).Activate
Worksheets("Data").Activate
Range("A1").Select
Range("A1").End(xlDown).Offset(1, 0).Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Windows(WBkName).Activate


End Sub
Sub CLASS_6_INTL()
Windows(WBkName).Activate
Sheets("CLASS 6 - INTL").Activate

Range("A2").Select
Selection.End(xlToRight).Select
Selection.End(xlToRight).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy

Windows(WBkNameRollUp).Activate
Worksheets("Data").Activate
Range("A1").Select
Range("A1").End(xlDown).Offset(1, 0).Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Windows(WBkName).Activate

End Sub
Sub MasterCallAllCopyNPaste()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Call SaveToRelativePath
Call CLASS_1_USA
Call CLASS_1_USA
Call CLASS_2_USA
Call CLASS_3_USA
Call CLASS_4_USA
Call CLASS_6_USA
Call CLASS_1_INTL
Call CLASS_2_INTL
Call CLASS_3_INTL
Call CLASS_4_INTL
Call CLASS_6_INTL

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
MsgBox "10 CLASSs data copied to Master Roll-Up Template"
End Sub
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,691
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
A Const has to be set equal to a constant expression so you can't use:

Const WBkName As String =ThisWorkbook.Name

You could replace the Const with:
Dim WBkName as String
then in your sub:
WBkName = ThisWorkbook.Name
 
Solution

Imran_IsshackNY

Board Regular
Joined
Feb 3, 2021
Messages
83
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
A Const has to be set equal to a constant expression so you can't use:

Const WBkName As String =ThisWorkbook.Name

You could replace the Const with:
Dim WBkName as String
then in your sub:
WBkName = ThisWorkbook.Name
@JoeMo

Thanks very much. This works perfectly.

Imran
 

Forum statistics

Threads
1,147,476
Messages
5,741,351
Members
423,657
Latest member
Medrok2021

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top