Copy from workbook 2 and paste to wb1 range

[simmerer]

Hi,.
I have this and it fails at the paste for "Subscript out of range". It would seem I am not properly selecting my destination. Is it my method?

Sub Macro2()
'
' Macro2 Macro
Dim wb1 As Excel.Workbook 'Open PSSLA workbook first
Set wb1 = ThisWorkbook
Dim wb2 As String 'Open Raw Data Workbook next
wb2 = Application.GetOpenFilename("Excel workbooks,*.xls*")

If wb2 = "False" Then
' ' the user clicked Cancel

Else
' the user selected a file; its path+name is in wb2
Application.Workbooks.Open Filename:=wb2
End If

Worksheets("IBM Rational ClearQuest Web").Range("A2:K2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Workbooks("wb1").Worksheets("PS").Select (failure point)
Range("A2").Select
Selection.Paste

End Sub

 

[Fluff]

Re: Copy from woorkbook 2 and paste to wb1 range

Try

wb1.Worksheets("PS").Select

 

[Misca]

Re: Copy from woorkbook 2 and paste to wb1 range

Try

wb1.Worksheets("PS").Select

 

[simmerer]

Re: Copy from woorkbook 2 and paste to wb1 range

Hi Fluff! Thanks for responding. I have already tried your suggestion and I get the same error. It is like the code forgot what wb1 was.

 

[Fluff]

Re: Copy from woorkbook 2 and paste to wb1 range

Sorry it should be

wb1.Worksheets("PS").Activate

 

[simmerer]

Re: Copy from woorkbook 2 and paste to wb1 range

Same error. so close, yet so far...

 

[Fluff]

Re: Copy from woorkbook 2 and paste to wb1 range

Do you have a sheet in ThisWorkbook called PS?

 

[simmerer]

Re: Copy from woorkbook 2 and paste to wb1 range

Yes, I have a worksheet named "PS" in ThisWorkbook. I start with my first workbook open. Rather than use the qualified file name (which changes monthly), i just refer to it as ThisWorkbook, then assign the wb1. Thisworkbook value can change, so I didn't want to use it any further.

 

[Fluff]

Re: Copy from woorkbook 2 and paste to wb1 range

Thisworkbook is the workbook containing the macro. Is that the workbook that contains the "PS" sheet?

 

[simmerer]

Re: Copy from woorkbook 2 and paste to wb1 range

Actually the macro is in my Personal workbook which I have hidden.