I have a spreadsheet of parts data called Register. On a separate page, I've
created a form called OnHold form. Some of the cells in the OnHold form get
populated with information from data in the Register spreadsheet. The data
will always come from the same row and will always be from the last row.

I think I've figured out how to get to the last row of the Register sheet
(this in a commandbutton on the OnHold Sheet).
Dim rgLastCell As Range
Set rgLastCell = Sheet.Register.Range("A65536").End(xlUp)
Range("B9").Value = rgLastCell.Value

But maybe not. I want the last value in the last cell in Row A to go into
cell B9 in the OnHold form. Then I want the last value in the last cell of
Row B to go into cell G11, etc. I thought to just repeat the code above but
change the ranges, and I'm sure you know why it won't work, but I don't.

Can anyone help? I'm really new at macros in Excel.
Thanks,
DM

On Sep 24, 5:02 pm, Diana Morrison
wrote:
I have a spreadsheet of parts data called Register. On a separate page, I've
created a form called OnHold form. Some of the cells in the OnHold form get
populated with information from data in the Register spreadsheet. The data
will always come from the same row and will always be from the last row.

I think I've figured out how to get to the last row of the Register sheet
(this in a commandbutton on the OnHold Sheet).
Dim rgLastCell As Range
Set rgLastCell = Sheet.Register.Range("A65536").End(xlUp)
Range("B9").Value = rgLastCell.Value

But maybe not. I want the last value in the last cell in Row A to go into
cell B9 in the OnHold form. Then I want the last value in the last cell of
Row B to go into cell G11, etc. I thought to just repeat the code above but
change the ranges, and I'm sure you know why it won't work, but I don't.

Can anyone help? I'm really new at macros in Excel.
Thanks,
DM

untested:

Dim sourceWS As Worksheet
Dim rgLastCellRow As Long
Set sourceWS = Sheets("Register")
With sourceWS
rgLastCellRow = .Range("A65536").End(xlUp).Row
Range("B9").Value = .Cells(rgLastCellRow, 1).Text
Range("G11").Value = .Cells(rgLastCellRow, 2).Text
End With
Set sourceWS = Nothing

Fabulous. Thanks so much!

Diana

"JW" wrote:

On Sep 24, 5:02 pm, Diana Morrison
wrote:
I have a spreadsheet of parts data called Register. On a separate page, I've
created a form called OnHold form. Some of the cells in the OnHold form get
populated with information from data in the Register spreadsheet. The data
will always come from the same row and will always be from the last row.

I think I've figured out how to get to the last row of the Register sheet
(this in a commandbutton on the OnHold Sheet).
Dim rgLastCell As Range
Set rgLastCell = Sheet.Register.Range("A65536").End(xlUp)
Range("B9").Value = rgLastCell.Value

But maybe not. I want the last value in the last cell in Row A to go into
cell B9 in the OnHold form. Then I want the last value in the last cell of
Row B to go into cell G11, etc. I thought to just repeat the code above but
change the ranges, and I'm sure you know why it won't work, but I don't.

Can anyone help? I'm really new at macros in Excel.
Thanks,
DM

untested:

Dim sourceWS As Worksheet
Dim rgLastCellRow As Long
Set sourceWS = Sheets("Register")
With sourceWS
rgLastCellRow = .Range("A65536").End(xlUp).Row
Range("B9").Value = .Cells(rgLastCellRow, 1).Text
Range("G11").Value = .Cells(rgLastCellRow, 2).Text
End With
Set sourceWS = Nothing

Same spreadsheet, whole new problem...

Now they want to be able to place the cursor on any line in the register,
and populate the form with the info on that particular line.

Can you help?
Thanks
Diana

"Diana Morrison" wrote:

I have a spreadsheet of parts data called Register. On a separate page, I've
created a form called OnHold form. Some of the cells in the OnHold form get
populated with information from data in the Register spreadsheet. The data
will always come from the same row and will always be from the last row.

I think I've figured out how to get to the last row of the Register sheet
(this in a commandbutton on the OnHold Sheet).
Dim rgLastCell As Range
Set rgLastCell = Sheet.Register.Range("A65536").End(xlUp)
Range("B9").Value = rgLastCell.Value

But maybe not. I want the last value in the last cell in Row A to go into
cell B9 in the OnHold form. Then I want the last value in the last cell of
Row B to go into cell G11, etc. I thought to just repeat the code above but
change the ranges, and I'm sure you know why it won't work, but I don't.

Can anyone help? I'm really new at macros in Excel.
Thanks,
DM