VBA to paste data into existing workbook without specifying workbook name?

Question

I am creating a workbook which will be used as a template for monthly reports (let's call it 'ReportWorkbookTest') and am struggling to write or record a macro which will paste data into the ReportWorkbookTest from various, unspecified workbooks.

To create the monthly reports, data is exported from a server to a .xlsx file named by the date/time the report was exported. Therefore, the name of the workbook which information will be pasted form will always have different names. The columns that the information in the monthly data exports will always remain the same (columns D:G & I). I've managed to do this for two specified workbooks but cannot transpose to new monthly data exports.

    Range("I4").Select
Windows("Export 2018-06-21 11.51.34.xlsx").Activate
ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=9, Criteria1:= _
    xlFilterLastMonth, Operator:=xlFilterDynamic
Range("D2:G830,I2:I830").Select
Range("I2").Activate
Selection.Copy
Windows("ReportWorkbookTest.xlsm").Activate
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
    xlNone, SkipBlanks:=False, Transpose:=False

Is there a way to set up the VBA so that the workbook names do not need to be specified while running the macro? Also, how do I specify that the macro only copies the active rows in the table if the number of rows changes per export?

Thanks!

Answer 1

If only these two workbooks will be open you can use numbers instead of the name:

Workbooks(1)
and
Workbooks(2) 

Workbooks(1) will be the one that was opened first, more likely ReportWorkbookTest.xlsm where the macro will be, so you can provide instructions that this file should be opened first. If more than these two workbooks will be open you can try a loop approach, here is an example to use:

Dim wkb as Workbook
Dim thisWb as Workbook
Dim expWb as Workbook
Set thisWb = ThisWorkbook
For Each wkb in Workbooks
    If wkb.Name Like "Export 2018-*" Then
        expWb = wkb
        Exit For
    End If
Next
If Not expWb Is Nothing Then
    'Found Export, do stuff like copy from expWb to thisWb
    expWb.Worksheets(1).Range("B20:B40").Copy
    thisWb.Sheets("PasteSheet").Range("A3").PasteSpecial xlValues
Else
    'Workbook with Export name not found
End If

Answer 2

This is your framework, if you have multiple files to import then I would suggest a wizard instead.

Wizard framework would be: 1) prompt the user to select a file (of a certain type you might check for, can be a column name - header) 2) if it passes validation then import the data (and process it) 2b) if doesn't pass report it wasn't a valid file and prompt again 3) prompt for the next file type ......

I have a project like this that takes 4 different data "dumps" and merges them into a summary workbook each month.

But for a single file of changing name, here you go for a framework: you can eliminate cycling through all of the worksheets if there is only one you might also not be appending data to what already exists, but that is what finding the new last row is for.

Option Explicit

'Sub to get the Current FileName
Private Sub getFN()

    Dim Finfo As String
    Dim FilterIndex As Long
    Dim Title As String

    Dim CopyBook As Workbook    'Workbook to copy from
    Dim CopySheet As Worksheet  'Worksheet to copy from
    Dim FN As Variant           'File Name
    Dim wsNum As Double         'worksheet # as you move through the Copy Book
    Dim cwsLastRow As Long      'copy worksheet last row
    Dim mwsLastRow As Long      'master worksheet last row
    Dim masterWS As Worksheet   'thisworkbook, your master worksheet

    Dim rngCopy1 As Range
    Dim rngCopy2 As Range

    Set masterWS = ThisWorkbook.Worksheets("Master Security Logs")

    'Set up file filter
    Finfo = "Excel Files (*.xls*),*.xls*"
    'Set filter index to Excel Files by default in case more are added
    FilterIndex = 1
    ' set Caption for dialogue box
    Title = "Select the Current AP Reconcile Workbook"

    'get the Forecast Filename
    FN = Application.GetOpenFilename(Finfo, FilterIndex, Title)

    'Handle file Selection
    If FN = False Then
        MsgBox "No file was selected.", vbExclamation, "Not so fast"
    Else
        'Do your Macro tasks here
        'Supress Screen Updating but don't so this until you know your code runs well
        Application.ScreenUpdating = False

        'Open the File
        Workbooks.Open (FN)
        'Hide the file so it is out of the way
        Set CopyBook = ActiveWorkbook

        For wsNum = 1 To CopyBook.Sheets.Count 'you stated there will be 8, this is safer
            'Do your work here, looks like you are copying certain ranges from each sheet into ThisWorkbook
            CopySheet = CopyBook.Worksheets(wsNum) '1,2,3,4,5,6,7,8

            'Finds the lastRow in your Copysheet each time through
            cwsLastRow = CopySheet.Cells(CopySheet.Rows.Count, "A").End(xlUp).Row

            'Set your copy ranges
            Set rngCopy1 = CopySheet("D2:D"&cwsLastRow) 'this is your D column
            Set rngCopy2 = CopySheet("I2:I"&cwsLastRow) 'this is your I column

            'so you would have to keep tabs on what the lastRow of this sheet is too and always start at +1
            mwsLastRow = masterWS.Cells(masterWS.Rows.Count, "A").End(xlUp).Row

            'Copy the ranges in where you want them on the master sheet
            'rngCopy1.Copy destination:= masterWS.Range("D"&mwsLastRow+1)
            'rngCopy2.Copy destination:= masterWS.Range("I"&mwsLastRow+1)

            'Clear the clipboard before you go around again
            Application.CutCopyMode = False
        Next wsNum
    End If

    'Close the workbook opened for the copy
    CopyBook.Close savechanges:=False 'Not needed now

    'Screen Updating Back on
    Application.ScreenUpdating = True

End Sub