excel-VBA: copying last column with dynamic paths and names

Question

I have a xlsm that amonst others runs through all .xslx files in a directory, runs a sub, saves them. (Thank you Tabias) inside this sub I am now trying to add something that would add the last column from a third file. My first problem here is how to define the sourcefile. We need to take data from the exact file, with a similar name. So MC.xslx ahs to copy from MC12february.xlsx and KA.xlsx has to import from KAwhateverdate.xlsx

Set wbA = Workbooks.Open("C:\files" & "\" & ActiveWorkbook.Name & "*.xlsx")

unfortunately, active.workbook.name includes the extention, so OR you guys can tell me a solution OR i have to save the files date+name first and change it into wbA = Workbooks.Open("C:\files" & "\*" & ActiveWorkbook.Name) right?

The same goes for the sheet. Those wil, depending on the file, be called MC, KA,KC,... Next since i only want to copy the last column of the file into the last column of the other file I'm quite confused. I found this code and thought it was the most understandable.

Sub import()

Dim Range_to_Copy As Range
Dim Range_Destination As Range

Dim Sheet_Data As Worksheet 'sheet from where we pull the data
Dim Sheet_Destination As Worksheet ' destination
Dim workbook_data As Workbook
Dim workbook_destination As Workbook



Set workbook_data = "N:\blah\deposit" & "\*" & ActiveWorkbook.Name
Set workbook_detination = ActiveWorkbook
Set Sheet_Data = ThisWorkbook.Sheets("Sheet1") 'help, how do i do this?
Set Sheet_Destination = ThisWorkbook.Sheets("Sheet1") ' and this?

Set Range_to_Copy = sht.UsedRange.Rows(sht.UsedRange.Rows.Count).Row

Set Range_Destination = sht.UsedRange.Rows(sht.UsedRange.Rows.Count).Row

Range_to_Copy.Copy Range_Destination  'this copies from range A to B (basically A.copy B), but i changed variable names to make it easier...


'you can simplify without variables like this:
'Sheets("Sheet1").Range("D1").Copy Sheets("Summary).Range("A1")          <=====    does the same as the above coding

None of the more simpler solutions seemed fit either. example

As you see I'm completely stuck at how to define the last column and the name of the sheet. This code is to uncomplete for me to check by doing. Can someone put me on the right path? thank you.

Answer 1

As a supplement, I'd suggest creating a simeple, re-usable file open functions where you can provide a filename as a String that you'd like to search for. The function will loop through a directory (as Batman suggested) and, optionally, pull the most recent version (using date modified) of that file. Below is a set of functions that I use frequently. There is a subfolder parameter `subF' that will allow you to search within subfolder(s) relative to the current file location.

'FUNCTION opnWB
'--Opens a workbook based on filename parameter
'----WILDCARDS before and after the filename are used to allow for filename flexibility
'----Subfolder is an OPTIONAL PARAMETER used if the location of the file is located in a subfolder
Public Function opnWB(ByVal flNM As String, Optional ByVal subF As String = "") As Workbook
    If subF <> "" Then subF = "\" & subF

    Dim pthWB As String
        pthWB = "\*" & flNM & "*"  'wildcard characters before and after filename
        pthWB = filePull(subF, pthWB)

    Set opnWB = Workbooks.Open(ActiveWorkbook.path & subF & "\" & pthWB, UpdateLinks:=0)

End Function
'FUNCTION filePull
'--Cycles through folder for files that match the filename parameter (with WILDCARDS)
'--If there is more than one file that matches the filename criteria (with WILDCARDS),
'----the file "Date Modified" attribute is used and the most recent file is "selected"
Private Function filePull(ByVal subF As String, ByVal path As String) As String
    Dim lDate, temp As Date
    Dim rtrnFl, curFile As String

    Filename = Dir(ActiveWorkbook.path & subF & path)
    Do While Filename <> ""
        curFile = Filename
        curFile = ActiveWorkbook.path & subF & "\" & Filename

        If lDate = 0 Then
            rtrnFl = Filename
            lDate = GetModDate(curFile)
        Else
            temp = GetModDate(curFile)
        End If

        If temp > lDate Then
            rtrnFl = Filename
            lDate = temp
        End If

        Filename = Dir()
    Loop

    filePull = rtrnFl

End Function
'FUNCTION GetModDate
'--Returns the date a file was last modified
Public Function GetModDate(ByVal filePath As String) As Date
    GetModDate = CreateObject("Scripting.FileSystemObject").GetFile(filePath).DateLastModified
End Function

You could tweak this method where the filename would have to start file the String you pass in by simply removing the wildcard character before flNM. To use, you would simply call the opnWB function, passing in "MC" or whatever general file name you'd like to open:

Dim wbTarMC as Workbook
Set wbMC = opnWB("MC", "Source Files") 'this would open up MC.xlsx file within the subfolder "Source Files" (relative to current file location)

Hope this helps.