Selection Macro not working...

[davie1982]

I have this code to select a table without selecting the headers... it comes up with an error...

Sub zselect()

    Dim lastrow As Integer
    lastrow = Range("A65536").End(xlUp).Row
    Dim lastcol As Integer
    lastcol = Range("AA2").End(xlToLeft).Column
    
    Range("A1:" & lastcol & lastrow).Select

End Sub

I just figured out that lascol is a number... is there any way i could bring it back as the column letter?

 

[VoG]

Where you are dealing with column numbers it is easier to use Cells to define the range

Sub zselect()
    Dim lastrow As Long, lastcol As Integer
    lastrow = Range("A" & Rows.Count).End(xlUp).Row
    lastcol = Range("AA2").End(xlToLeft).Column
    Range(Cells(1, 1), Cells(lastrow, lastcol)).Select
End Sub

Why do you need to Select this range - it is rarely necessary to select anything in order to work with it.

 

[sgdev]

try this instead - let me know if it help...

Sheet1.Range("b2", Sheet1.Range("b2").SpecialCells(xlCellTypeLastCell)).Select

i've assumed that:
sheet1 is the name of your worksheet
b2 is the first cell of your data

ta

 

[davie1982]

It's part of a macro that will copy the table data to another workbook from cells A2 - end of table to cells F15 on another worksheet. I figured this is the best way, and i don't want to copy the header row (A1-c1) because the titles are different in the new wb AND the destination cell changes with different spreadsheets i open.

 

[VoG]

Try this

Sub zselect()
Dim lastrow As Long, lastcol As Integer
With Sheets("Sheet1")
    lastrow = .Range("A" & Rows.Count).End(xlUp).Row
    lastcol = .Range("AA2").End(xlToLeft).Column
    .Range(.Cells(2, 1), .Cells(lastrow, lastcol)).Copy Destination:=Sheets("Sheet2").Range("F15")
End With
Application.CutCopyMode = False
End Sub

 

[davie1982]

Hmm, it's kind of what i need.. except that i want it to search for a specific word and paste the values under that cell.

RemAccount is the value of the cell

It's unique and found nowhere else in sheet 2

The cell is not always going to be "F15", it may change, so, searching for RemAccount is the best option because it is the first heading in the new table.

 

[VoG]

Try

Sub zselect()
Dim lastrow As Long, lastcol As Integer, Found As Range
Set Found = Sheets("Sheet2").Cells.Find(what:="RemAccount")
If Found Is Nothing Then
    MsgBox "RemAccount not found."
    Exit Sub
End If
With Sheets("Sheet1")
    lastrow = .Range("A" & Rows.Count).End(xlUp).Row
    lastcol = .Range("AA2").End(xlToLeft).Column
    .Range(.Cells(2, 1), .Cells(lastrow, lastcol)).Copy Destination:=Found.Offset(1, 0)
End With
Application.CutCopyMode = False
End Sub

 

[davie1982]

Ah that's perfect, thank you.