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]

As I said, if you put the code in MAG Pivot Version Copy.xlsm and activate the source workbook before running it, it should work as it is I think.

 

[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 Variant    'NTP Performance Reports - Backup Data\2013-14\NTP Performance Report 2013-14 Period 6 September - Data.xlsx
    Dim srcwb As Workbook
    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
    Dim fNameAndPath As Variant, wb As Workbook


    ' Switch screen updating back off
    Application.ScreenUpdating = False
    
        
    ' Create objects to use
    srcwb = Application.GetOpenFilename(FileFilter:="SEEL\nsb 2\Reports - Monthly\NTP Performance Reports - Backup Data\2013-14\(*.XLSX), *.XLSX", Title:="Select File To Be Opened").Activate
    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 = Worksheets("MAG Pivot Version.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

ok ive added in the file prompt but its bringing up Runtime error 424 on line
srcwb = Application.GetOpenFilename(FileFilter:="SEEL\nsb 2\Reports - Monthly\NTP Performance Reports - Backup Data\2013-14\(*.XLSX), *.XLSX", Title:="Select File To Be Opened").Activate

 

[Andrew Poulsom]

GetOpenFilename only returns what the name of the file that the user selects (if any). So you need to assign its result to a variant, and pass that variant to Workbooks.Open. More here:

XL97: How to Use the GetOpenFilename Method

 

[Mr Denove]

Morning,

I have had a look over the link you sent me and I am afraid I am no further forward.

 

[Andrew Poulsom]

What didn't you understand?

 

[Mr Denove]

As Im not very experienced in VBA Andrew.

I am asking for guidance on what the code should be and also how I insert and where in the code I have supplied above.
I want it to prompt to the folder, then allow the user to select the file.
This file is then passed as the src Worksheet (as it contains the 4 sheets as named at the beginning.
I will be using the Pivot workbook as the source of the code and therefore need to change the code somehow as it is currently referred to as the tgt worksheet but as soon as i use it with the Pivot workbook it is reading that as the source which is wrong.
This is why I am asking for specific help. I know what I need to do just not how to do it and need explicit answers.

 

[Andrew Poulsom]

The basic syntax is:

    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

 

[Mr Denove]

ok, I understand what that is doing thank you.
where in my code should I insert this?

 

[Andrew Poulsom]

Try at the beginning of your Test procedure.

 

[Mr Denove]

Im afraid that didnt work. It opened the selected file then stopped