finding heading to paste data in that column

[B-Man]

I have a spreadsheet where I paste data into at last row+1 and all work fine at the moment....
I want to move columns around and tidy up the sheet but when I move a column I have to edit the vba to allow for it.
I'm thinking of doing something like this where the headings = the heading name it needs to find.

Am I on the right track or is there a much simpler option?

Find headings for each column to paste data

    Dim rng As Range

    Dim col1 As Variant
    Dim col2 As Variant
    Dim col3 As Variant
    Dim col4 As Variant
    Dim col5 As Variant
    Dim col6 As Variant

    Dim Heading1 As String
    Dim Heading2 As String
    Dim Heading3 As String
    Dim Heading4 As String
    Dim Heading5 As String
    Dim Heading6 As String

    Heading1 =
    Heading2 =
    Heading3 =
    Heading4 =
    Heading5 =
    Heading6 =

    Set rng = wsDest.Range("A4:" & .Range("ZZ4").End(xlToRight).Address)
    col1 = Application.WorksheetFunction.Match(Heading1, rng, 0)
    col2 = Application.WorksheetFunction.Match(Heading2, rng, 0)
    col3 = Application.WorksheetFunction.Match(Heading3, rng, 0)
    col4 = Application.WorksheetFunction.Match(Heading4, rng, 0)
    col5 = Application.WorksheetFunction.Match(Heading5, rng, 0)
    col6 = Application.WorksheetFunction.Match(Heading6, rng, 0)

 

[Legacy 143009]

Use Application.Match() function. It is just like worksheet MATCH function.

Dim columnNumber As Long

columnNumber = Application.Match("Header Name", Range("A1:ZZ1"),0)

 

[B-Man]

ah much simpler I just copied code i found and modified it to use.
for some reason I thought match wouldn't work in the vba.
also hadn't thought of trying to simplify it any.

Cheers

 

[Legacy 143009]

Happy that it did help