Copy data from closed workbook to active workbook

Question

I'm new to using VBA, and don't really understand a lot of terminology yet, so please bear with me.

Using the record macro function, I've got the below macro:

 Sub CFData()
'
' CFData Macro
'

'
    Sheets("CF Data").Select
    Range("J5").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.ClearContents
    Range("B5").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Cut
    Range("J5").Select
    ActiveSheet.Paste
    Range("B5").Select
    Workbooks.Open Filename:= _
        "W:\\Shared\Config&Planning\CF Data.xlsx"
    Range("A2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Copy
    Windows("Template 2105.xlsx").Activate
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows("CF Data.xlsx").Activate
    Application.CutCopyMode = False
    ActiveWindow.Close
End Sub

I'm using it to copy a range of data that is automatically updated each morning, and paste it into the active workbook, which is saved with a new name each day (eg "Template 2105" today, "Template 2205" tomorrow etc). Having recorded the macro though, it doesn't recognise a different file name when selecting the window. I know there's obviously a much better way to write this, but I have no idea what needs to change.

Many thanks

Please provide the sequence of Template_XXXX, the issue with your macro is that you'll have to copy your data to blank (new Workbook) and only afterwards save it as desired "Template_XXXX.xlsx" — ygaft, May 21 '18 at 14:56
We need to know if that size and location of the range you are going to copy is always the same. Also they have provided an auto file naming, you could always prompt for the naming as well as query the user to select yesterdays file. — Wookies-Will-Code, May 21 '18 at 16:25
Please explain in more detail, what does `"it doesn't recognise a different file name when selecting the window"` — GMalc, May 21 '18 at 16:56

score 0 · Answer 1 · answered May 21 '18 at 15:28

Replace

Windows("Template 2105.xlsx").Activate

with

Windows("Template " & Format(Day(Now()), "00") & Format(Month(Now()), "00") & ".xlsx").Activate

The above will dynamically create the string based on today's date. I also recommend you take a look here to see How to avoid using Select in Excel VBA.

warner_sc · Answer 2 · 2018-05-21T16:30:59.747

Hope this help you:

Using @dwirony dynamic named workbook method

(untested)

 Sub CFData()

    ' CFData Macro

    Dim cf_data As Worksheet

    Set cf_data = Sheets("CF Data")

    With cf_data

        ' clear cell contents
        .Range(.Range("J5"), .Range("J5").End(xlDown).End(xlToRight)).ClearContents
        ' cut and paste
        .Range(.Range("B5"), .Range("B5").End(xlDown).End(xlToRight)).Cut .Range("J5")
        ' .Range("B5").Select ' select should be avoided

    End With

    ' open workbook
    Workbooks.Open Filename:="W:\\Shared\Config&Planning\CF Data.xlsx"

    'copy
    Range(Range("A2"), Range("A2").End(xlDown).End(xlToRight)).Copy

    Windows("Template " & Format(Day(Now()), "00") & Format(Month(Now()), "00") & ".xlsx").Activate

    'paste VALUES only
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

    'close without saving (FALSE = no save)
    Workbooks("CF Data.xlsx").Close False

    ' clear copy mode
    Application.CutCopyMode = False

End Sub

You might need to change `Selection.PasteSpecial` to `Range("A2").PasteSpecial`. — dwirony, May 21 '18 at 16:09
Thanks, this is a good start. However I can see where it's not going to work. When the sheet is updated, we simply open yesterdays file, run the macros to update it, then save as the new filename, using the correct date. This won't reference yesterday file when switching windows. — P.Seymour, May 22 '18 at 08:54

Copy data from closed workbook to active workbook

2 Answers2