Recorded Macro - fill ranges - new tabs - hide formulas? Thank you :)

[Rickster7]

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

 

[Michael M]

This might be a start

Sub Macro3()
Dim lr As Long
lr = Cells(Rows.Count, "A").End(xlUp).Row
Range("A1").Value = "Marketer"
Range("J1").Value = "Doctor"
Range("K1").Value = "Action Taken"
Sheets("NEW").Range("I2:K2").Copy Destination:=Sheets("Unprocessed EOEs").Range("A1")
Sheets("Unprocessed EOEs").Activate
Range("I2:I" & lr).Formula = "=IF(ISERROR(VLOOKUP($C2,OLD!$C$2:$K$250,7,FALSE)=TRUE),"" "",VLOOKUP($C2,OLD!$C$2:$K$250,7,FALSE))"
Range("J2:J" & lr).Formula = "=IF(ISERROR(VLOOKUP($C2,OLD!$C$2:$K$250,8,FALSE)=TRUE),"" "",VLOOKUP($C2,OLD!$C$2:$K$250,8,FALSE))"
Range("K2:K" & lr).Formula = "=IF(ISERROR(VLOOKUP($C2,OLD!$C$2:$K$250,9,FALSE)=TRUE),"" "",VLOOKUP($C2,OLD!$C$2:$K$250,9,FALSE))"
Columns("A:Q").EntireColumn.AutoFit
With Rows("1:1")
.Font.Bold = True
.Font.Underline = xlUnderlineStyleSingle
End With
Rows("1:" & lr).Select
Worksheets("Unprocessed EOEs").Sort.SortFields.Clear
Worksheets("Unprocessed EOEs").Sort.SortFields.Add Key:=Range( _
"J2:J" & lr), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Unprocessed EOEs").Sort
.SetRange Range("A1:K" & lr)
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub

 

[Rickster7]

wow nicely done. thank you! Is there a way to hide the formulas and still be able to edit the cells? Also I noticed the first 3 headings disappeared when I ran the macro. A B C has headers in the original upload. Do you think it would be better to insert the formula instead of having to have copied it. would it make a difference since its already in the macro? From the looks I will transfer the updated data into the OLD tab and run a new one with new data so I wont have to be changing tabs names. Does that make sense. sorry for all the questions. trying so hard here and you are a blessing in disguise.

 

[Michael M]

hiding formulas.....you could hide the formula bar !, but hiding the formulas is fairly pointless as they can be made visible fairly easily.
The formulas are inserted in the code I used, rather than copied. It's faster that way

 

[Rickster7]

oic. the formulas are not important for the user since they are going to add and delete data in the empty and non empty cells. just thought there was a way to rid of the formula after the macro was ran. would you know why the headers in A B C disappeared when I ran your macro? once again thanks so much!

 

[Michael M]

If you only want to change the formulas to values that's a different matter....I thought you actually wanted to "hide" them.
This will replace the formulas with values

Sub Macro3()
Dim lr As Long
lr = Cells(Rows.Count, "A").End(xlUp).Row
Range("A1").Value = "Marketer"
Range("J1").Value = "Doctor"
Range("K1").Value = "Action Taken"
Sheets("NEW").Range("I2:K2").Copy Destination:=Sheets("Unprocessed EOEs").Range("A2")
Sheets("Unprocessed EOEs").Activate
Range("I2:I" & lr).Formula = "=IF(ISERROR(VLOOKUP($C2,OLD!$C$2:$K$250,7,FALSE)=TRUE),"" "",VLOOKUP($C2,OLD!$C$2:$K$250,7,FALSE))"
Range("J2:J" & lr).Formula = "=IF(ISERROR(VLOOKUP($C2,OLD!$C$2:$K$250,8,FALSE)=TRUE),"" "",VLOOKUP($C2,OLD!$C$2:$K$250,8,FALSE))"
Range("K2:K" & lr).Formula = "=IF(ISERROR(VLOOKUP($C2,OLD!$C$2:$K$250,9,FALSE)=TRUE),"" "",VLOOKUP($C2,OLD!$C$2:$K$250,9,FALSE))"
Range("I2:K" & lr).Value = Range("I2:K" & lr).Value
Columns("A:Q").EntireColumn.AutoFit
With Rows("1:1")
.Font.Bold = True
.Font.Underline = xlUnderlineStyleSingle
End With
Rows("1:" & lr).Select
Worksheets("Unprocessed EOEs").Sort.SortFields.Clear
Worksheets("Unprocessed EOEs").Sort.SortFields.Add Key:=Range( _
"J2:J" & lr), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Unprocessed EOEs").Sort
.SetRange Range("A1:K" & lr)
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub

Which sheet are the headers disappearing in ???

 

[Rickster7]

ah the thing problme is i inserted columns I1,J1,J2 so I changed that back. its getting there. thank you so much for this. this is my first post on here and am delighted to get help. I test this out more tomorrow. have a good night

 

[Rickster7]

Hi Michael,

Here is part of the macro was possible with your help. However, I just need to incorporate formatting. On the original the format is
With Rows("1:1")
.Font.Bold = True
.Font.Underline = xlUnderlineStyleSingle
End With
Rows("1:" & lr).Select

And now need to change it to

Range("A1:K1").Select
Selection.Font.Bold = True
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
.PatternTintAndShade = 0
End With
With Selection.Font
.ThemeColor = xlThemeColorDark1
.TintAndShade = 0
End With
End Sub

Any chance you can help me. I used a recorded macro for the new format and want to add it to the original.Wish I knew how to repay you buddy. Thanks a million!

 

[Michael M]

Maybe this....UNTESTED

Sub MM1()
Dim lr As Long
lr = Cells(Rows.Count, "A").End(xlUp).Row
Range("A1").Value = "Marketer"
Range("J1").Value = "Doctor"
Range("K1").Value = "Action Taken"
Sheets("NEW").Range("I2:K2").Copy Destination:=Sheets("Unprocessed EOEs").Range("A2")
Sheets("Unprocessed EOEs").Activate
Range("I2:I" & lr).Formula = "=IF(ISERROR(VLOOKUP($C2,OLD!$C$2:$K$250,7,FALSE)=TRUE),"" "",VLOOKUP($C2,OLD!$C$2:$K$250,7,FALSE))"
Range("J2:J" & lr).Formula = "=IF(ISERROR(VLOOKUP($C2,OLD!$C$2:$K$250,8,FALSE)=TRUE),"" "",VLOOKUP($C2,OLD!$C$2:$K$250,8,FALSE))"
Range("K2:K" & lr).Formula = "=IF(ISERROR(VLOOKUP($C2,OLD!$C$2:$K$250,9,FALSE)=TRUE),"" "",VLOOKUP($C2,OLD!$C$2:$K$250,9,FALSE))"
Range("I2:K" & lr).Value = Range("I2:K" & lr).Value
Columns("A:Q").EntireColumn.AutoFit
With Rows("1:1")
.Font.Bold = True
.Font.Underline = xlUnderlineStyleSingle
End With
With Rows("1:1").Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorLight1
End With
With Rows("1:1").Font
.ThemeColor = xlThemeColorDark1
End With
Rows("1:" & lr).Select
Worksheets("Unprocessed EOEs").Sort.SortFields.Clear
Worksheets("Unprocessed EOEs").Sort.SortFields.Add Key:=Range( _
"J2:J" & lr), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Unprocessed EOEs").Sort
.SetRange Range("A1:K" & lr)
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub

 

[Rickster7]

THANkS BROTHER!