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]

What do you mean by stopped? Is this what you have?

Sub Test()
    Dim FName As Variant
    FName = Application.GetOpenFilename(FileFilter:="Excel Files (*.xlsx), *.xlsx", Title:="Select File To Be Opened")
    If FName = False Then
        Exit Sub
    Else
        Workbooks.Open FName
    End If    
    SG_MoveColumns ("Starts")
    SG_MoveColumns ("Leavers (incl SSMA Prog)")
    SG_MoveColumns ("In-training")
    SG_MoveColumns ("Achievements")
End Sub

 

[Mr Denove]

okay, mine wasnt the same as yours so I have now amended it accordingly.

Now getting runtime error 9 against

Set src = Workbooks(FName).Worksheets(sSheetname)[/]

I am also wanting to hardcode the folder path so it is just the file being selected.

 

[Andrew Poulsom]

You don't need to alter your existing code. It uses the ActiveWorkbook, which is the one that was just opened. The error occurs because the Workbooks property expects just the name of the workbook, not it's full path.

 

[Mr Denove]

i was seeing that from the watch window.

How do i then get it to just produce the file name and not path?

 

[Andrew Poulsom]

Why do you need the filename? If you must have it you can use ActiveWorkbook.Name.

 

[Mr Denove]

The following part of the code

src = Workbooks(FName).Worksheets(sSheetname)

uses the selected file to copy the source data from.
but its giving me Runtime error 9 and the FName is blank so not being passed from the first code. This is why I need it or am I wrong?

 

[Mr Denove]

As I am still learning I would need to see how this is used in the current code if you could provide that please

 

[Andrew Poulsom]

It would be:

Set src = Workbooks(ActiveWorkbook.Name).Worksheets(sSheetname)

but it's unnecessary because the Worksheets property is applied to the ActiveWorkbook if it's unqualified. The variable FName is declared in the procedure Test, so it's not visible to other procedures.

 

[Mr Denove]

ok, but as I mentioned can you explain where in the code this line should be please?
thanks

 

[Andrew Poulsom]

It would replace this line:

Set src = Worksheets(sSheetname)  ' use sheet name passed in to the