I have a master table that gets updated with rows from a data entry table in another workbook. I used some looping to make it work and it works fine.
However, the first column of my Master sheet has an item number (a simple integer) and the ones that come over from the data entry sheet, when added to the bottom of the master also are in sequence, so the numbers on the master sheet for have column A looking like this:
129
130
131
1
2
3
What I want to do is to make it look like this:
129
130
131
132
133
134
In Excel, this is simple-- use autofill-- select a range including both the cell with the 131 and the cell with the 1 and drag down with the copy handle (or just grab the cell with the 131 and hold the CTRL key while you drag -- important if you get rows deleted so maybe directly above the 131 is NOT 130-- maybe 130 was deleted).
So I want to use the autofill that holds the CTRL key down. I recorded a macro to look at it the code.
After my rows are transferred, I have variables that I checked in the Immediate window-- I have the LastRow-- that was the Excel row number of the last row of my table BEFORE the transfer. I also have NewLastRow, (which I calculate with a rowcounter variable from the looping). The NewLastRow is the Excel row number of the last row AFTER the transfer.
I wrote the following code but and when it step through it, it always bombs on the line where I try to select the range where I want the active cell to be when the autofill is executed. I get a Run Time Error '1004': Method 'Range' of object'_Global' failed. Here's my code:
[pre]
[/pre]
Can you tell me what is wrong with the way I'm trying to range select the cell in the A column?
However, the first column of my Master sheet has an item number (a simple integer) and the ones that come over from the data entry sheet, when added to the bottom of the master also are in sequence, so the numbers on the master sheet for have column A looking like this:
129
130
131
1
2
3
What I want to do is to make it look like this:
129
130
131
132
133
134
In Excel, this is simple-- use autofill-- select a range including both the cell with the 131 and the cell with the 1 and drag down with the copy handle (or just grab the cell with the 131 and hold the CTRL key while you drag -- important if you get rows deleted so maybe directly above the 131 is NOT 130-- maybe 130 was deleted).
So I want to use the autofill that holds the CTRL key down. I recorded a macro to look at it the code.
After my rows are transferred, I have variables that I checked in the Immediate window-- I have the LastRow-- that was the Excel row number of the last row of my table BEFORE the transfer. I also have NewLastRow, (which I calculate with a rowcounter variable from the looping). The NewLastRow is the Excel row number of the last row AFTER the transfer.
I wrote the following code but and when it step through it, it always bombs on the line where I try to select the range where I want the active cell to be when the autofill is executed. I get a Run Time Error '1004': Method 'Range' of object'_Global' failed. Here's my code:
[pre]
Code:
'Renumber the new row item nummbers beginning with the next integer after the last row before the transfer
NewFinalRow = FinalRow + RowCounter
Range(FinalRow, 1).Select
Selection.AutoFill Destination:=Range(Cells(FinalRow, 1), (Cells(NewFinalRow, 1))), Type:=xlFillSeries
Range((Cells(FinalRow, 1)), (Cells(NewFinalRow, 1))).Select
Range((Cells(NewFinalRow + 1, 1))).SelectCan you tell me what is wrong with the way I'm trying to range select the cell in the A column?
