Select problem
 

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

Select problem
 

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

Select problem
 

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

Select problem
 

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

Select problem
 

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

Select problem
 

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

Select problem
 

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

Select problem
 

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