here is my code;

Dim lastRow As Range
Dim A As Range

Private Sub Workbook_Open()
lastRow = Cells(Rows.Count, "A").End(xlUp).Row + 1
A = lastRow
A.Select
End Sub

can someone tell me what i need to fix to make this work.thanks

You are using range objects so you need to use set and different properties...

Dim lastRow As Range
Dim A As Range

Private Sub Workbook_Open()
set lastRow = Cells(Rows.Count, "A").End(xlUp).offset(1,0)
set A = lastRow 'not sure why you are doing this...
A.Select
End Sub

--
HTH...

Jim Thomlinson


"Mark J" wrote:

here is my code;

Dim lastRow As Range
Dim A As Range

Private Sub Workbook_Open()
lastRow = Cells(Rows.Count, "A").End(xlUp).Row + 1
A = lastRow
A.Select
End Sub

can someone tell me what i need to fix to make this work.thanks

The calculated value for LastRow is not a range; rather it is a number (the
..Row property reference and the +1 make that the case). You probably want
something like this...

Private Sub Workbook_Open()
Cells(Rows.Count, "A").End(xlUp).Offset(1).Select
End Sub

--
Rick (MVP - Excel)


"Mark J" wrote in message
...
here is my code;

Dim lastRow As Range
Dim A As Range

Private Sub Workbook_Open()
lastRow = Cells(Rows.Count, "A").End(xlUp).Row + 1
A = lastRow
A.Select
End Sub

can someone tell me what i need to fix to make this work.thanks

Thank you, and will this always go to the first blank row?

"Jim Thomlinson" wrote:

You are using range objects so you need to use set and different properties...

Dim lastRow As Range
Dim A As Range

Private Sub Workbook_Open()
set lastRow = Cells(Rows.Count, "A").End(xlUp).offset(1,0)
set A = lastRow 'not sure why you are doing this...
A.Select
End Sub

--
HTH...

Jim Thomlinson


"Mark J" wrote:

here is my code;

Dim lastRow As Range
Dim A As Range

Private Sub Workbook_Open()
lastRow = Cells(Rows.Count, "A").End(xlUp).Row + 1
A = lastRow
A.Select
End Sub

can someone tell me what i need to fix to make this work.thanks

No, not the FIRST blank row; rather, it goes to the the first blank row
AFTER the last data cell (the distinction matters if you have blank cells
WITHIN your data).

--
Rick (MVP - Excel)


"Mark J" wrote in message
...
Thank you, and will this always go to the first blank row?

"Jim Thomlinson" wrote:

You are using range objects so you need to use set and different
properties...

Dim lastRow As Range
Dim A As Range

Private Sub Workbook_Open()
set lastRow = Cells(Rows.Count, "A").End(xlUp).offset(1,0)
set A = lastRow 'not sure why you are doing this...
A.Select
End Sub

--
HTH...

Jim Thomlinson


"Mark J" wrote:

here is my code;

Dim lastRow As Range
Dim A As Range

Private Sub Workbook_Open()
lastRow = Cells(Rows.Count, "A").End(xlUp).Row + 1
A = lastRow
A.Select
End Sub

can someone tell me what i need to fix to make this work.thanks

with this redone;
Dim lastRow As Range
Dim A As Range
Dim rng1 As Long
Dim rng As Range

Private Sub Workbook_Open()

Set lastRow = Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
lastRow.Select

WBname = " " & Format(Now() - 1, "mmddyyyy") & ".xls"
Workbooks.Open "C:\LIMS\WaterQuality_Export-daily" & WBname
Set rng = Range("A1:Q500")
rng.Copy

'code to paste data into sheet(2)

End Sub

how would i code it to paste the data into sheet(2)?



"Rick Rothstein" wrote:

The calculated value for LastRow is not a range; rather it is a number (the
..Row property reference and the +1 make that the case). You probably want
something like this...

Private Sub Workbook_Open()
Cells(Rows.Count, "A").End(xlUp).Offset(1).Select
End Sub

--
Rick (MVP - Excel)


"Mark J" wrote in message
...
here is my code;

Dim lastRow As Range
Dim A As Range

Private Sub Workbook_Open()
lastRow = Cells(Rows.Count, "A").End(xlUp).Row + 1
A = lastRow
A.Select
End Sub

can someone tell me what i need to fix to make this work.thanks

What this code does is it looks at teh currently active sheet and it goes to
the first blank row in column A of that sheet. If you wnated a specific sheet
then...

Dim lastRow As Range
Dim A As Range

Private Sub Workbook_Open()
set lastRow = sheets("sheet1").Cells(Rows.Count, "A").End(xlUp).offset(1,0)
set A = lastRow 'not sure why you are doing this...
A.parent.select 'Select the sheet
A.Select
End Sub

--
HTH...

Jim Thomlinson


"Mark J" wrote:

Thank you, and will this always go to the first blank row?

"Jim Thomlinson" wrote:

You are using range objects so you need to use set and different properties...

Dim lastRow As Range
Dim A As Range

Private Sub Workbook_Open()
set lastRow = Cells(Rows.Count, "A").End(xlUp).offset(1,0)
set A = lastRow 'not sure why you are doing this...
A.Select
End Sub

--
HTH...

Jim Thomlinson


"Mark J" wrote:

here is my code;

Dim lastRow As Range
Dim A As Range

Private Sub Workbook_Open()
lastRow = Cells(Rows.Count, "A").End(xlUp).Row + 1
A = lastRow
A.Select
End Sub

can someone tell me what i need to fix to make this work.thanks

Mark,

Yor going OTT with set now, you don't need them

Set rng = Range("A1:Q500")
rng.Copy

Becomes

range("A1:Q500").copy
sheets("Sheet2").range("A1").pastespecial
Application.cutcopymode=false

Mike

"Mark J" wrote:

with this redone;
Dim lastRow As Range
Dim A As Range
Dim rng1 As Long
Dim rng As Range

Private Sub Workbook_Open()

Set lastRow = Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
lastRow.Select

WBname = " " & Format(Now() - 1, "mmddyyyy") & ".xls"
Workbooks.Open "C:\LIMS\WaterQuality_Export-daily" & WBname
Set rng = Range("A1:Q500")
rng.Copy

'code to paste data into sheet(2)

End Sub

how would i code it to paste the data into sheet(2)?



"Rick Rothstein" wrote:

The calculated value for LastRow is not a range; rather it is a number (the
..Row property reference and the +1 make that the case). You probably want
something like this...

Private Sub Workbook_Open()
Cells(Rows.Count, "A").End(xlUp).Offset(1).Select
End Sub

--
Rick (MVP - Excel)


"Mark J" wrote in message
...
here is my code;

Dim lastRow As Range
Dim A As Range

Private Sub Workbook_Open()
lastRow = Cells(Rows.Count, "A").End(xlUp).Row + 1
A = lastRow
A.Select
End Sub

can someone tell me what i need to fix to make this work.thanks