Recorded Macro Ammendments

[PHIL.Pearce84]

Hi All,

I have a recorded macro, see below, is there a way to amend this so instead of stopping at row 188 it stops when the data ends? Fairly new to this style.

Any help would be appreciated.

Thanks


Sub Update()
'
' Update Macro
'
' Keyboard Shortcut: Ctrl+Shift+S
'
Range("C6").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Range("C5").Select
Sheets("Credit-Debit Card Template").Select
ActiveWindow.SmallScroll Down:=6
Range("A25").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A25").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.NumberFormat = "m/d/yyyy"
Range("B25").Select
Sheets("Converted File (Income Team)").Select
Range("U6").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("Credit-Debit Card Template").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("C25").Select
Sheets("Converted File (Income Team)").Select
Range("U4").Select
Application.CutCopyMode = False
Range("BZ6").Select
Selection.AutoFill Destination:=Range("BZ6:BZ169")
Range("BZ6:BZ169").Select
Range("BZ6").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("Credit-Debit Card Template").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("C25").Select
Sheets("Converted File (Income Team)").Select
Range("BZ7").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.ClearContents
Range("A5").Select
Sheets("Converted File (Income Team)").Select
Range("P6").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("Credit-Debit Card Template").Select
Range("F25").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("E25").Select
Application.CutCopyMode = False
ActiveCell.Formula2R1C1 = "=credit/Debit CardPayments"
Range("E25").Select
ActiveCell.FormulaR1C1 = "Credit/Debit Card Payments"
Range("E25").Select
Selection.AutoFill Destination:=Range("E25:E188")
Range("E25:E188").Select
Range("D25").Select
Sheets("Converted File (Income Team)").Select
Range("A1").Select
Sheets("Credit-Debit Card Template").Select
ActiveWindow.SmallScroll Down:=-12
Range("A2").Select
End Sub

 

[Joe4]

If you wanted to replace something like this:

Range("BZ6:BZ169").Select

with something dynamic that goes down to the last row with data in column BZ, you can do it like this:

Dim lr as Long
lr = Cells(Rows.Count, "BZ").End(xlUp).Row
Range("BZ6:BZ" & lr).Select

Also, you should never used reserved words (words of existing functions, methods, properties, or objects in VBA) like "Update" as the name of your procedures, functions, or variables. Doing so can cause errors and unexpected results. I would recommend changing the name to something like "MyUpdate".

 

[PHIL.Pearce84]

Amazing! Thank you!

 

[Joe4]

You are welcome!