How to copy and paste based on the headers

[Alex89]

Hi everyone,

The code I have does what I would like it to do, however, I would like to change how it selects the data. Right now, it copys and paste data based on the location of the cells. Now, I would like to copy and paste the data based on the headers in row 8 of "Activity Tracker - Publisher Co" tab. Into their respective columns in the “Master Publisher Content” tab. For example, the header “Publisher” in the activity tracker is in cell B8 while the data starts from B9 onwards. This data should go be matched with the header “Publisher / Influencer” in the “Master Publisher Content” in cell C2, where the data continues in C3 and onwards.

Other examples include as follows:

Activity Tracker	Copy/Paste	Master Publisher Content
Content URL/Details (C8)	-->	Content Name (D2)
Co-investor (G8)	-->	Co-investing Partner (B2)

<tbody>
</tbody>

Sub CopyandPaste_ContentName()
'
' CopyandPaste_ContentName Macro
'


    'COPY & PASTE - "Publisher / Influencer" (Master/Paste)
    Sheets("Activity Tracker - Publisher Co").Select
    Range("B9").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("Master Publisher Content").Select
    Range("C3").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    ActiveWindow.SmallScroll Down:=-18
    
    'COPY & PASTE - "Content Name" (Master/Paste)
    Sheets("Activity Tracker - Publisher Co").Select
    Range("C9").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("Master Publisher Content").Select
    Range("D3").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    ActiveWindow.SmallScroll Down:=-18
    
    'COPY & PASTE - "Dream / Consider / Plan" (Master/Paste)


    Sheets("Activity Tracker - Publisher Co").Select
    Range("E9").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("Master Publisher Content").Select
    Range("E3").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    ActiveWindow.SmallScroll Down:=-18


    'COPY & PASTE - "Content Type (Video / Editorial / Video+Editorial)" (Master/Paste)
    'Sheets("Activity Tracker - Publisher Co").Select
    'Range("F9").Select
    'Range(Selection, Selection.End(xlDown)).Select
    'Selection.Copy
    'Sheets("Master Publisher Content").Select
    'Range("F3").Select
    'Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        'xlNone, SkipBlanks:=False, Transpose:=False
    'ActiveWindow.SmallScroll Down:=-18
    
    
    'COPY & PASTE - "Co-Investing Partner" (Master/Paste)
    Sheets("Activity Tracker - Publisher Co").Select
    Range("G9").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("Master Publisher Content").Select
    Range("B3").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    ActiveWindow.SmallScroll Down:=-18
    
    'COPY & PASTE - "Reporting Start Date" (Master/Paste)
    Sheets("Activity Tracker - Publisher Co").Select
    Range("I9").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("Master Publisher Content").Select
    Range("G3").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    ActiveWindow.SmallScroll Down:=-18
    
    'COPY & PASTE - "Reporting End Date" (Master/Paste)
    Sheets("Activity Tracker - Publisher Co").Select
    Range("J9").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("Master Publisher Content").Select
    Range("H3").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    ActiveWindow.SmallScroll Down:=-18




    Sheets("Enter Info").Select




End Sub

 

[Alex89]

bump

 

[Alex89]

bump

 

[MARK858]

Alex89, do you have a table with what header matches with which header as you have no consistent matching content from what you have posted, so as it stands there is not way of making a searching code without writing code for each match individually.

 

[Alex89]

Thank you for your time! What would the logic behind that be, just out of curiosity?

Below are the tables I am trying to copy and paste the data from




[TABLE="width: 351"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Source[/TD]
[TD]Destination[/TD]
[/TR]
[TR]
[TD]1. Co-Investor[/TD]
[TD]1. Co-Investing Partner[/TD]
[/TR]
[TR]
[TD]2. Publisher[/TD]
[TD]2. Publisher / Influencer[/TD]
[/TR]
[TR]
[TD]3. Content URL/Details[/TD]
[TD]3. Content Name[/TD]
[/TR]
[TR]
[TD]4. Content Category[/TD]
[TD]4. Dream / Consider / Plan[/TD]
[/TR]
[TR]
[TD]5. Actual Launch Date[/TD]
[TD]5. Reporting Start Date[/TD]
[/TR]
[TR]
[TD]6. End Date[/TD]
[TD]6. Reporting End Date[/TD]
[/TR]
</tbody>[/TABLE]

 

[MARK858]

What would the logic behind that be, just out of curiosity?

You would loop through the cells in row 8, use Find to search the first column in the table for the cells value and Offset to get the destination column name, then use the destination column name in another Find.

 

[Alex89]

Got it, that makes sense! What I'm trying to do here is a little different than what I explained originally, but I am attempting to find the column header "Publisher" in the "Activity Tracker - Publisher Co" worksheet, offsetting the data so I am selecting one row below the header (Row 9) and pasting the data to "Master Publisher Content" in C3

This is what I have so far, but i'm getting errors saying that the object doesn't support this property, and I am not sure why.

Publisher.Offset(1, 0).End(xlDown).Select.Copy , _
Destination:=Master.Range("C3").Paste.Value

Sub SampleCopyPasteHeaders()


    Dim Publisher As Range
    Dim Master As Worksheet
    
    Set Master = ThisWorkbook.Worksheets("Master Publisher Content")
    Set ActivityTracker = ThisWorkbook.Worksheets("Activity Tracker - Publisher Co")
    
    With ThisWorkbook
        Set Publisher = ActivityTracker.Rows(8).Find(What:="Publisher", LookIn:=xlValues, lookat:=xlWhole, _
                                          MatchCase:=False, SearchFormat:=False)


        Publisher.Offset(1, 0).End(xlDown).Select.Copy , _
        Destination:=Master.Range("C3").Paste.Value


    End With
    
End Sub

 

[MARK858]

Try

Publisher.Offset(1, 0).End(xlDown).Copy
Master.Range("C3").PasteSpecial xlValues

or

Master.Range("C3").Value = Publisher.Offset(1, 0).End(xlDown).Value

or

With Publisher
       Range(.Offset(1, 0), .Offset(1, 0).End(xlDown)).Copy
      Master.Range("C3").PasteSpecial xlValues
End With

depending on exactly what you are trying to achieve

 

[Alex89]

The second piece of code worked exactly the way I wanted! Thank you so much.

One small thing that I haven't been able to figure out is how to get the code to skip blanks on the source data worksheet. For example, sometimes there are blanks in "Activity Tracker - Publisher Co" worksheet so the code only copies up until a blank while there is still data in the column. How would I go about including the blanks when I copy it from the source data.

 

[MARK858]

What are you trying to get? The last used cell in the column?