Hi,
I created a recorded macro. What im doing is downloading a file from a database and have to update that file with the old data from prior download and extract the formats and formulas into the new download. My problem is when i did the recorded macro I copied the formula from the prior tab and had to manually change the tab name and used the auto fill to capture the old data into the new. I know there will be times that the formulas will have to go down further which is 3 columns and it wont be able to capture all the data from the old as there can be longer ranges. Any ideas and is there a way to hide the formulas and be able still be able to apply changes once the macro is ran? Hopefully someone can help. thanks in advance!
*note: The 3 columns may need to be longer ranges is
Marketer,Doctor,Action Taken
Is there a way when i bring the new data i dont have to change tab names into a new tab for the formula. The tab im extracting and copying from is named "OLD". How can i recreate this process?
Again any help will be most appreciated. thanks so much!
Sub Macro3()
'
' Macro3 Macro
'
' Keyboard Shortcut: Ctrl+k
'
ActiveCell.FormulaR1C1 = "Marketer"
Range("J1").Select
ActiveCell.FormulaR1C1 = "Doctor"
Range("K1").Select
ActiveCell.FormulaR1C1 = "Action Taken"
Range("I2").Select
Sheets("NEW").Select
Range("I2:K2").Select
ActiveWindow.SmallScroll Down:=-18
Selection.Copy
Sheets("Unprocessed EOEs").Select
ActiveSheet.Paste
Range("I2").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = _
"=IF(ISERROR(VLOOKUP(RC3,OLD!R2C3:R250C11,7,FALSE)=TRUE),"" "",VLOOKUP(RC3,OLD!R2C3:R250C11,7,FALSE))"
Range("J2").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISERROR(VLOOKUP(RC3,OLD!R2C3:R250C11,8,FALSE)=TRUE),"" "",VLOOKUP(RC3,OLD!R2C3:R250C11,8,FALSE))"
Range("K2").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISERROR(VLOOKUP(RC3,OLD!R2C3:R250C11,9,FALSE)=TRUE),"" "",VLOOKUP(RC3,OLD!R2C3:R250C11,9,FALSE))"
Range("I2").Select
Selection.AutoFill Destination:=Range("I2:I46")
Range("I2:I46").Select
ActiveWindow.SmallScroll Down:=-3
Range("J2").Select
Selection.AutoFill Destination:=Range("J2:J46")
Range("J2:J46").Select
Range("K2").Select
Selection.AutoFill Destination:=Range("K2:K46")
Range("K2:K46").Select
Range("K7").Select
ActiveWindow.SmallScroll Down:=15
Range("I45").Select
ActiveWindow.SmallScroll Down:=-33
Columns("A:Q").Select
Columns("A:Q").EntireColumn.AutoFit
Rows("1:1").Select
Selection.Font.Bold = True
Selection.Font.Underline = xlUnderlineStyleSingle
Rows("1:1").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveWorkbook.Worksheets("Unprocessed EOEs").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Unprocessed EOEs").Sort.SortFields.Add Key:=Range( _
"J2:J46"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Unprocessed EOEs").Sort
.SetRange Range("A1:K46")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("A1").Select
End Sub
I created a recorded macro. What im doing is downloading a file from a database and have to update that file with the old data from prior download and extract the formats and formulas into the new download. My problem is when i did the recorded macro I copied the formula from the prior tab and had to manually change the tab name and used the auto fill to capture the old data into the new. I know there will be times that the formulas will have to go down further which is 3 columns and it wont be able to capture all the data from the old as there can be longer ranges. Any ideas and is there a way to hide the formulas and be able still be able to apply changes once the macro is ran? Hopefully someone can help. thanks in advance!
*note: The 3 columns may need to be longer ranges is
Marketer,Doctor,Action Taken
Is there a way when i bring the new data i dont have to change tab names into a new tab for the formula. The tab im extracting and copying from is named "OLD". How can i recreate this process?
Again any help will be most appreciated. thanks so much!
Sub Macro3()
'
' Macro3 Macro
'
' Keyboard Shortcut: Ctrl+k
'
ActiveCell.FormulaR1C1 = "Marketer"
Range("J1").Select
ActiveCell.FormulaR1C1 = "Doctor"
Range("K1").Select
ActiveCell.FormulaR1C1 = "Action Taken"
Range("I2").Select
Sheets("NEW").Select
Range("I2:K2").Select
ActiveWindow.SmallScroll Down:=-18
Selection.Copy
Sheets("Unprocessed EOEs").Select
ActiveSheet.Paste
Range("I2").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = _
"=IF(ISERROR(VLOOKUP(RC3,OLD!R2C3:R250C11,7,FALSE)=TRUE),"" "",VLOOKUP(RC3,OLD!R2C3:R250C11,7,FALSE))"
Range("J2").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISERROR(VLOOKUP(RC3,OLD!R2C3:R250C11,8,FALSE)=TRUE),"" "",VLOOKUP(RC3,OLD!R2C3:R250C11,8,FALSE))"
Range("K2").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISERROR(VLOOKUP(RC3,OLD!R2C3:R250C11,9,FALSE)=TRUE),"" "",VLOOKUP(RC3,OLD!R2C3:R250C11,9,FALSE))"
Range("I2").Select
Selection.AutoFill Destination:=Range("I2:I46")
Range("I2:I46").Select
ActiveWindow.SmallScroll Down:=-3
Range("J2").Select
Selection.AutoFill Destination:=Range("J2:J46")
Range("J2:J46").Select
Range("K2").Select
Selection.AutoFill Destination:=Range("K2:K46")
Range("K2:K46").Select
Range("K7").Select
ActiveWindow.SmallScroll Down:=15
Range("I45").Select
ActiveWindow.SmallScroll Down:=-33
Columns("A:Q").Select
Columns("A:Q").EntireColumn.AutoFit
Rows("1:1").Select
Selection.Font.Bold = True
Selection.Font.Underline = xlUnderlineStyleSingle
Rows("1:1").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveWorkbook.Worksheets("Unprocessed EOEs").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Unprocessed EOEs").Sort.SortFields.Add Key:=Range( _
"J2:J46"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Unprocessed EOEs").Sort
.SetRange Range("A1:K46")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("A1").Select
End Sub
