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]

Why do you keep changing your VBA code? You still don't have what I posted in the Test procedure. Your question isn't going to be resolved if you don't follow my suggestions.

 

[Mr Denove]

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

Sub SG_MoveColumns(sSheetname As String)

    Dim src As Worksheet    'NTP Performance Reports - Backup Data\2013-14\NTP Performance Report 2013-14 Period 6 September - Data.xlsx
    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.xlsm").Worksheets("Data")
    tgtLastRow = tgt.Cells(Rows.Count, 2).End(xlUp).Row

    ' Selects the columns to be copied
    myColumns = Array("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

Hi Andrew,

I have just ran the above code and it now works.
Thank you so much for persevering, it has been a learning curve for me.

In this changed code where would the MsgBox be inserted to say all complete?
And also to leave it on the Pivot file?

Last two questions for today I promise as my head is frazzled.
But again thanks.

 

[Andrew Poulsom]

Try:

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")
    ThisWorkbook.Activate
    MsgBox "All done."
End Sub

 

[Mr Denove]

Works like a dream Andrew thanks.

Few questions raised now since this compiles just I am wanting it to.

How can I add a break between opening the source file and then starting the code again to populate the fields?
This is needed to add data to an inserted column in the source worksheets. Im thinking maybe just a separate macro and two buttons to control the two steps? Or just populate before running the code?

Also why does the MsgBox "All done" get inserted in the code there? It would help me appreciate the working code if you do have time to explain.
Thanks.

 

[Andrew Poulsom]

You can split the Test procedure into two procedures:

Sub Test1()
    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
End Sub

Sub Test2()
    SG_MoveColumns ("Starts")
    SG_MoveColumns ("Leavers (incl SSMA Prog)")
    SG_MoveColumns ("In-training")
    SG_MoveColumns ("Achievements")
    ThisWorkbook.Activate
    MsgBox "All done."
End Sub

Provided that the opened workbook is the active workbook when Test2 is run your other procedures should work. You won't be able to call that procedure from a button in another workbook. To do that you would need something like this:

Dim wb As Workbook

Sub Test1()
    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
        Set wb = Workbooks.Open(FName)
    End If
End Sub

Sub Test2()
    If Not wb Is Nothing Then
        wb.Activate
        SG_MoveColumns ("Starts")
        SG_MoveColumns ("Leavers (incl SSMA Prog)")
        SG_MoveColumns ("In-training")
        SG_MoveColumns ("Achievements")
        ThisWorkbook.Activate
        MsgBox "All done."
    End If
End Sub

To answer your second question it's there to tell the user when all the called procedures have finished.

 

[Mr Denove]

Thanks