copy range,vlookup and if statements

V

vijayk

New Member
Joined
Jun 11, 2011
Messages
2
hi,
Have recorded one macro which works fine but just would like to know if there is a simpler way of writing it. I am new to vb and macros and hope the length of the macro can be reduced by way of a loop or some commands to reiterate. Any help would be highly appreciated!

Sub bench()
'
' bench Macro
'
'
Range("N13:N19").Select
Application.CutCopyMode = False
Selection.Copy
Range("A13").Select
ActiveSheet.Paste
Range("A12:M13").Select
Application.CutCopyMode = False
Selection.Copy
ActiveSheet.Next.Select
ActiveSheet.Next.Select
Application.CutCopyMode = False
Selection.ClearContents
ActiveSheet.Previous.Select
ActiveSheet.Previous.Select
Selection.Copy
ActiveSheet.Next.Select
ActiveSheet.Next.Select
Range("A1").Select
ActiveSheet.Paste
Range("A3").Select
ActiveSheet.Previous.Select
ActiveSheet.Previous.Select
Range("A19").Select
Application.CutCopyMode = False
Selection.Copy
ActiveSheet.Next.Select
ActiveSheet.Next.Select
ActiveSheet.Paste
Range("B3").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(""Mandideep"",BenchMarkReportExcel!R[10]C[-1]:R[16]C[11],2,FALSE)"
Range("C3").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-1]=BenchMarkReportExcel!R[16]C[-1],BenchMarkReportExcel!R[16]C,"""")"
Range("C3").Select
Selection.AutoFill Destination:=Range("C3:M3"), Type:=xlFillDefault
Range("C3:M3").Select
ActiveSheet.Previous.Select
ActiveSheet.Previous.Select
ActiveWindow.SmallScroll Down:=13
Range("N23:N29").Select
Selection.Copy
Range("A23").Select
ActiveSheet.Paste
Range("A22:N23").Select
Application.CutCopyMode = False
Selection.Copy
ActiveSheet.Next.Select
ActiveSheet.Next.Select
Range("A6").Select
ActiveSheet.Paste
Range("A8").Select
ActiveSheet.Previous.Select
ActiveSheet.Previous.Select
Range("A29").Select
Application.CutCopyMode = False
Selection.Copy
ActiveSheet.Next.Select
ActiveSheet.Next.Select
ActiveSheet.Paste
Range("B8").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(""Mandideep"",BenchMarkReportExcel!R[15]C[-1]:R[21]C[11],2,FALSE)"
Range("C8").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-1]=BenchMarkReportExcel!R[21]C[-1],BenchMarkReportExcel!R[21]C,"""")"
Range("C8").Select
Selection.AutoFill Destination:=Range("C8:M8"), Type:=xlFillDefault
Range("C8:M8").Select
Range("N6:N7").Select
Selection.ClearContents
Range("A11").Select
Sheets("BenchMarkReportExcel").Select
Range("A32:M35").Select
ActiveWindow.SmallScroll Down:=8
Range("N33:N39").Select
Selection.Copy
Range("A33").Select
ActiveSheet.Paste
Range("A32:N33").Select
Application.CutCopyMode = False
Selection.Copy
Application.CutCopyMode = False
Range("B22:M22").Select
Selection.Copy
Range("B32").Select
ActiveSheet.Paste
Range("A32:M33").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
ActiveSheet.Paste
Sheets("BenchMarkReportExcel").Select
Range("A38").Select
Application.CutCopyMode = False
Selection.Copy
ActiveSheet.Next.Select
ActiveSheet.Next.Select
Range("A13").Select
ActiveSheet.Paste
ActiveSheet.Previous.Select
ActiveSheet.Previous.Select
ActiveSheet.Next.Select
ActiveSheet.Next.Select
Range("B13").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(""Mandideep"",BenchMarkReportExcel!R[20]C[-1]:R[26]C[11],2,FALSE)"
Range("C13").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-1]=BenchMarkReportExcel!R[25]C[-1],BenchMarkReportExcel!R[25]C,"""")"
Range("C13").Select
Selection.AutoFill Destination:=Range("C13:M13"), Type:=xlFillDefault
Range("C13:M13").Select
Range("A15").Select
ActiveSheet.Previous.Select
ActiveSheet.Previous.Select
Range("N43:N49").Select
Selection.Copy
Range("A43").Select
ActiveSheet.Paste
Range("A42:M43").Select
Application.CutCopyMode = False
Selection.Copy
ActiveSheet.Next.Select
ActiveSheet.Next.Select
Range("A16").Select
ActiveSheet.Paste
Range("A18").Select
ActiveSheet.Previous.Select
ActiveSheet.Previous.Select
Range("A48").Select
Application.CutCopyMode = False
Selection.Copy
ActiveSheet.Next.Select
ActiveSheet.Next.Select
ActiveSheet.Paste
ActiveSheet.Previous.Select
ActiveSheet.Previous.Select
Range("B48").Select
ActiveSheet.Next.Select
ActiveSheet.Next.Select
Range("B18").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(""Mandideep"",BenchMarkReportExcel!R[25]C[-1]:R[31]C[11],2,FALSE)"
Range("C18").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-1]=BenchMarkReportExcel!R[30]C[-1],BenchMarkReportExcel!R[30]C,"""")"
Range("C18").Select
Selection.AutoFill Destination:=Range("C18:M18"), Type:=xlFillDefault
Range("C18:M18").Select
Range("A21").Select
ActiveSheet.Previous.Select
ActiveSheet.Previous.Select
Range("N53:N59").Select
Selection.Copy
Range("A53").Select
ActiveSheet.Paste
Range("A52:M53").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
ActiveSheet.Paste
Range("A23").Select
ActiveSheet.Previous.Select
ActiveSheet.Previous.Select
Range("A58").Select
Application.CutCopyMode = False
Selection.Copy
ActiveSheet.Next.Select
ActiveSheet.Next.Select
Application.CutCopyMode = False
Selection.Copy
ActiveSheet.Paste
ActiveSheet.Previous.Select
ActiveSheet.Previous.Select
Application.CutCopyMode = False
Selection.Copy
ActiveSheet.Next.Select
ActiveSheet.Next.Select
ActiveSheet.Paste
Range("B23").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(""Mandideep"",BenchMarkReportExcel!R[35]C,2,FALSE)"
Range("B23").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(""Mandideep"",BenchMarkReportExcel!R[35]C,2,FALSE)"
Sheets("Sheet2").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(""Mandideep"",BenchMarkReportExcel!R[30]C[-1]:R[36]C[11],2,FALSE)"
Range("C23").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-1]=BenchMarkReportExcel!R[35]C[-1],BenchMarkReportExcel!R[35]C,"""")"
Range("C23").Select
Selection.AutoFill Destination:=Range("C23:M23"), Type:=xlFillDefault
Range("C23:M23").Select
Range("A25").Select
Sheets("BenchMarkReportExcel").Select
Range("N63:N69").Select
Selection.Copy
Range("A63").Select
ActiveSheet.Paste
Range("A62:M63").Select
Application.CutCopyMode = False
Selection.Copy
ActiveSheet.Next.Select
ActiveSheet.Next.Select
ActiveSheet.Paste
Range("A27").Select
ActiveSheet.Previous.Select
ActiveSheet.Previous.Select
Range("A68").Select
Application.CutCopyMode = False
Selection.Copy
Range("A69").Select
ActiveSheet.Next.Select
ActiveSheet.Next.Select
ActiveSheet.Paste
Range("B27").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = _
"=vllokup(""Mandideep"",BenchMarkReportExcel!R[36]C[-1]:R[42]C[11],2,FALSE)"
Range("B27").Select
ActiveCell.FormulaR1C1 = _
"=vlokup(""Mandideep"",BenchMarkReportExcel!R[36]C[-1]:R[42]C[11],2,FALSE)"
Range("B27").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(""Mandideep"",BenchMarkReportExcel!R[36]C[-1]:R[42]C[11],2,FALSE)"
Range("C27").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-1]=BenchMarkReportExcel!R[41]C[-1],BenchMarkReportExcel!R[41]C,"""")"
Range("C27").Select
Selection.AutoFill Destination:=Range("C27:M27"), Type:=xlFillDefault
Range("C27:M27").Select
Range("A30").Select
Sheets("BenchMarkReportExcel").Select
ActiveWindow.SmallScroll Down:=24
Range("N73:N79").Select
Selection.Copy
Range("A73").Select
ActiveSheet.Paste
Range("A72:N73").Select
Application.CutCopyMode = False
Selection.Copy
ActiveSheet.Next.Select
ActiveSheet.Next.Select
ActiveSheet.Paste
Range("N30:N31").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("A32").Select
ActiveSheet.Previous.Select
ActiveSheet.Previous.Select
Range("A78").Select
Selection.Copy
ActiveSheet.Next.Select
ActiveSheet.Next.Select
ActiveSheet.Paste
Range("B32").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(""Mandideep"",BenchMarkReportExcel!R[41]C[-1]:R[47]C[11],2,FALSE)"
Range("C32").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-1]=BenchMarkReportExcel!R[46]C[-1],BenchMarkReportExcel!R[46]C,"""")"
Range("C32").Select
Selection.AutoFill Destination:=Range("C32:N32"), Type:=xlFillDefault
Range("C32:N32").Select
Range("N30:N32").Select
Selection.ClearContents
Range("A34").Select
Sheets("BenchMarkReportExcel").Select
Range("N83:N89").Select
Selection.Copy
Range("A83").Select
ActiveSheet.Paste
Range("A82:M83").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
ActiveSheet.Paste
ActiveWindow.SmallScroll Down:=13
Sheets("BenchMarkReportExcel").Select
Range("A88").Select
Application.CutCopyMode = False
Selection.Copy
Range("A18:A88").Select
Range("A88").Activate
Application.CutCopyMode = False
Range("A88").Select
Selection.Copy
ActiveSheet.Next.Select
ActiveSheet.Next.Select
Range("A36").Select
ActiveSheet.Paste
Range("B36").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(""Mandideep"",BenchMarkReportExcel!R[47]C[-1]:R[53]C[11],2,FALSE)"
Range("C36").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-1]=BenchMarkReportExcel!R[52]C[-1],BenchMarkReportExcel!R[52]C,"""")"
Range("C36").Select
Selection.AutoFill Destination:=Range("C36:M36"), Type:=xlFillDefault
Range("C36:M36").Select
Range("A39").Select
ActiveSheet.Previous.Select
ActiveSheet.Previous.Select
ActiveWindow.SmallScroll Down:=12
Range("A92:M93").Select
Selection.Copy
ActiveSheet.Next.Select
ActiveSheet.Next.Select
ActiveSheet.Paste
Range("A41").Select
ActiveSheet.Previous.Select
ActiveSheet.Previous.Select
Range("A97").Select
Application.CutCopyMode = False
Selection.Copy
ActiveSheet.Next.Select
ActiveSheet.Next.Select
ActiveSheet.Paste
Range("B41").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(""Mandideep"",BenchMarkReportExcel!R[51]C[-1]:R[58]C[11],2,FALSE)"
Range("C41").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-1]=BenchMarkReportExcel!R[56]C[-1],BenchMarkReportExcel!R[56]C,"""")"
Range("C41").Select
Selection.AutoFill Destination:=Range("C41:M41"), Type:=xlFillDefault
Range("C41:M41").Select
Range("A43").Select
Sheets("BenchMarkReportExcel").Select
Range("N103:N109").Select
Selection.Copy
Range("A103").Select
ActiveSheet.Paste
Range("A102:M103").Select
Application.CutCopyMode = False
Selection.Copy
ActiveSheet.Next.Select
ActiveSheet.Next.Select
ActiveSheet.Paste
Range("A45").Select
ActiveSheet.Previous.Select
ActiveSheet.Previous.Select
Range("A108").Select
Application.CutCopyMode = False
Selection.Copy
ActiveSheet.Next.Select
ActiveSheet.Next.Select
ActiveSheet.Paste
Range("B45").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(""Mandideep"",BenchMarkReportExcel!R[58]C[-1]:R[64]C[11],2,FALSE)"
Range("C45").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-1]=BenchMarkReportExcel!R[63]C[-1],BenchMarkReportExcel!R[63]C,"""")"
Range("C45").Select
Selection.AutoFill Destination:=Range("C45:M45"), Type:=xlFillDefault
Range("C45:M45").Select
Range("A48").Select
Sheets("BenchMarkReportExcel").Select
Range("N113:N119").Select
Selection.Copy
Range("A113").Select
ActiveSheet.Paste
Range("A112:M113").Select
Application.CutCopyMode = False
Selection.Copy
ActiveSheet.Next.Select
ActiveSheet.Next.Select
ActiveSheet.Paste
ActiveSheet.Previous.Select
ActiveSheet.Previous.Select
Range("A119").Select
Application.CutCopyMode = False
Selection.Copy
ActiveSheet.Next.Select
ActiveSheet.Next.Select
Range("A50").Select
ActiveSheet.Paste
ActiveSheet.Previous.Select
ActiveSheet.Previous.Select
ActiveSheet.Next.Select
ActiveSheet.Next.Select
Range("B50").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(""Mandideep"",BenchMarkReportExcel!R[63]C[-1]:R[69]C[11],2,FALSE)"
Range("C50").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-1]=BenchMarkReportExcel!R[69]C[-1],BenchMarkReportExcel!R[69]C,"""")"
Range("C50").Select
Selection.AutoFill Destination:=Range("C50:M50"), Type:=xlFillDefault
Range("C50:M50").Select
Range("A52").Select
Sheets("BenchMarkReportExcel").Select
Range("N123:N129").Select
Selection.Copy
Range("A123").Select
ActiveSheet.Paste
Range("A122:M123").Select
Application.CutCopyMode = False
Selection.Copy
ActiveSheet.Next.Select
ActiveSheet.Next.Select
ActiveSheet.Paste
ActiveSheet.Previous.Select
ActiveSheet.Previous.Select
Range("A129").Select
Application.CutCopyMode = False
Selection.Copy
ActiveSheet.Next.Select
ActiveSheet.Next.Select
Range("A54").Select
ActiveSheet.Paste
Range("B54").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=VLOOKUP"
Range("B53").Select
ActiveCell.FormulaR1C1 = "=VL"
Range("B54").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(""Mandideep"",BenchMarkReportExcel!R[69]C[-1]:R[75]C[11],2,0)"
Range("C54").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-1]=BenchMarkReportExcel!R[75]C[-1],BenchMarkReportExcel!R[75]C,"""")"
Range("C54").Select
Selection.AutoFill Destination:=Range("C54:M54"), Type:=xlFillDefault
Range("C54:M54").Select
Range("A56").Select
Sheets("BenchMarkReportExcel").Select
Range("N133:N139").Select
Selection.Copy
Range("A133").Select
ActiveSheet.Paste
Range("A132:M133").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
ActiveSheet.Paste
Range("A58").Select
ActiveSheet.Previous.Select
ActiveSheet.Previous.Select
Range("A139").Select
Application.CutCopyMode = False
Selection.Copy
ActiveSheet.Next.Select
ActiveSheet.Next.Select
ActiveSheet.Paste
ActiveSheet.Previous.Select
ActiveSheet.Previous.Select
Range("A137").Select
Application.CutCopyMode = False
Selection.Copy
ActiveSheet.Next.Select
ActiveSheet.Next.Select
ActiveSheet.Paste
Range("B58").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(""Mandideep"",BenchMarkReportExcel!R[75]C[-1]:R[81]C[11],2,FALSE)"
Range("C58").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-1]=BenchMarkReportExcel!R[79]C[-1],BenchMarkReportExcel!R[79]C,"""")"
Range("C58").Select
Selection.AutoFill Destination:=Range("C58:M58"), Type:=xlFillDefault
Range("C58:M58").Select
Range("A60").Select
ActiveSheet.Previous.Select
ActiveSheet.Previous.Select
Range("N143:N149").Select
Selection.Copy
Range("A143").Select
ActiveSheet.Paste
Range("A142:M143").Select
Application.CutCopyMode = False
Selection.Copy
ActiveSheet.Next.Select
ActiveSheet.Next.Select
ActiveSheet.Paste
Range("A62").Select
ActiveSheet.Previous.Select
ActiveSheet.Previous.Select
Range("A147").Select
Application.CutCopyMode = False
Selection.Copy
ActiveSheet.Next.Select
ActiveSheet.Next.Select
ActiveSheet.Paste
Range("B62").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(""Mandideep"",BenchMarkReportExcel!R[91]C[-1]:R[97]C[11],2,FALSE)"
Range("C62").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-1]=BenchMarkReportExcel!R[95]C[-1],BenchMarkReportExcel!R[95]C,"""")"
Range("C62").Select
Selection.AutoFill Destination:=Range("C62:M62"), Type:=xlFillDefault
Range("C62:M62").Select
Range("A64").Select
Sheets("BenchMarkReportExcel").Select
Range("N153:N159").Select
Selection.Copy
Range("A153").Select
ActiveSheet.Paste
Range("A152:M153").Select
Application.CutCopyMode = False
Selection.Copy
ActiveSheet.Next.Select
ActiveSheet.Next.Select
ActiveSheet.Paste
Range("A66").Select
ActiveSheet.Previous.Select
ActiveSheet.Previous.Select
Range("A157").Select
Application.CutCopyMode = False
Selection.Copy
ActiveSheet.Next.Select
ActiveSheet.Next.Select
ActiveSheet.Paste
ActiveSheet.Previous.Select
ActiveSheet.Previous.Select
Range("B157").Select
ActiveSheet.Next.Select
ActiveSheet.Next.Select
Range("B66").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(""Mandideep"",BenchMarkReportExcel!R[87]C[-1]:R[93]C[11],2,FALSE)"
Range("C66").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-1]=BenchMarkReportExcel!R[91]C[-1],BenchMarkReportExcel!R[91]C,"""")"
Range("C66").Select
Selection.AutoFill Destination:=Range("C66:M66"), Type:=xlFillDefault
Range("C66:M66").Select
ActiveWindow.SmallScroll Down:=8
Sheets("BenchMarkReportExcel").Select
ActiveWindow.SmallScroll Down:=25
Range("N163:N169").Select
Selection.Copy
Range("A163").Select
ActiveSheet.Paste
Range("N173:N179").Select
Application.CutCopyMode = False
Selection.Copy
Range("A173").Select
ActiveSheet.Paste
Range("A171:M173").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
Range("A68").Select
ActiveSheet.Paste
Sheets("BenchMarkReportExcel").Select
Range("A179:O179").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
Range("A71").Select
ActiveSheet.Paste
Range("N71:O71").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("A68").Select
Selection.ClearContents
Range("A73").Select
Sheets("BenchMarkReportExcel").Select
ActiveWindow.SmallScroll Down:=37
Sheets("Sheet2").Select
End Sub
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Sort by date Sort by votes
This can be shortened massively. To help me, can you confirm, that for the bulk of the code, only 2 sheets are involved, one is called "BenchMarkReportExcel".
1. What's the other sheet called?
2. What's the name of the sheet you start on (the active sheet) when you run he macro?
 
Upvote 0
If there are just two sheets involved then the code might be reducible to a 14 times loop of something like this (the 6-line group hasn't been adjusted with variables, they're jut a translation of part of what you had):
Code: 
Sub bench()
'http://www.mrexcel.com/forum/showthread.php?t=556466
Set RightSheet = Sheets("Sheet2")
Set LeftSheet = Sheets("BenchMarkReportExcel")

'start loop
LeftSheet.Range("N23:N29").Copy LeftSheet.Range("A23")
LeftSheet.Range("A22:N23").Copy RightSheet.Range("A6")
LeftSheet.Range("A29").Copy RightSheet.Range("A8")
RightSheet.Range("B8").FormulaR1C1 = "=VLOOKUP(""Mandideep"",BenchMarkReportExcel!R[15]C[-1]:R[21]C[11],2,FALSE)"
RightSheet.Range("C8:M8").FormulaR1C1 = "=IF(RC[-1]=BenchMarkReportExcel!R[21]C[-1],BenchMarkReportExcel!R[21]C,"""")"
RightSheet.Range("N6:N7").ClearContents
'end loop

