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".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