vba code to paste special (values) using specific ranges

Hi – I need help with some code to paste special (values) from specific ranges on one worksheet to specific ranges on another worksheet. I feel as though I should be able to figure this one out but I’m struggling.

The origin of the data is on the tab titled “FINAL”, which consists mostly of VLOOKUP formulas. I need to copy from the FINAL tab and paste special (values only) onto the destination tab, titled “Promotions”.

The data on the FINAL tab will always be different in future samples, and will extend down to a different row every time depending on the data size – so the code should run as long as there is a value in column C on the FINAL tab.

The destination tab (“Promotions”) has some merged cells that the FINAL tab doesn’t have, so I’m not able to select all and paste special; it looks like I need code to go line by line and offset. There are two steps I need the code to do for each piece of data, here are instructions for the first step the code should do:

1a. FINAL tab: select and copy range D3:BY3
1b. Promotions tab: paste special (values) the selected range starting in cell A16
2a. FINAL tab: select and copy range R2:AO2
2b. Promotions tab: paste special (values) the selected range starting in cell O15


After that, the code will do the same thing but will offset going down to lower rows. On the FINAL tab it will offset 2 rows down to copy, then paste special on Promotions tab offsetting 7 rows down. If it helps to have it written out, I’ll write the next steps out like I did above:

3a. FINAL tab: select and copy range D5:BY5
3b. Promotions tab: paste special (values) the selected range starting in cell A23
4a. FINAL tab: select and copy range R4:AO4
4b. Promotions tab: paste special (values) the selected range starting in cell O22



I hope this all makes sense, please let me know if not. Any help would be greatly appreciated!!

Re: vba code to paste special (values) using specific ranges

Hi - I created a copy of my spreadsheet and trimmed it down as much as I could but I tried all I could think of and I can't get the size of the attachment small enough for ozgrid to let me upload it

I tried taking some screenshots and attached those instead. I had to trim those down to fit as well, I took two screenshots: one showing the first rows and columns from the FINAL tab, and one showing the first rows and columns from the PROMOTIONS tab. On the Promotions tab, I manually did a Paste Special for the first set of data to show what I'm looking for.

Please let me know if any additional clarification is needed. Thanks!

Re: vba code to paste special (values) using specific ranges

Bump

Re: vba code to paste special (values) using specific ranges

Any takers? I am still new to vba but I've come up with the following code to try to accomplish what I want. I am stuck at the offset part - the first loop works and does the paste special - but it stops when I'm trying to have it offset to go to the next Source and Destination ranges.

I get a run time error '1004': Activate Method Of Range Class Failed. The debugger goes to the first line where I try to offset. Instead of "activate" I have also tried "select" but that doesn't work either. Can anyone please help me figure it out?

Sub test()


Dim rSource As Excel.Range
Dim rSournce1 As Excel.Range
Dim rDestination As Excel.Range
Dim rDestination1 As Excel.Range




    Set rSource = Worksheets("FINAL").Range("D3:BY3")
    Set rDestination = Worksheets("Promotions").Range("A16")
    
    Set rSource1 = Worksheets("FINAL").Range("R2:AO2")
    Set rDestination1 = Worksheets("Promotions").Range("O15")
     
Application.ScreenUpdating = False






Do While IsEmpty(Worksheets("FINAL").Columns("C")) = False
        rSource.Copy
        Worksheets("Promotions").Select
        rDestination.Select
        Selection.PasteSpecial Paste:=xlPasteValues, _
        Operation:=xlNone, _
        SkipBlanks:=False, _
        Transpose:=False
    
        rSource1.Copy
        rDestination1.Select
        Selection.PasteSpecial Paste:=xlPasteValues, _
        Operation:=xlNone, _
        SkipBlanks:=False, _
        Transpose:=False
    
    rSource.Offset(2, 0).Activate
    rDestination.Offset(7, 0).Activate
    rSource1.Offset(2, 0).Activate
    rDestination1.Offset(7, 0).Activate
Loop




        




Application.ScreenUpdating = True




End Sub