Quick VBA Questions

[tommychowdah]

Hi, I have the following macro below. What this does is loads a specific sheet from from s separate closed excel file. The issue is, the sheet name has to be "Dynamic 1" in the closed model. Is there a way to create a cell with a named range, and whatever sheet name the user inputs, that is the sheet the is imported from the closed model? Thank you!


Sub IMPORT_FLAT_RATE()


'Macro created by Thomas Spiegel on 12/31/2017


Dim wbk_Output As Workbook
Dim wbk_Transfer As Workbook
Dim sourceSheet As Worksheet
Set sourceSheet = ActiveSheet


With Application
.ScreenUpdating = False
.EnableEvents = False
.DisplayAlerts = False
.Calculation = Manual
End With


Set wbk_Output = ActiveWorkbook


Application.DefaultFilePath = ""
MsgBox ("Select he Current Quarter's Flate Rate Scenario From The ALM Output Model.")
fnData = Application.GetOpenFilename("All files (*.xlsm),*.xlsm")
Set wbk_Transfer = Workbooks.Open(fnData)


wbk_Transfer.Activate
Sheets("Dynamic 1").Select
Sheets("Dynamic 1").Range("A1:ZZ6000").Select
Selection.Copy


wbk_Output.Activate
Sheets("Dynamic Base Case (Data)").Select
Sheets("Dynamic Base Case (Data)").Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False


Application.GoTo Reference:=Range("A1"), Scroll:=True


wbk_Transfer.Activate
wbk_Transfer.Close savechanges:=False


Call sourceSheet.Activate


MsgBox "The Current Quarter's Dynamic Base Case Scenario Has Been Loaded."


End Sub

 

[dmt32]

Hi,
as a suggestion, if the worksheet selection needs to be dynamic, consider creating a userform that displays all the worksheets in transfer workbook. You can then use the selected sheet from this in your code.


Dave