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

StuartBewley

New Member
Joined
Sep 7, 2015
Messages
6
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
 

Some videos you may like

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

sericom

Well-known Member
Joined
Jan 19, 2006
Messages
891
Maybe something like

Code:
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

Well-known Member
Joined
Jul 5, 2014
Messages
16,728
Office Version
2013
Platform
Windows
Its a lot easier reading script if you post them inside hastags #

Like this:
Code:
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
 
Last edited:

StuartBewley

New Member
Joined
Sep 7, 2015
Messages
6
Thank you very much with for your help with this - my code now works perfectly! Much appreciated! Stuart
 

Watch MrExcel Video

Forum statistics

Threads
1,090,035
Messages
5,411,995
Members
403,407
Latest member
craigey1

This Week's Hot Topics

Top