Edit Macro to select cell using .end(xlup)

[adam_neb]

I recorded a macro to reference a cell in another workbook. When I recorded the macro, I used the key strokes to GoTo B65536, then control up-arrow to get to the last cell in column B that had data. I did this because each week a new value is added in column B for each new week. When I ran my macro it pulled fine, EXCEPT when I added new information to Column B. The macro returned an absolute reference.

I looked in my reference book "VBA and Macros for Microsoft Excel" by Mr. Excel, and I see where he shows using Range("B65536").End(xlup), but I am having a bugger of a time getting my macro to return the correct value when Column B has been updated. Here is my macro:

Sub wbfsc()
'
' wbfsc Macro
' Macro recorded 9/27/2006 by adam_neb
'

'
Application.Goto Reference:="R2C3"
Selection.FormulaR1C1 = "='H:\Dept\Marketing\FUEL\Weekly Updates\Weekly E-Mail Attachments\2006 Fuel Prices.xls'!R194C2/100"
End Sub

What changes to I need to make so that it looks for the last populated cell in Column B and return that value? When I run the macro as-is, the results in Cell C2 is
'='H:\Dept\Marketing\FUEL\Weekly Updates\Weekly E-Mail Attachments\[2006 Fuel Prices.xls]2006 Fuel Prices'!$B$194/100

Note the absolute reference of $B$194.

Can anyone offer any direction?
Thanks!
Adam

 

[Joe Was]

ActiveSheet.Range("B65536").End(xlUp).OffSet(1, 0).Select

 

[adam_neb]

Joe:

Thanks for the direction! One question though: the data I need is not in the "active sheet" that is open and which contains the macro.

I replaced

Selection.FormulaR1C1 = "='H:\Dept\Marketing\FUEL\Weekly Updates\Weekly E-Mail Attachments\2006 Fuel Prices.xls'!R194C2/100"

with your suggestion

ActiveSheet.Range("B65536").End(xlUp).OffSet(1, 0).Select

but I do not get any data when I run the macro.

So, do I need to change

ActiveSheet.Range

to the name I the workbook that contains the data I need?

My macro starts with moving to the cell that need to hold the data

Application.Goto Reference:="R2C3"

now how do I edit my macro to bring back the data I need?

I tried changing ActiveSheet.Range.Range("B65536").End(xlUp).OffSet(1, 0).Select

to 'H:\Dept\Marketing\FUEL\Weekly Updates\Weekly E-Mail Attachments\2006 Fuel Prices.xls'.Range("B65536").End(xlUp).OffSet(1, 0).Select

but that did not bring over the data.

A little more help would be great!
Thanks again!
Adam

 

[Joe Was]

It is not as stright forward as one would think.


Sub myImport()
'Standard module code, like: Module1!
Dim myRng As Range

Workbooks.Open Filename:="H:\Dept\Marketing\FUEL\Weekly Updates\Weekly E-Mail Attachments\2006 Fuel Prices.xls"

Set myRng = Workbooks(2).Sheets("Sheet1").Range("$B$194")

Workbooks(1).Sheets("Sheet1").Range("B65536").End(xlUp).Offset(1, 0).Value = myRng.Value

Workbooks(2).Close
End Sub