Macro Lookup, copy and paste

F

ferr

New Member
Joined
Jun 9, 2022
Messages
19
Office Version
  1. 365
Platform
  1. Windows
1. Is there a more efficient code than the code below, as I have to run it for each participant group i.e. 10 times and VBA won't allow it because it is too long?
2. Instead of the code starting to paste in column H as the dates will change frequently, can it look up the date in the upload tab row 48 and match it to the date in the final tab row 1 before pasting?


Sheets("UPLOAD").Select

Range("G50").Select

ActiveSheet.PivotTables("PivotTable7").PivotCache.Refresh

Range("E56,E61,E66").Select

Range("E66").Activate

Selection.Copy

Sheets("Final").Select

Range("H2").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

Sheets("UPLOAD").Select

Range("F56,F61,F66").Select

Range("F66").Activate

Application.CutCopyMode = False

Selection.Copy

Sheets("Final").Select

Range("I2").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

Sheets("UPLOAD").Select

Range("G56,G61,G66").Select

Range("G66").Activate

Application.CutCopyMode = False

Selection.Copy

Sheets("Final").Select

Range("J2").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

Sheets("UPLOAD").Select

Range("H56,H61,H66").Select

Range("H66").Activate

Application.CutCopyMode = False

Selection.Copy

Sheets("Final").Select

Range("K2").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

Sheets("UPLOAD").Select

Range("I56,I61,I66").Select

Range("I66").Activate

Application.CutCopyMode = False

Selection.Copy

Sheets("Final").Select

Range("L2").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

Sheets("UPLOAD").Select

Range("J56,J61,J66").Select

Range("J66").Activate

Application.CutCopyMode = False

Selection.Copy

Sheets("Final").Select

Range("M2").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

Sheets("UPLOAD").Select

Range("K56,K61,K66").Select

Range("K66").Activate

Application.CutCopyMode = False

Selection.Copy

Sheets("Final").Select

Range("N2").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

Sheets("UPLOAD").Select

Range("L56,L61,L66").Select

Range("L66").Activate

Application.CutCopyMode = False

Selection.Copy

Sheets("Final").Select

Range("O2").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

Sheets("UPLOAD").Select

Range("M56,M61,M66").Select

Range("M66").Activate

Application.CutCopyMode = False

Selection.Copy

Sheets("Final").Select

Range("P2").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

Sheets("UPLOAD").Select

Range("N56,N61,N66").Select

Range("N66").Activate

Application.CutCopyMode = False

Selection.Copy

Sheets("Final").Select

Range("Q2").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

Sheets("UPLOAD").Select

Range("O56,O61,O66").Select

Range("O66").Activate

Application.CutCopyMode = False

Selection.Copy

Sheets("Final").Select

Range("R2").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

Sheets("UPLOAD").Select

Range("P56,P61,P66").Select

Range("P66").Activate

Application.CutCopyMode = False

Selection.Copy

Sheets("Final").Select

Range("S2").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

Sheets("UPLOAD").Select

Range("Q56,Q61,Q66").Select

Range("Q66").Activate

Application.CutCopyMode = False

Selection.Copy

Sheets("Final").Select

Range("T2").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

Sheets("UPLOAD").Select

Range("R56,R61,R66").Select

Range("R66").Activate

Application.CutCopyMode = False

Selection.Copy

Sheets("Final").Select

Range("U2").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

Sheets("UPLOAD").Select

Range("S56,S61,S66").Select

Range("S66").Activate

Application.CutCopyMode = False

Selection.Copy

Sheets("Final").Select

Range("V2").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

Sheets("UPLOAD").Select

Range("T56,T61,T66").Select

Range("T66").Activate

Application.CutCopyMode = False

Selection.Copy

Sheets("Final").Select

Range("W2").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

Sheets("UPLOAD").Select

Range("U56,U61,U66").Select

Range("U66").Activate

Application.CutCopyMode = False

Selection.Copy

Sheets("Final").Select

Range("X2").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False



Sheets("UPLOAD").Select

Range("V56,V61,V66").Select

Range("V66").Activate

Application.CutCopyMode = False

Selection.Copy

Sheets("Final").Select

Range("Y2").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

Sheets("UPLOAD").Select

Range("W56,W61,W66").Select

Range("W66").Activate

Application.CutCopyMode = False

Selection.Copy

Sheets("Final").Select

Range("Z2").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

Sheets("UPLOAD").Select

Range("X56,X61,X66").Select

Range("X66").Activate

