Hi Team,
I am trying to develop a macro to cut the second part of the report which is present in the existing sheet and then by doing vlook up to insert those values in the existing report.
Explanation:
I have a report which is split into two parts. The first part starts from A1 and then second part starts from row named PAGE 2. The second part of the report consists of 15 columns and the first part of the reports consists of 30 columns. I have to insert 14 columns in the first part of the report i.e. have to insert columns C31-C44 from second part into first set of report after C30.
One column is unique between both set of reports (C8) using which vlookup has to be done and values inserted.
Have attached a sample report and below is the coding that i am using.
My problem is that the columns are being inserted however the entire report is being moved to AZ . The newly inserted columns must appear in the original report after C30 and before U1. After inserting columns and doing vlookup --> convert to values the second part of the report must be deleted.
Can anyone please help .
I am trying to develop a macro to cut the second part of the report which is present in the existing sheet and then by doing vlook up to insert those values in the existing report.
Explanation:
I have a report which is split into two parts. The first part starts from A1 and then second part starts from row named PAGE 2. The second part of the report consists of 15 columns and the first part of the reports consists of 30 columns. I have to insert 14 columns in the first part of the report i.e. have to insert columns C31-C44 from second part into first set of report after C30.
One column is unique between both set of reports (C8) using which vlookup has to be done and values inserted.
Have attached a sample report and below is the coding that i am using.
My problem is that the columns are being inserted however the entire report is being moved to AZ . The newly inserted columns must appear in the original report after C30 and before U1. After inserting columns and doing vlookup --> convert to values the second part of the report must be deleted.
Can anyone please help .
Code:
Sub check()
'
' check Macro
'
'
Columns("AE:AE").Select
Range("AE3").Activate
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Range("AE15").Select
Sheets("Sheet1").Select
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Sheets("Report").Select
ActiveSheet.Paste
Range("H16").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Range("L3587").Select
Selection.End(xlUp).Select
Range("L16").Select
Selection.End(xlToRight).Select
Range("AE16").Select
ActiveSheet.Paste
Range("AF16").Select
ActiveWindow.SmallScroll ToRight:=17
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],Sheet1!C1:C2,2,0)"
Range("AG16").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-2],Sheet1!C1:C3,3,0)"
Range("AH16").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-3],Sheet1!C1:C4,4,0)"
Range("AI16").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-4],Sheet1!C1:C5,5,0)"
Range("AJ16").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-5],Sheet1!C1:C6,6,0)"
Range("AK16").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-6],Sheet1!C1:C7,7,0)"
Range("AL16").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-7],Sheet1!C1:C8,8,0)"
Range("AM16").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-8],Sheet1!C1:C9,9,0)"
Range("AN16").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-9],Sheet1!C1:C10,10,0)"
Range("AO16").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-10],Sheet1!C1:C11,11,0)"
Range("AP16").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-11],Sheet1!C1:C12,12,0)"
Range("AQ16").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-12],Sheet1!C1:C13,13,0)"
Range("AR16").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-13],Sheet1!C1:C14,14,0)"
Range("AS16").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-14],Sheet1!C1:C15,15,0)"
Range("AS17").Select
Selection.End(xlToLeft).Select
Selection.End(xlDown).Select
Range("AF3609:AS3609").Select
Range(Selection, Selection.End(xlUp)).Select
Selection.FillDown
ActiveWorkbook.Save
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("AE:AE").Select
Range("AE3591").Activate
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Range("A1:AY1").Select
ActiveWorkbook.Save
End Sub