using VBA to drop down to next row

Copper Contributor

Hi All!

I am not a coder and have spent endless hours working on this so alas I consult the experts!

 

I have a form-esque sheet that I have created (think invoice style set up), I want certain values from this form to populate onto a WIP sheet in my workbook. I have gotten the code created, and it will bring over the data, but however I manipulate it, it seems to either make it just overwrite what is written or drop into the wrong cells. Data starts in the B column (A is a date that is assigned with a timestamp formula) and will go from row 2 on down (I have headers). I am using a "button" to do the transfer, if that matters. I also want to be certain that it is grabbing the next available row because there maybe times that I am free typing entries into the WIP sheet, not all info will necessarily come from the transfer process. 

This is what I have so far:

Sub Button2_Click()
Dim Name As String, MRN As String, Location As String, ComparisonStudy As String, FacilityName As String, FacilityPhone As String, FacilityFax As String
Worksheets("Release Form").Select
Name = Range("C6")
MRN = Range("G8")
Location = Range("G9")
ComparisonStudy = Range("E24")
FacilityName = Range("D12")
FacilityPhone = Range("D14")
FacilityFax = Range("D15")
Worksheets("WIP").Select
Worksheets("WIP").Range("A2").Select
If Worksheets("WIP").Range("A2").Offset(1, 0) <> "" Then
Worksheets("WIP").Range("A2" & ActiveCell.Row + 1).Offset(1, 0).Select
End If
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Name
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = MRN
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Location
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = ComparisonStudy
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = FacilityName
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = FacilityFax
ActiveCell.Offset(0, 1).Select
Worksheets("Release Form").Select

End Sub

 

Thank you in advance, all help is greatly appreciated!

 

 

3 Replies

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...

  1. Switch tab over to the tab containing the table where you want to past the data
  2. Select the cell containing the first piece of data (or heading) e.g."A1"
  3. 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.