Forum Discussion
using VBA to drop down to next row
Hi,
Check this line of your code...
"Worksheets("WIP").Range("A2" & ActiveCell.Row + 1).Offset(1, 0).Select"
The issue is that you're always selecting cell "A2" as your starting point and stepping across to the right by one column and then down one row.
Doing that means that once you've entered data to that area, the next time you run the macro, it goes back to cell "A2" and overwrites the data.
There are several ways to overcome this, one of which could be to insert a new row at this point before adding the data …
"Selection.EntireRow.Insert"
another might be selecting "A2" and then going to the bottom of the page and jumping back up to the empty cell (in column "A") which should be a blank row at the end of your table.
"Selection.End(xlDown).Select"
Google the above and see how you can use them. There are other options but these two will help you get started.
Kind regards
N
Thank you so much for the response. I have done some googling and attempted to integrate your changes to no avail. I am continuing to get the overwrite issue and now it is taking a huge amount of time to both load my document and run the VBA. I am at a loss :(
Hi,
Try creating a new (temporary) macro with the auto recorder. You need this to record the right steps
Once switched on...
- Switch tab over to the tab containing the table where you want to past the data
- Select the cell containing the first piece of data (or heading) e.g."A1"
- You need to record the jump to the last (empty) cell in that column - can be done several way e.g. Ctrl + Down arrow and then Down Arrow followed by enter.
This should record the steps to get you to the blank row in your data ready for paste. You can then copy this part into the main macro and replace the faulty part (step) just before the paste. That way, when you run the macro, it should no longer overwrite your existing data.
It's a little more difficult to explain exactly where this needs to go or help as every file and macro is different.
Hope it points you in the right direction.