Add a new source sheet to this code?

Mr Denove

Active Member
Joined
Jun 8, 2007
Messages
446
Rich (BB code):
Sub TestOpenFile()
    Dim FName As Variant
    FName = Application.GetOpenFilename(FileFilter:="Excel Files (*.xlsm), *.xlsm", Title:="Select File To Be Opened")
    If FName = False Then
        Exit Sub
    Else
        Application.EnableEvents = False
        Workbooks.Open FName
        Application.EnableEvents = True
    End If
    SG_MoveColumns ("Starts")
    ThisWorkbook.Activate
    MsgBox "Starts Done.Check Columns."
      
    Worksheets("Import Macros").Range("F4").Value = "Done"

End Sub

Sub SG_MoveColumns(sSheetname As String)

    Dim src As Worksheet
    Dim srcLastRow As Double
    Dim srcLastCol As Double
    Dim tgt As Worksheet
    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("Template - Data.xlsm").Worksheets("Starts")    
        tgtLastRow = tgt.Cells(Rows.Count, 2).End(xlUp).Row

    ' Selects the columns to be copied
    
 myColumns = Array("Assignment id", "Programme", "Trainee type", "Regional area", "Age at start", _
 "Awarding body", "Creditor", "Provider", "Employed status", "ESF dos. number", "ESF dos. desc", _
 "Eligibility code", "Eligibility desc", "Esol", "Expected end date", "First time entrant", _
 "Framework id", "MA framework desc", "SDS funding org desc", "Funding type", "GG funding", _
 "Input date", "Input leaving date", "Modern apprenticeship", "Leaving code", "Leaving code desc", _
 "CLP Outcome desc", "Leaving date", "SDS local area", "SDS local area desc" _
, "Soc code", "Soc code desc", "Soc2000", "Soc2000 desc", "Start date", "Status indicator", "Training category", _
"VQ level", "VQ reference", "VQ title", "VQ level held", "Unemployed duration", "Unempdur description", "Currjob duration", _
"Currjob dur desc", "Person id", "First names", "Last name", "NI number", "Date of birth", "Gender", "Exclude from Survey", _
"Disability", "Ethnicity", "Home phone no", "Works phone no", "Mobile phone no", "Email Address", "Asylum seeker", "SQA cand. no", _
"Completed", "Employed status at end", "Exp attainment", _
"Prog type", "Program type desc", "CL Learn.Prog", "MA Curr. Emp.", "MA Curr. role", _
"MA Prev. Emp. ", "Referred by code", "Soc2000 at end", "Soc2000 at end desc", "Contract title", _
"Person postcode in", "Person postcode out", "Person address1", "Person address2", "Person address3", _
"Person address4", "Person posttown", "Trainee district code", "Trainee district desc", "Max placement id", _
"Company id", "Company name", "Company address1", "Company address2", "Company address3", "Company address4", "Company town", _
"Company postcode in", "Company postcode out", " Company sic03 code", "Company employee size band03", "Sic03 code", _
"Sic03 desc", "Sic03 level 1 code", "Sic03 level 1 desc", "Company district code", "Company district desc", _
"Reporting Area", "SIA", "Learning Provider on Contract", "Occupational Grouping", "Updated Programme", "Programme Type", _
"Age Band", "Advanced Bookings", "Updated Employer", "MA Framework Band", "Input Period", "Start Period", "CSS Customer Ref")
                
    ' 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

There is a bolded line of code above.
Set tgt = Workbooks("Template - Data.xlsm").Worksheets("Starts")
I want to run another sheet called "AaA Starts" (from the same workbook as the "Starts" worksheet).
How do I tweak this code to run both consecutively?
Thanks in advance.
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
I should have mentioned that the Starts and AaA Starts will be pasted into different tabs with the same name as reference.
 
Upvote 0

Forum statistics

Threads
1,216,084
Messages
6,128,722
Members
449,465
Latest member
TAKLAM

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top