Copy data from one worksheet to another by matching column headers

[jwb1012]

I am using the code below, which works beautifully to insert the new column headings I need. Now, I am trying to come up with a way to look at the "N" columns inserted between column B and (former) column C as a result of the code (below)... and if this column heading also appears in row 13 of the "Staffing Plan" worksheet, then I need to copy the data in that column from the "Staffing Plan" worksheet and paste it into the matching column on the "Import" worksheet.

Does anyone have an efficient method in mind to make this happen?

Sub InsertN()
 Dim N As Long
 N = Sheets("Pricing").Range("I23")
 With Sheets("Import")
       .Columns("C").Resize(, N).Insert
       Sheets("Pricing").Range("E9:E" & 9 + N - 1).Copy
       .Range("C1").PasteSpecial Paste:=xlValues, Transpose:=True
       Application.CutCopyMode = False
 End With
 End Sub

On "Pricing" worksheet:

• In column E (from row 9+)...I have a list that the user will input values - which will become column headings
• In cell $I$23 I have a count of how many values are inserted into this range ("N")

On "Import" worksheet:

• It inserts "N" columns between column B and column C
• Then it copies the list from column E on "Pricing", transposes and pastes values into row 1 of "import"...in the columns that were just added between column B and (former) column C

EXAMPLE:

"Pricing" Worksheet
"New Heading 1" ..... E9
"New Heading 2" ..... E10

As a result.... $I$23 = 2


"Import" Worksheet

(before existing code above)

[TABLE="class: cms_table, width: 328"]
<tbody>[TR]
[TD]Column A
[/TD]
[TD]Column
B
[/TD]
[TD]Column
C
[/TD]
[TD]Column D
[/TD]
[/TR]
[TR]
[TD]entry 1
[/TD]
[TD]entry 1
[/TD]
[TD]entry 1
[/TD]
[TD]entry 1
[/TD]
[/TR]
[TR]
[TD]entry 2
[/TD]
[TD]entry 2
[/TD]
[TD]entry 2
[/TD]
[TD]entry 2
[/TD]
[/TR]
</tbody>[/TABLE]



(after existing code above)

[TABLE="class: cms_table, width: 456"]
<tbody>[TR]
[TD]Column A
[/TD]
[TD]<new 1="" Heading="">Column B</new>
[/TD]
[TD]<new 2="" Heading="">NEW HEADING 1</new>
[/TD]
[TD]NEW HEADING 2
[/TD]
[TD]Column C
[/TD]
[TD]Column D
[/TD]
[/TR]
[TR]
[TD]entry 1
[/TD]
[TD]entry 1
[/TD]
[TD](blank)
[/TD]
[TD](blank)
[/TD]
[TD]entry 1
[/TD]
[TD]entry 1
[/TD]
[/TR]
[TR]
[TD]entry 2
[/TD]
[TD]entry 2
[/TD]
[TD](blank)
[/TD]
[TD](blank)
[/TD]
[TD]entry 2
[/TD]
[TD]entry 2
[/TD]
[/TR]
</tbody>[/TABLE]



(RESULT AFTER CODE MODIFICATION)

#1: for this example, lets say that only "NEW HEADING 1" was found on the "Staffing Plan" worksheet in row 13.... [TABLE="class: cms_table, width: 456"]
<tbody>[TR]
[TD]Column A
[/TD]
[TD]<new 1="" Heading="">Column B</new>
[/TD]
[TD]<new 2="" Heading="">NEW HEADING 1</new>
[/TD]
[TD]NEW HEADING 2
[/TD]
[TD]Column C
[/TD]
[TD]Column D
[/TD]
[/TR]
[TR]
[TD]entry 1
[/TD]
[TD]entry 1
[/TD]
[TD]ABC
[/TD]
[TD](blank)
[/TD]
[TD]entry 1
[/TD]
[TD]entry 1
[/TD]
[/TR]
[TR]
[TD]entry 2
[/TD]
[TD]entry 2
[/TD]
[TD]XYZ
[/TD]
[TD](blank)
[/TD]
[TD]entry 2
[/TD]
[TD]entry 2
[/TD]
[/TR]
</tbody>[/TABLE]



#2: for this example, lets say that BOTH "NEW HEADING 1" and "NEW HEADING 2" were found on the "Staffing Plan" worksheet in row 13.... [TABLE="class: cms_table, width: 456"]
<tbody>[TR]
[TD]Column A
[/TD]
[TD]<new 1="" Heading="">Column B</new>
[/TD]
[TD]<new 2="" Heading="">NEW HEADING 1</new>
[/TD]
[TD]NEW HEADING 2
[/TD]
[TD]Column C
[/TD]
[TD]Column D
[/TD]
[/TR]
[TR]
[TD]entry 1
[/TD]
[TD]entry 1
[/TD]
[TD]ABC
[/TD]
[TD]DEF
[/TD]
[TD]entry 1
[/TD]
[TD]entry 1
[/TD]
[/TR]
[TR]
[TD]entry 2
[/TD]
[TD]entry 2
[/TD]
[TD]XYZ
[/TD]
[TD]UVW
[/TD]
[TD]entry 2
[/TD]
[TD]entry 2
[/TD]
[/TR]
</tbody>[/TABLE]

 

[jwb1012]

Hi everyone - I was able to re-use some code from an old worksheet, but I can't seem to get it to paste values. Also, I think it's copying the entire column - I will need to import this into a system (these "blank" rows will cause error), so I was wondering if anyone can help me get it to only copy to the last row used.

Thanks again for any guidance and support.

Code 1: INSERT COLUMNS/COLUMN HEADERS

Sub InsertN()
 Dim N As Long
 N = Sheets("Pricing").Range("I23")
 With Sheets("Import")
       .Columns("C").Resize(, N).Insert
       Sheets("Pricing").Range("E9:E" & 9 + N - 1).Copy
       .Range("C1").PasteSpecial Paste:=xlValues, Transpose:=True
       Application.CutCopyMode = False
 End With
 End Sub

Code 2: COPY DATA INTO NEW COLUMNS BASED ON HEADER

Sub Copy_Data()
    Dim header As Range, headers As Range
    
    Set headers = Worksheets("Staffing Plan").Range("M13:AC13")
    For Each header In headers
        If GetHeaderColumn(header.Value) > 0 Then
            Range(header.Offset(1, 0), header.End(xlDown)).Copy Destination:=Worksheets("Res Hrs Cost-PP").Cells(2, GetHeaderColumn(header.Value))
        End If
    Next
End Sub

Function GetHeaderColumn(header As String) As Integer
    Dim headers As Range
    Set headers = Worksheets("Res Hrs Cost-PP").Range("C1:K1")
    GetHeaderColumn = IIf(IsNumeric(Application.Match(header, headers, 0)), Application.Match(header, headers, 0), 0)
End Function