VBA to copy a closed workbook range to the currently open workbook

[dstepan]

I am trying to write a code to copy a range from a closed workbook to the current open workbook.

The closed workbook is the source data. .../GLDetail-ICSales.xlsx"

The current open workbook is the destination for the source data. "H:\Accounting\O Services\Accounting\Month End Financial Close\2019 Month End Close\April\Sent"April 2019.xlsm"

My problem is that the current open workbook folder and file name will change every month.

Is it possible to reflect this in VBA code?

[code/]
Sub CopyGLdetail()
'
' CopyGLdetail Macro
'


'
ChDir _
"H:\Accounting\O Services\Accounting\Month End Financial Close\2019 Month End Close"
Workbooks.Open filename:= _
"H:\Accounting\O Services\Accounting\Month End Financial Close\2019 Month End Close\GLDetail-ICSales.xlsx"
Range("Table1").Select
Selection.Copy
Windows("April 2019.xlsm").Activate
(Next month I will need this to be "May 2019.xlsm")

Range("B5").Select
ActiveSheet.Paste
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
ChDir _
"H:\Accounting\O Services\Accounting\Month End Financial Close\2019 Month End Close\April\Sent"
(Next month I will need the folder name "April" to be "May")
ActiveWorkbook.SaveAs filename:= _
"H:\Accounting\O Services\Accounting\Month End Financial Close\2019 Month End Close\April\Sent"April 2019.xlsm" _
(Next month I will need both the folder and the file updated)
, FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
End Sub

 

[dstepan]

I figured out a solution - see below.

/
Sub CopyGLdetail()
'
' CopyGLdetail Macro
'


'
Dim fNameAndPath As Variant, wb As Workbook
    ChDir _
        "H:\Accounting\O Services\Accounting\Month End Financial Close\2019 Month End Close"
    Workbooks.Open filename:= _
        "H:\Accounting\O Services\Accounting\Month End Financial Close\2019 Month End Close\GLDetail-ICSales.xlsx"
    Range("Table1").Select
    Selection.Copy
    fNameAndPath = Application.GetOpenFilename(FileFilter:="Excel Files (*.XLSM), *.XLSM", Title:="Select File To Be Opened")
    If fNameAndPath = False Then Exit Sub
    Set wb = Workbooks.Open(filename:=fNameAndPath)
    Range("B5").Select
    ActiveSheet.Paste
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    wb.Close savechanges:=True 'or false
End Sub
[CODE]