How to copy and paste based on the headers

Alex89

New Member
Joined
May 30, 2019
Messages
34
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>







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

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
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.
 
Upvote 0
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




SourceDestination
1. Co-Investor1. Co-Investing Partner
2. Publisher2. Publisher / Influencer
3. Content URL/Details3. Content Name
4. Content Category4. Dream / Consider / Plan
5. Actual Launch Date5. Reporting Start Date
6. End Date6. Reporting End Date

<colgroup><col><col></colgroup><tbody>
</tbody>
 
Upvote 0
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.
 
Upvote 0
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




Code:
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
 
Upvote 0
Try
Code:
Publisher.Offset(1, 0).End(xlDown).Copy
Master.Range("C3").PasteSpecial xlValues
or
Code:
Master.Range("C3").Value = Publisher.Offset(1, 0).End(xlDown).Value
or
Code:
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
 
Last edited:
Upvote 0
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.
 
Upvote 0
What are you trying to get? The last used cell in the column?
 
Upvote 0

Forum statistics

Threads
1,213,533
Messages
6,114,179
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top