End Sub
However, there wasn't perfect repeating in your code, some copying seemed to missed out (I didn't spot N93:N99 being copied to column A on the LeftSheet), sometimes the last line wasn't the clearing the same cell(s), relatively speaking, and you didn't always copy the same sized ranges (1 extra column was copied over once or twice (A72:N73 instead of A72:M73), the destination rows were 4 rows apart half the time and 5 rows apart for the rest of the time - however, that was probably you making little slip-ups while doing it manually. There'll be a few lines to add right at the bottom to tidy up.

So… if the data and destination blocks are, or can be, truly repeating, then we can knock your nearly 600 lines of code to just 20 or so.
 
Upvote 0
hello,
First of all thanks for ur prompt reply. Really appreciate it!

This is copying only one set of data from the tables. I have this macro recorded again, eliminating unncessay clicks. Can it be written in a loop to make it less bulky.


Sub bench()
'
' bench Macro
Sheets("BenchMarkReportExcel").Select
Range("N13:N19").Select
Selection.Copy
Range("A13").Select
ActiveSheet.Paste
Range("N33:N39").Select
Application.CutCopyMode = False
Selection.Copy
Range("A33").Select
ActiveSheet.Paste
Range("A49").Select
Sheets("BenchMarkReportExcel").Select
Range("A12:M13").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
ActiveSheet.Paste
Range("A3").Select
Sheets("Sheet2").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = ""
Sheets("BenchMarkReportExcel").Select
Range("A19").Select
Sheets("Sheet2").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(""Mandideep"",BenchMarkReportExcel!R[10]C:R[16]C,1,FALSE)"
Range("A3").Select
Columns("A:A").ColumnWidth = 13.71
Range("B3").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(""Mandideep"",BenchMarkReportExcel!R[10]C[-1]:R[16]C[11],2,FALSE)"
Range("C3").Select
ActiveCell.FormulaR1C1 = _
"=IF(Sheet2!RC[-1]=BenchMarkReportExcel!R[16]C[-1],BenchMarkReportExcel!R[16]C,"""")"
Range("C3").Select
Selection.AutoFill Destination:=Range("C3:M3"), Type:=xlFillDefault
Range("C3:M3").Select
Range("A4").Select
Sheets("BenchMarkReportExcel").Select
ActiveWindow.SmallScroll Down:=23
Range("A32:M33").Select
Selection.Copy
Sheets("Sheet2").Select
ActiveSheet.Paste
Range("A6").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=R[-3]C"
Range("B6").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(""Mandideep"",BenchMarkReportExcel!R[27]C[-1]:R[33]C[11],2,FALSE)"
Range("C6").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-1]=BenchMarkReportExcel!R[32]C[-1],BenchMarkReportExcel!R[32]C,"""")"
Range("C6").Select
Selection.AutoFill Destination:=Range("C6:M6"), Type:=xlFillDefault
Range("C6:M6").Select
Range("A7").Select
Sheets("Sheet2").Select
Range("A8").Select
Sheets("BenchMarkReportExcel").Select
ActiveWindow.SmallScroll Down:=22
Range("N43:N49").Select
Selection.Copy
Range("A43").Select
ActiveSheet.Paste
Range("A42:M43").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
ActiveSheet.Paste
Range("A10").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=R[-4]C"
Range("B10").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-1],BenchMarkReportExcel!R[33]C[-1]:R[39]C[11],2,FALSE)"
Range("C10").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-1]=BenchMarkReportExcel!R[38]C[-1],BenchMarkReportExcel!R[38]C,"""")"
Range("C10").Select
Selection.AutoFill Destination:=Range("C10:M10"), Type:=xlFillDefault
Range("C10:M10").Select
Range("A11").Select
Sheets("BenchMarkReportExcel").Select
Range("N53:N59").Select
Selection.Copy
Range("A53").Select
ActiveSheet.Paste
Range("A52:M53").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
ActiveSheet.Paste
Range("A13").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=R[-3]C"
Range("B13").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-1],BenchMarkReportExcel!R[40]C[-1]:R[46]C[11],2,FALSE)"
Range("C13").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-1]=BenchMarkReportExcel!R[45]C[-1],BenchMarkReportExcel!R[45]C,"""")"
Range("C13").Select
Selection.AutoFill Destination:=Range("C13:M13"), Type:=xlFillDefault
Range("C13:M13").Select
Range("A14").Select
Sheets("BenchMarkReportExcel").Select
ActiveWindow.SmallScroll Down:=21
Range("N63:N69").Select
Selection.Copy
Range("A63").Select
ActiveSheet.Paste
Range("A62:M63").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
ActiveSheet.Paste
Sheets("Sheet2").Select
Range("A16").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=R[-3]C"
Range("B16").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-1],BenchMarkReportExcel!R[47]C[-1]:R[53]C[11],2,FALSE)"
Range("C16").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-1]=BenchMarkReportExcel!R[52]C[-1],BenchMarkReportExcel!R[52]C,"""")"
Range("C16").Select
Selection.AutoFill Destination:=Range("C16:M16"), Type:=xlFillDefault
Range("C16:M16").Select
Range("A17").Select
Sheets("BenchMarkReportExcel").Select
ActiveWindow.SmallScroll Down:=7
Range("N73:N79").Select
Selection.Copy
Range("A73").Select
ActiveSheet.Paste
Range("A72:M73").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
ActiveSheet.Paste
Range("A19").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=R[-3]C"
Range("B19").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-1],BenchMarkReportExcel!R[54]C[-1]:R[60]C[11],2,FALSE)"
Range("C19").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-1]=BenchMarkReportExcel!R[59]C[-1],BenchMarkReportExcel!R[59]C,"""")"
Range("C19").Select
Selection.AutoFill Destination:=Range("C19:M19"), Type:=xlFillDefault
Range("C19:M19").Select
Range("A20").Select
Sheets("BenchMarkReportExcel").Select
ActiveWindow.SmallScroll Down:=22
Range("N93:N99").Select
Selection.Copy
Range("A93").Select
ActiveSheet.Paste
Range("A92:M93").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
ActiveSheet.Paste
ActiveWindow.SmallScroll Down:=9
Range("A22").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=R[-3]C"
Range("B22").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-1],BenchMarkReportExcel!R[70]C[-1]:R[77]C[11],2,FALSE)"
Range("C22").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-1]=BenchMarkReportExcel!R[75]C[-1],BenchMarkReportExcel!R[75]C,"""")"
Range("C22").Select
Selection.AutoFill Destination:=Range("C22:M22"), Type:=xlFillDefault
Range("C22:M22").Select
Range("A23").Select
Sheets("BenchMarkReportExcel").Select
Range("N103:N109").Select
Selection.Copy
Range("A103").Select
ActiveSheet.Paste
Range("A102:M103").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
ActiveSheet.Paste
Range("A25").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=R[-6]C"
Range("B25").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-1],BenchMarkReportExcel!R[78]C[-1]:R[84]C[11],2,FALSE)"
Range("C25").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-1]=BenchMarkReportExcel!R[83]C[-1],BenchMarkReportExcel!R[83]C,"""")"
Range("C25").Select
Selection.AutoFill Destination:=Range("C25:M25"), Type:=xlFillDefault
Range("C25:M25").Select
Range("A26").Select
Sheets("BenchMarkReportExcel").Select
ActiveWindow.SmallScroll Down:=19
Range("N113:N119").Select
Selection.Copy
Range("A113").Select
ActiveSheet.Paste
Range("A112:M113").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
ActiveSheet.Paste
Range("A28").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=R[-3]C"
Range("B28").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-1],BenchMarkReportExcel!R[85]C[-1]:R[91]C[11],2,FALSE)"
Range("C28").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-1]=BenchMarkReportExcel!R[91]C[-1],BenchMarkReportExcel!R[91]C,"""")"
Range("C28").Select
Selection.AutoFill Destination:=Range("C28:M28"), Type:=xlFillDefault
Range("C28:M28").Select
Range("A29").Select
Sheets("BenchMarkReportExcel").Select
ActiveWindow.SmallScroll Down:=13
Range("N123:N129").Select
Selection.Copy
Range("A123").Select
ActiveSheet.Paste
Range("A122:M123").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
ActiveSheet.Paste
Range("A31").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=R[-3]C"
Range("B31").Select
Sheets("Sheet2").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-1],BenchMarkReportExcel!R[92]C[-1]:R[98]C[11],2,FALSE)"
Range("C31").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-1]=BenchMarkReportExcel!R[106]C[-1],BenchMarkReportExcel!R[106]C,"""")"
Range("C31").Select
Sheets("Sheet2").Select
Range("B31").Select
Sheets("Sheet2").Select
Range("C31").Select
Sheets("Sheet2").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-1]=BenchMarkReportExcel!R[98]C[-1],BenchMarkReportExcel!R[99]C,"""")"
Sheets("Sheet2").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-1]=BenchMarkReportExcel!R[98]C[-1],BenchMarkReportExcel!R[98]C,"""")"
Range("C31").Select
Selection.AutoFill Destination:=Range("C31:M31"), Type:=xlFillDefault
Range("C31:M31").Select
ActiveWindow.SmallScroll Down:=7
Range("A32").Select
Sheets("BenchMarkReportExcel").Select
ActiveWindow.SmallScroll Down:=10
Range("N133:N139").Select
Selection.Copy
Range("A133").Select
ActiveSheet.Paste
Range("A132:M133").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
ActiveSheet.Paste
Range("A34").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=R[-3]C"
Range("B34").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(Sheet2!RC[-1],BenchMarkReportExcel!R[99]C[-1]:R[105]C[11],2,FALSE)"
Range("C34").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-1]=BenchMarkReportExcel!R[103]C[-1],BenchMarkReportExcel!R[103]C,"""")"
Range("C34").Select
Selection.AutoFill Destination:=Range("C34:M34"), Type:=xlFillDefault
Range("C34:M34").Select
Range("A35").Select
Sheets("BenchMarkReportExcel").Select
Range("N143:N149").Select
Selection.Copy
Range("A143").Select
ActiveSheet.Paste
Range("A142:M143").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
ActiveSheet.Paste
Range("A37").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=R[-6]C"
Range("B37").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-1],BenchMarkReportExcel!R[106]C[-1]:R[112]C[11],2,FALSE)"
Range("C37").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-1]=BenchMarkReportExcel!R[110]C[-1],BenchMarkReportExcel!R[110]C,"""")"
Range("C37").Select
Selection.AutoFill Destination:=Range("C37:M37"), Type:=xlFillDefault
Range("C37:M37").Select
Range("A38").Select
Sheets("BenchMarkReportExcel").Select
ActiveWindow.SmallScroll Down:=31
Range("N163:N169").Select
Selection.Copy
Range("A163").Select
ActiveSheet.Paste
Range("N173:N179").Select
Application.CutCopyMode = False
Selection.Copy
Range("A173").Select
ActiveSheet.Paste
Range("A172:M173").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
ActiveSheet.Paste
ActiveWindow.SmallScroll Down:=5
Range("A40").Select
Sheets("Sheet2").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=BenchMarkReportExcel!R[139]C"
Range("B40").Select
Sheets("BenchMarkReportExcel").Select
Range("B179:M179").Select
Selection.Copy
Sheets("Sheet2").Select
ActiveSheet.Paste
Range("A41").Select
Sheets("BenchMarkReportExcel").Select
Range("B218").Select
Sheets("Sheet2").Select
End Sub
 
