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]

Exactly! For example, if I want to collect data from the "Publisher" column in the source worksheet, but there are multiple blank cells throughout the column, the code only copies up until the cell prior to the blank, even though there is data that goes past it. I would like to collect the last piece of data in that column, including the blanks!

Thanks again for your time, I really appreciate it!

 

[Alex89]

**Rather, the 3rd piece of cod worked the way I wanted it to

Exactly! For example, if I want to collect data from the "Publisher" column in the source worksheet, but there are multiple blank cells throughout the column, the code only copies up until the cell prior to the blank, even though there is data that goes past it. I would like to collect the last piece of data in that column, including the blanks!

Thanks again for your time, I really appreciate it!

 

[Alex89]

**Rather, the 3rd piece of cod worked the way I wanted it to

Exactly! For example, if I want to collect data from the "Publisher" column in the source worksheet, but there are multiple blank cells throughout the column, the code only copies up until the cell prior to the blank, even though there is data that goes past it. I would like to collect the last piece of data in that column, including the blanks!

Thanks again for your time, I really appreciate it!

 

[MARK858]

Try (untested)...

      Range(Publisher.Offset(1, 0), ActivityTracker.Columns(Publisher.Column).Find("*", , xlValues, , xlByRows, xlPrevious)).Copy
      Master.Range("C3").PasteSpecial xlValues

 

[Alex89]

Hmm it does include the blanks when it's copying. But now it's copying all of the data from the source worksheet (8 extra columns), but only up until row 47 when there are 83 rows for this specific worksheet.

 

[MARK858]

Only copying one column for me, you don't have merged cells do you?

but only up until row 47 when there are 83 rows for this specific worksheet

What is the row number of the last cell with data in the Publisher column?

 

[Alex89]

No merged cells!

It varies, but the data typically doesn't surpass the 150th row (from the source page)

 

[Alex89]

Actually, I think I found a workaround by autofiltering - so the blanks are at the bottom of the cells!

Thank you so much for your help!!!!

 

[MARK858]

When you say data, is it actual visible data or formulas returning "" or formatting? and am I correct that the publisher cell is on the ActivityTracker sheet?

Also was any of the data hidden by filter or normal hiding,?

 

[Alex89]

It is visible data, consisting of text mostly, with 2 columns formatted as dates. Yes, Publisher cell is the header in Row 8 of the activity tracker.