I receive a sheet of data daily, containing a list of numbers. The number are of several digits long and I convert them all in to six digit numbers in column “B” with the following formula. =text(a1,“000000”) I then click on the bottom right corner of cell A1 and the formula populates down. I then Special pates (values) the result in to cell A1’
I have now recorded a macro to do this process, but when this was set up there were only 500 rows of data, so when I receive a list containing 800 rows of data the macro only works up to row 500.
Can the macro be altered to accommodate as many rows as there are rows of data?
The macro reads:
Range("E3").Select
ActiveCell.FormulaR1C1 = "=TEXT(RC[-3],""000000"")"
Range("E3").Select
Selection.AutoFill Destination:=Range("E3:E500"), Type:=xlFillDefault
Range("E3:E500").Select
Selection.Copy
ActiveWindow.ScrollRow = 1
Range("B3").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Columns("E:E").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("E3").Select
End Sub
I have now recorded a macro to do this process, but when this was set up there were only 500 rows of data, so when I receive a list containing 800 rows of data the macro only works up to row 500.
Can the macro be altered to accommodate as many rows as there are rows of data?
The macro reads:
Range("E3").Select
ActiveCell.FormulaR1C1 = "=TEXT(RC[-3],""000000"")"
Range("E3").Select
Selection.AutoFill Destination:=Range("E3:E500"), Type:=xlFillDefault
Range("E3:E500").Select
Selection.Copy
ActiveWindow.ScrollRow = 1
Range("B3").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Columns("E:E").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("E3").Select
End Sub