.select

[vbaNewby]

Hello guru's,

I have the following:

Set Sh = ThisWorkbook.Sheets("Sheet10")
    With Sh
        Set rng = .Cells(1, .Columns.count).End(xlToLeft)
        Set rng = Range(rng, rng.End(xlToLeft))
    End With
 rng.Copy
 '

 'Sheets("Sheet2").Select
 ThisWorkbook.Sheets("Sheet2").Select

It is failing with out of range error and "select method of worksheet class failed" on either

 'Sheets("Sheet2").Select
 ThisWorkbook.Sheets("Sheet2").Select

Sheet10 is a hidden tab. Sheet2 is not hidden.

What is wrong?

Thanks in advance.

 

[GlennUK]

Is ThisWorkbook the active workbook? If not, you need to activate it before selecting a sheet on it.

 

[VoG]

You shouldn't need to select. Try like this

Set Sh = ThisWorkbook.Sheets("Sheet10")
With Sh
    Set Rng = .Cells(1, .Columns.Count).End(xlToLeft)
    Set Rng = Range(Rng, Rng.End(xlToLeft))
End With
Rng.Copy Destination:=Sheets("Sheet2").Range("A1")

 

[vbaNewby]

You shouldn't need to select. Try like this

Set Sh = ThisWorkbook.Sheets("Sheet10")
With Sh
    Set Rng = .Cells(1, .Columns.Count).End(xlToLeft)
    Set Rng = Range(Rng, Rng.End(xlToLeft))
End With
Rng.Copy Destination:=Sheets("Sheet2").Range("A1")

Does this paste? What I have is:

 Set Sh = ThisWorkbook.Sheets("Sheet10")
    With Sh
        Set rng = .Cells(1, .Columns.count).End(xlToLeft)
        Set rng = Range(rng, rng.End(xlToLeft))
    End With
 rng.Copy


 Sheets("Sheet2").Select
 Range("F12").Select
 ActiveSheet.Paste

Does your code replace this? It works fine without hidden tabs!

 

[VoG]

This will paste into F12 of Sheet2

Set Sh = ThisWorkbook.Sheets("Sheet10")
With Sh
    Set Rng = .Cells(1, .Columns.Count).End(xlToLeft)
    Set Rng = Range(Rng, Rng.End(xlToLeft))
End With
Rng.Copy Destination:=Sheets("Sheet2").Range("F12")

 

[vbaNewby]

This will paste into F12 of Sheet2

Set Sh = ThisWorkbook.Sheets("Sheet10")
With Sh
    Set Rng = .Cells(1, .Columns.Count).End(xlToLeft)
    Set Rng = Range(Rng, Rng.End(xlToLeft))
End With
Rng.Copy Destination:=Sheets("Sheet2").Range("F12")

Thanks VOG. Seems to work. One more question: If I call Workbooks.Open("someFileName")

And then further down my code, I do:

Sheets("OriginalWorkbookSheet").Select

or

 ThisWorkbook.Sheets("OriginalWorkbookSheet").Select

I am getting an out or range error. I believe this is because I open someFileName. What i want to do is go back to my original workbook and select a sheet.

How can I adjust this?

 

[VoG]

If you must select (it is very rarely necessary) then you would need code like

ThisWorkbook.Activate
Sheets("OriginalWorkbookSheet").Select
Range("B1").Select

 

[vbaNewby]

If you must select (it is very rarely necessary) then you would need code like

ThisWorkbook.Activate
Sheets("OriginalWorkbookSheet").Select
Range("B1").Select

Thanks for your help Vog. Kind regards.