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

T

TheHack22

Board Regular
Joined
Feb 3, 2021
Messages
121
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

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Sort by date Sort by votes
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
 
Upvote 0
Solution
JoeMo said:
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
Click to expand...
@JoeMo

Thanks very much. This works perfectly.

Imran
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question
VBA to select partial filename and go back and forth between two files to copy paste
Replies
1
Views
166
shknbk2
S
N
  • Question
VBA Questions - Copying dynamic data from one tab to another and deleting filtered rows
Replies
1
Views
187
DanteAmor
DanteAmor
S
  • Solved
VBA does not continue to copy after blank cell
Replies
8
Views
526
jolivanes
jolivanes
G
  • Question
How can i extract the correct leave time on this code?
Replies
1
Views
363
rlv01
R
S
  • Question
How to streamline?
Replies
2
Views
169
sachavez
S

Forum statistics

Threads
1,215,045
Messages
6,122,836
Members
449,096
Latest member
Erald

Share this page

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
Back
Top