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.
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
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.
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