Finding last row, copying and pasting to previous worksheet

[crafter85]

Hi all!

I'm new here and for the most part have found what I've needed but I'm stuck with this one.

Basically, I've got a form which populates a Master worksheet by inputting the data into the next available row. I also want this data to appear on individual records (so the data on each submitted form will be added to a Master worksheet as a long list of records, but at the same time will also produce its own personal record as a new worksheet containing the data from the form). I've created the template worksheet Master_record for my individual records and written some VB to copy this worksheet and give it a name of my choosing (in this case, an employee's name) and would like to update this at the same time as the master when filling in the form. It seemed easier to update the Master then simply copy the data across to Row 42 on the new worksheet but as the row of data and name of the spreadsheet will change each time, I would need code that looked for:

The last row with data in it from the Master worksheet and then...
paste it to the previous active worksheet (rather than a named worksheet as it would change each time)

Or, I could simply write the VB to copy the data to the new worksheet but again, have issue with the worksheet name as it will change each time...

Hope this makes sense. Any help gratefully received!

 

[JLGWhiz]

Maybe this will help.
1.
The following snippet effectively goes to the bottom of the sheet, then comes upward until it finds a cell with data.

Dim rn As Long
rn = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
MsgBox rn

The message box would display the row number for the last cell in column A which contains data.
2.
The following snippet finds the bottom most cell which contains anything other than null.

Dim rn As Long
rn = ActiveSheet.Cells.Find("*", , xlFormulas, xlpart, xlByRows, xlPrevious).Row
MsgBox rn

Again, it works from the bottom upward.
The first one is useful if you know which column you want to look at for the target cell. The second method is when a specific column is not needed but you want to know the limit of your data range, which may or may not be visible. Once you have the value or the variable 'rn', you can use that as a row number anywhere in your code. The next available row would be Rows(rn + 1} or Range("A" & rn + 1) or you can use Offset to move the cell references in the direction and distance you need.

 

[crafter85]

Thanks JLGWhiz however doesn't solve all my problems so going to attack it from a different angle!

 

[JLGWhiz]

Thanks JLGWhiz however doesn't solve all my problems so going to attack it from a different angle!

You're welcome,
Regards, JLG