How do I amend the following code to work from the tgt workbook?

[Mr Denove]

Sub Test()
    
    SG_MoveColumns ("Starts")
    SG_MoveColumns ("Leavers (incl SSMA Prog)")
    SG_MoveColumns ("In-training")
    SG_MoveColumns ("Achievements")

End Sub

Sub SG_MoveColumns(sSheetname As String)

    Dim src As Worksheet
    Dim srcLastRow As Double
    Dim srcLastCol As Double
    Dim tgt As Worksheet    'Data   in MAG Pivot Version Copy
    Dim tgtLastRow As Double
    Dim dest As Range
    Dim i As Long
    Dim x As Long
    Dim sColLetter As String
    Dim stgtColLetter As String
    Dim bFoundCol As Boolean
    

    ' Switch screen updating back off
    Application.ScreenUpdating = False

    ' Create objects to use
    Set src = Worksheets(sSheetname)  ' use sheet name passed in to the
    srcLastRow = src.Cells(Rows.Count, 1).End(xlUp).Row
    srcLastCol = src.Cells(1, Columns.Count).End(xlToLeft).Column
    
     Set tgt = Workbooks("MAG Pivot Version Copy.xlsx").Worksheets("Data")
    tgtLastRow = tgt.Cells(Rows.Count, 2).End(xlUp).Row

    ' Selects the columns to be copied
    myColumns = Array("Status", "Assignment id", "Trainee district desc", "Gender", "Age Band", "VQ Level", "Updated programme", "Provider", "Updated Employer")
    
    ' Search the source worksheet to find the columns that the required field are in
    For i = 0 To UBound(myColumns)
    On Error Resume Next
            
            ' search the column headers - assume that held in row 1
            '   set the flag to NOT FOUND
            bFoundCol = False
            
            For x = 1 To srcLastCol
            On Error Resume Next
            
                If Trim(UCase(myColumns(i))) = Trim(UCase(src.Cells(1, x).Text)) Then
                    bfound = True
                    
                    ' convert the column number in to a column letter
                    sColLetter = Col_Letter(x)
                    
                    ' convert the array to the target column letter
                    stgtColLetter = Col_Letter(i + 1)
                    
                    ' copy of the column data
                    'Range(sColLetter is the column reference & "2" is the row of that column i.e. 2 omits the header
                    src.Range(sColLetter & "2:" & sColLetter & srcLastRow).Copy tgt.Range(stgtColLetter & tgtLastRow + 1)

                    Exit For
                End If
            
            Next x
    Next i
        
    'Tidy-up created objects
    Set src = Nothing
    Set tgt = Nothing
    
    ' Switch screen updating back on
    Application.ScreenUpdating = True

End Sub

Function Col_Letter(lngCol As Long) As String
    Dim vArr
    
    ' calculate the letter linked to the column
    vArr = Split(Cells(1, lngCol).Address(True, False), "$")
    
    ' return the letter
    Col_Letter = vArr(0)
End Function

The above code currently works from the src workbook but this will change month on month and makes sense to move it to the tgt workbook.

Can someone please advise how I amend the code to to do this as its causing me problems?
Also need to find a way of adding the worksheets "Starts" etc to be copied over with the relevan data?
Thanks in advance

 

[Andrew Poulsom]

You can't have VBA code in a workbook with the extension xlsx.

 

[Mr Denove]

I understand that Andrew, it has to be xlsm, but its changing the code to work so that the Pivot (target) workbook is the one used to open the required src workbook and run it from there that I am needing.

 

[Mr Denove]

Bump

 

[Andrew Poulsom]

So how would you determine what the src workbook was?

 

[Mr Denove]

By user prompt with a path to the folder allowing the most recent file to be selected.

 

[Andrew Poulsom]

Have you written the VBA code for that?

 

[Mr Denove]

not yet as i would like to find a way of amending the code first that allows the destination file to be where the macro is stored, as per the question I have asked

 

[Andrew Poulsom]

It's not possible to amend your code without knowing what the source workbook is. If it's the ActiveWorkbook your code should work as it stands, I think.

 

[Mr Denove]

Im not quite sure why this has become so complicated.

As it stands the code (which is currently stored in my Personal Workbook) but run it with both workbooks open src and tgt.
I would like the tgt spreadsheet which in this case will be the Pivot Workbook to store the Macro as it is a generic file.
However if I run this code using the Pivot file as the src file it wont work.
I am asking how I change the code to cope with the change I want to make to it i.e.
The tgt file is the Pivot file and stores the macro
And the source file is a variant selected by the user.