Application.CutCopyMode = False

Selection.Copy

Sheets("Final").Select

Range("AA2").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

Sheets("UPLOAD").Select

Range("Y56,Y61,Y66").Select

Range("Y66").Activate

Application.CutCopyMode = False

Selection.Copy

Sheets("Final").Select

Range("AB2").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

Sheets("UPLOAD").Select

Range("Z56,Z61,Z66").Select

Range("Z66").Activate

Application.CutCopyMode = False

Selection.Copy

Sheets("Final").Select

Range("AC2").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

Sheets("UPLOAD").Select

Range("AA56,AA61,AA66").Select

Range("AA66").Activate

Application.CutCopyMode = False

Selection.Copy

Sheets("Final").Select

Range("AD2").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

Sheets("UPLOAD").Select

Range("AB56,AB61,AB66").Select

Range("AB66").Activate

Application.CutCopyMode = False

Selection.Copy

Sheets("Final").Select

Range("AE2").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

Sheets("UPLOAD").Select

Range("AC56,AC61,AC66").Select

Range("AC66").Activate

Application.CutCopyMode = False

Selection.Copy

Sheets("Final").Select

Range("AF2").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

Sheets("UPLOAD").Select

Range("AD56,AD61,AD66").Select

Range("AD66").Activate

Application.CutCopyMode = False

Selection.Copy

Sheets("Final").Select

Range("AG2").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

Sheets("UPLOAD").Select

Range("AE56,AE61,AE66").Select

Range("AE66").Activate

Application.CutCopyMode = False

Selection.Copy

Sheets("Final").Select

Range("AH2").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

Sheets("UPLOAD").Select

Range("AF56,AF61,AF66").Select

Range("AF66").Activate

Application.CutCopyMode = False

Selection.Copy

Sheets("Final").Select

Range("AI2").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

Sheets("UPLOAD").Select

Range("AG56,AG61,AG66").Select

Range("AG66").Activate

Application.CutCopyMode = False

Selection.Copy

Sheets("Final").Select

Range("AJ2").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

Sheets("UPLOAD").Select

Range("AH56,AH61,AH66").Select

Range("AH66").Activate

Application.CutCopyMode = False

Selection.Copy

Sheets("Final").Select

Range("AK2").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

Sheets("UPLOAD").Select

Range("AI56,AI61,AI66").Select

Range("AI66").Activate

Application.CutCopyMode = False

Selection.Copy

Sheets("Final").Select

Range("AL2").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

Sheets("UPLOAD").Select

Range("AJ56,AJ61,AJ66").Select

Range("AJ66").Activate

Application.CutCopyMode = False

Selection.Copy

Sheets("Final").Select

Range("AM2").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

Sheets("UPLOAD").Select

Range("AK56,AK61,AK66").Select

Range("AK66").Activate

Application.CutCopyMode = False

Selection.Copy

Sheets("Final").Select

Range("AN2").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Sort by date Sort by votes
VBA Code: 
Sub TryThis()

    Dim rngCopy As Range
    Dim i As Long, inc As Long, NoSteps As Long
    
    Set rngCopy = Worksheets("Upload").Range("E56:AK56")
    NoSteps = 3     'It sounds like you need 10?
    inc = 5
    
    With Worksheets("Final").Range("H2:AN2")
        For i = 0 To NoSteps - 1
            .Offset(i).Value = rngCopy.Offset(i * inc).Value
        Next i
    End With
    
End Sub
 
Upvote 0
ferr said:
2. Instead of the code starting to paste in column H as the dates will change frequently, can it look up the date in the upload tab row 48 and match it to the date in the final tab row 1 before pasting?
Click to expand...
Can you please explain in more detail what you mean by this? (Bearing in mind that we have no idea where your dates are, and which dates you want to match).
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question
Please help clean up my convoluted VBA code. It seems very redundant and repetitive.
Replies
1
Views
137
myall_blues
myall_blues
C
  • Solved
Please help me clean up my VBA script, I'm having trouble with redundancies
Replies
4
Views
156
CapRavOr
C
P
  • Solved
Counter in For Each loop
Replies
2
Views
263
Possum17
P
A
  • Question
Macro to update list to last row on another sheet copy source from multiple sheets
Replies
0
Views
347
Azamamir
A
P
  • Solved
Need help with macro Dynamic Range??
Replies
6
Views
263
pennbowl
P

Forum statistics

Threads
1,216,500
Messages
6,131,015
Members
449,615
Latest member
Nic0la

Share this page

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