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.