Upvote 0
This hasn't really answered the question: are the data and destination blocks truly repeating?
To show what's happening with your second macro, I created a BenchMarkReportExcel sheet in which all the cells had their own address in them. Then I ran your second macro, with a blank Sheet2, with its cell A1 being selected before the run (important).
This is the result (I only show column 1):
Excel Workbook
A
1A12
2N13
3#N/A
4A32
5N33
6#N/A
7
8A42
9N43
10#N/A
11A52
12N53
13#N/A
14A62
15N63
16#N/A
17A72
18N73
19#N/A
20A92
21N93
22#N/A
23A102
24N103
25#N/A
26A112
27N113
28#N/A
29A122
30N123
31#N/A
32A132
33N133
34#N/A
35A142
36N143
37#N/A
38
39
400
Sheet


1. If you look at the yellow cells, most of them jump in increments of 10, except A22 is missing, A82 is missing.
2. There is a completely blank row 7.

3. Similarly, there's some copying going on within BenchMarkReportExcel from column N to column A, and this is also not regular; N23:N29 is not copied, nor is N83:N93.

So, are 1, 2 and 3 above intentional?
If so then it's not so simple.
I can't see what'a actually on your sheet BenchMarkReportExcel so I'm working blind.
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Question
Help with Vba Code
Replies
3
Views
379
Coyotex3
C
D
  • Question
VBA optimization advice
Replies
3
Views
333
bobsan42
bobsan42
P
  • Solved
Need help with macro Dynamic Range??
Replies
6
Views
364
pennbowl
P
C
  • Question
Loop/Repeat Macro
2
Replies
11
Views
666
kvsrinivasamurthy
K
J
  • Question
Crashing Macro
2
Replies
12
Views
703
kevin9999
K

Forum statistics

Threads
1,220,965
Messages
6,157,119
Members
451,399
Latest member
alchavar

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