Hi,
I am very new to VBA and macros.This website really helped me in pick up some points regarding these topics.
Query:
I have 2 excels(base and reference) from which i need the data to be transferred to a new excel sheet.
For example,Excel A is base data and Excel B is reference data, so i want a macro which looks base data in reference data and gives me new data in the new excel sheet.
Chandoo and all the VBA/Macro experts can you please help me defying the macro for this ?
The macro i recorded is something as below:
I KNOW ITS NOT COMPLETE AND UNCLEAR AND COULDNT ATTACH THE SPREADSHEET TO EXPLAIN CLEARLY.
ActiveCell.FormulaR1C1 = "='FP space'!RC[-2]"
Range("C9"
.Select
ActiveCell.FormulaR1C1 = "='FP space'!R[-7]C[-1]"
Range("C10"
.Select
ActiveCell.FormulaR1C1 = "='FP space'!R[-8]C"
Range("C11"
.Select
Sheets("Front Sheet"
.Select
Sheets.Add
ActiveCell.FormulaR1C1 = "Store no"
Range("B1"
.Select
ActiveCell.FormulaR1C1 = "Merch group"
Range("C1"
.Select
ActiveCell.FormulaR1C1 = "Space"
Columns("C:C"
.Select
Columns("B:B"
.EntireColumn.AutoFit
Range("A2"
.Select
ActiveCell.FormulaR1C1 = _
"=IF('Front Sheet'!R[20]C[10]='Front Sheet'!R[8]C[2],'Front Sheet'!RC[2],""NA""
"
Range("A2"
.Select
Selection.Copy
Application.CutCopyMode = False
Range("A2"
.Select
Selection.AutoFill Destination:=Range("A2:C2"
, Type:=xlFillDefault
Range("A2:C2"
.Select
Range("B2"
.Select
ActiveCell.FormulaR1C1 = _
"=IF('Front Sheet'!R[20]C[10]='Front Sheet'!R[8]C[2],'Front Sheet'!R[9]C[2],""NA""
"
Range("B2"
.Select
ActiveCell.FormulaR1C1 = _
"=IF('Front Sheet'!R[20]C[10]='Front Sheet'!R[8]C[1],'Front Sheet'!R[9]C[2],""NA""
"
Range("B2"
.Select
ActiveCell.FormulaR1C1 = _
"=IF('Front Sheet'!R[20]C[9]='Front Sheet'!R[8]C[1],'Front Sheet'!R[9]C[2],""NA""
"
Range("A2"
.Select
ActiveCell.FormulaR1C1 = _
"=IF('Front Sheet'!R22C11='Front Sheet'!R10C3,'Front Sheet'!R2C3,""NA""
"
Range("B2"
.Select
ActiveCell.FormulaR1C1 = _
"=IF('Front Sheet'!R22C11='Front Sheet'!R10C3,'Front Sheet'!R11C4,""NA""
"
Range("B2"
.Select
Selection.AutoFill Destination:=Range("B2:C2"
, Type:=xlFillDefault
Range("B2:C2"
.Select
Range("C2"
.Select
ActiveCell.FormulaR1C1 = _
"=IF('Front Sheet'!R22C11='Front Sheet'!R10C3,'Front Sheet'!R[9]C[8],""NA""
"
Range("A2:C2"
.Select
Selection.AutoFill Destination:=Range("A2:C12"
, Type:=xlFillDefault
Range("A2:C12"
.Select
Range("B2"
.Select
ActiveCell.FormulaR1C1 = _
"=IF('Front Sheet'!R22C11='Front Sheet'!R10C3,'Front Sheet'!R[9]C4,""NA""
"
Range("B2"
.Select
Selection.AutoFill Destination:=Range("B2:B11"
, Type:=xlFillDefault
Range("B2:B11"
.Select
Selection.AutoFill Destination:=Range("B2:B12"
, Type:=xlFillDefault
Range("B2:B12"
.Select
Sheets("Sheet2"
.Select
Range("C2"
.Select
ActiveCell.FormulaR1C1 = _
"=IF('Front Sheet'!R22C11='Front Sheet'!R10C3,'Front Sheet'!R[9]C11,""NA""
"
Range("C2"
.Select
Selection.AutoFill Destination:=Range("C2:C12"
, Type:=xlFillDefault
Range("C2:C12"
.Select
Range("A1"
.Select
Sheets("Front Sheet"
.Select
Range("C2"
.Select
ActiveCell.FormulaR1C1 = "='FP space'!R[1]C[-2]"
Range("C9"
.Select
ActiveCell.FormulaR1C1 = "='FP space'!R[-6]C[-1]"
Range("C10"
.Select
ActiveCell.FormulaR1C1 = "='FP space'!R[-7]C"
Range("C10"
.Select
ActiveCell.FormulaR1C1 = "='FP space'!R[-7]C"
Range("C11"
.Select
ActiveSheet.ClearArrows
Range("C2"
.Select
ActiveCell.FormulaR1C1 = "2007"
Range("C9"
.Select
ActiveCell.FormulaR1C1 = "1"
Range("C10"
.Select
ActiveCell.FormulaR1C1 = "14"
Range("C11"
.Select
ActiveSheet.ClearArrows
I am very new to VBA and macros.This website really helped me in pick up some points regarding these topics.
Query:
I have 2 excels(base and reference) from which i need the data to be transferred to a new excel sheet.
For example,Excel A is base data and Excel B is reference data, so i want a macro which looks base data in reference data and gives me new data in the new excel sheet.
Chandoo and all the VBA/Macro experts can you please help me defying the macro for this ?
The macro i recorded is something as below:
I KNOW ITS NOT COMPLETE AND UNCLEAR AND COULDNT ATTACH THE SPREADSHEET TO EXPLAIN CLEARLY.
ActiveCell.FormulaR1C1 = "='FP space'!RC[-2]"
Range("C9"
ActiveCell.FormulaR1C1 = "='FP space'!R[-7]C[-1]"
Range("C10"
ActiveCell.FormulaR1C1 = "='FP space'!R[-8]C"
Range("C11"
Sheets("Front Sheet"
Sheets.Add
ActiveCell.FormulaR1C1 = "Store no"
Range("B1"
ActiveCell.FormulaR1C1 = "Merch group"
Range("C1"
ActiveCell.FormulaR1C1 = "Space"
Columns("C:C"
Columns("B:B"
Range("A2"
ActiveCell.FormulaR1C1 = _
"=IF('Front Sheet'!R[20]C[10]='Front Sheet'!R[8]C[2],'Front Sheet'!RC[2],""NA""
Range("A2"
Selection.Copy
Application.CutCopyMode = False
Range("A2"
Selection.AutoFill Destination:=Range("A2:C2"
Range("A2:C2"
Range("B2"
ActiveCell.FormulaR1C1 = _
"=IF('Front Sheet'!R[20]C[10]='Front Sheet'!R[8]C[2],'Front Sheet'!R[9]C[2],""NA""
Range("B2"
ActiveCell.FormulaR1C1 = _
"=IF('Front Sheet'!R[20]C[10]='Front Sheet'!R[8]C[1],'Front Sheet'!R[9]C[2],""NA""
Range("B2"
ActiveCell.FormulaR1C1 = _
"=IF('Front Sheet'!R[20]C[9]='Front Sheet'!R[8]C[1],'Front Sheet'!R[9]C[2],""NA""
Range("A2"
ActiveCell.FormulaR1C1 = _
"=IF('Front Sheet'!R22C11='Front Sheet'!R10C3,'Front Sheet'!R2C3,""NA""
Range("B2"
ActiveCell.FormulaR1C1 = _
"=IF('Front Sheet'!R22C11='Front Sheet'!R10C3,'Front Sheet'!R11C4,""NA""
Range("B2"
Selection.AutoFill Destination:=Range("B2:C2"
Range("B2:C2"
Range("C2"
ActiveCell.FormulaR1C1 = _
"=IF('Front Sheet'!R22C11='Front Sheet'!R10C3,'Front Sheet'!R[9]C[8],""NA""
Range("A2:C2"
Selection.AutoFill Destination:=Range("A2:C12"
Range("A2:C12"
Range("B2"
ActiveCell.FormulaR1C1 = _
"=IF('Front Sheet'!R22C11='Front Sheet'!R10C3,'Front Sheet'!R[9]C4,""NA""
Range("B2"
Selection.AutoFill Destination:=Range("B2:B11"
Range("B2:B11"
Selection.AutoFill Destination:=Range("B2:B12"
Range("B2:B12"
Sheets("Sheet2"
Range("C2"
ActiveCell.FormulaR1C1 = _
"=IF('Front Sheet'!R22C11='Front Sheet'!R10C3,'Front Sheet'!R[9]C11,""NA""
Range("C2"
Selection.AutoFill Destination:=Range("C2:C12"
Range("C2:C12"
Range("A1"
Sheets("Front Sheet"
Range("C2"
ActiveCell.FormulaR1C1 = "='FP space'!R[1]C[-2]"
Range("C9"
ActiveCell.FormulaR1C1 = "='FP space'!R[-6]C[-1]"
Range("C10"
ActiveCell.FormulaR1C1 = "='FP space'!R[-7]C"
Range("C10"
ActiveCell.FormulaR1C1 = "='FP space'!R[-7]C"
Range("C11"
ActiveSheet.ClearArrows
Range("C2"
ActiveCell.FormulaR1C1 = "2007"
Range("C9"
ActiveCell.FormulaR1C1 = "1"
Range("C10"
ActiveCell.FormulaR1C1 = "14"
Range("C11"
ActiveSheet.ClearArrows