Copying data between worksheets, based on criteria, where columns may change

[StuartBewley]

Hi guys,

I'm hoping someone can help me with this - it should be fairly simple but I'm quite new to VBA and struggling. I've found answers to similar questions on the site, but not quite the solution I'm looking for.

I basically have a master report that is produced monthly and pasted into a worksheet. I need my code to extract some records from the original report, based on a value appearing in one column, and copy the corresponding rows to a new worksheet.

In the example below I have a routine that takes the data from my "Master Data" worksheet and extracts the data where the value in column 13 is ""Corporate Tax". I am then able to copy the data from the relevant columns accordingly.

My issue is that the monthly master data is not always consistent, and therefore my "Corporate Tax" criteria may not always be in column 13.

It will always, however, be under a consistent heading, such as "skillset" or "function".

Is there a method of having the routine identify which column number that header is in, and to then set that as a variable?

I hope this makes sense.

Many thanks in advance.

Stuart




Sub Create_Skillset_WS()


'last row gives the last row that has been used in the master data sheet.
Dim LastRow As Long


'variable for empty row
Dim Erow As Long


LastRow = Sheets("Master Data").Cells(Rows.Count, 1).End(xlUp).Row


'begin input for first skillset - corporate tax




'step 1 - add a worksheet for each skillset
Sheets.Add.Name = "Corporate Tax"


'copy the header row from the "master data" worksheet


Sheets("Master data").Range("A3:BY3").Copy Sheets("Corporate Tax").Range("A1:BY1")


For i = 2 To LastRow


'code to determine the criteria within the column


If Sheets("Master data").Cells(i, 13) = "Corporate Tax" Then


Sheets("Master Data").Cells(i, 1).Copy


Erow = Sheets("Corporate Tax").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row


Sheets("Master Data").Cells(i, 1).Copy Sheets("Corporate Tax").Cells(Erow, 1)
Sheets("Master Data").Cells(i, 2).Copy Sheets("Corporate Tax").Cells(Erow, 2)




End If




Next i


Application.CutCopyMode = False
Sheets("Corporate Tax").Columns().AutoFit
Range("A1").Select




End Sub

 

[sericom]

Maybe something like

Set funcCol = Rows(5).Find("function", LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False)
Set skillCol = Rows(5).Find("skillset", LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False)
If Not funcCol Is Nothing Then
    mCol = funcCol.Column
ElseIf Not skillCol Is Nothing Then
    mCol = skillCol.Column
Else
    mCol = 0
End If
Debug.Print mCol

That will search row 5 for either of the terms, and if found set mCol to the column number.

You could replace Rows(5) with something like Range("A1:C20") if it isn't a fixed row.

 

[My Aswer Is This]

Its a lot easier reading script if you post them inside hastags #

Like this:

Sub Create_Skillset_WS()
 'last row gives the last row that has been used in the master data sheet.
 Dim LastRow As Long
'variable for empty row
 Dim Erow As Long
 LastRow = Sheets("Master Data").Cells(Rows.Count, 1).End(xlUp).Row
 'begin input for first skillset - corporate tax
 'step 1 - add a worksheet for each skillset
 Sheets.Add.Name = "Corporate Tax"
 'copy the header row from the "master data" worksheet
 Sheets("Master data").Range("A3:BY3").Copy Sheets("Corporate Tax").Range("A1:BY1")

     For i = 2 To LastRow

    'code to determine the criteria within the column

        If Sheets("Master data").Cells(i, 13) = "Corporate Tax" Then
            Sheets("Master Data").Cells(i, 1).Copy
            Erow = Sheets("Corporate Tax").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
            Sheets("Master Data").Cells(i, 1).Copy Sheets("Corporate Tax").Cells(Erow, 1)
            Sheets("Master Data").Cells(i, 2).Copy Sheets("Corporate Tax").Cells(Erow, 2)
        End If

    Next i

 Application.CutCopyMode = False
 Sheets("Corporate Tax").Columns().AutoFit
 Range("A1").Select
End Sub

 

[StuartBewley]

Thank you very much with for your help with this - my code now works perfectly! Much appreciated! Stuart