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

In that case you will need to ensure that your workbook is the active workbook & use this

    Set wb1 = ActiveWorkbook

 

[Fluff]

Re: Copy from woorkbook 2 and paste to wb1 range

Alternatively you can open the 2 workbooks & assign them a variable name like this

Sub Macro1()

    Dim Fname As String
    Dim wb(1 To 2) As Workbook
    Dim Cnt As Long
    
    For Cnt = 1 To 2
        Fname = Application.GetOpenFilename("Excel workbooks,*.xls*")
        Set wb(Cnt) = Workbooks.Open(Fname)
    Next Cnt
    MsgBox wb(1).Name
    MsgBox wb(2).Name
End Sub

 

[simmerer]

Re: Copy from woorkbook 2 and paste to wb1 range

So now with this I need to select the PS sheet and now that is out of range. The code is staring right at it.

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
Set wb1 = ActiveWorkbook (failure point)

Sheets("PS").Select

Range("A2").Select
Selection.Paste

End Sub

 

[Fluff]

Re: Copy from woorkbook 2 and paste to wb1 range

It should be like this

Sub Macro2()
'
' Macro2 Macro
Dim wb1 As Excel.Workbook 'Open PSSLA workbook first
Set wb1 = [COLOR=#ff0000]ActiveWorkbook[/COLOR]
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
[COLOR=#ff0000]wb1.Worksheets("PS").Activate[/COLOR]
Range("A2").Select
Selection.Paste

Sheets("PS").Select

Range("A2").Select
Selection.Paste

End Sub

 

[simmerer]

Re: Copy from woorkbook 2 and paste to wb1 range

wb1.Worksheets("PS").Activate is getting the out of range error again.

 

[Fluff]

Re: Copy from woorkbook 2 and paste to wb1 range

Open the PSSLA workbook & make sure the PS sheet is active & run this

Sub FluffTest()

Dim wb1 As Excel.Workbook 'Open PSSLA workbook first
Set wb1 = ActiveWorkbook

MsgBox "|" & ActiveSheet.Name & "|" & vbLf & wb1.Name & vbLf & ActiveWorkbook.Name
End Sub

What does the top line of the message box say & are the 2 file names underneath the same?

 

[simmerer]

Re: Copy from woorkbook 2 and paste to wb1 range

The top line is the gives the wb2 worksheet, second and third lines are the same: wb2 name.

 

[simmerer]

Re: Copy from woorkbook 2 and paste to wb1 range

I did that wrong. With the PSSLA Workbook open, and the PS Worksheet active, I get
|PS|
PSSLA.xlsm
PSSLA.xlsm

 

[simmerer]

Re: Copy from woorkbook 2 and paste to wb1 range

I put your value check into my code here and got the same results again.

Sub Macro2()
'
' Macro2 Macro
Dim wb1 As Excel.Workbook 'Open PSSLA workbook first
Set wb1 = ActiveWorkbook
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 strFile
Application.Workbooks.Open Filename:=wb2
End If

Worksheets("IBM Rational ClearQuest Web").Range("A2:K2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
wb1.Worksheets("PS").Activate
MsgBox "|" & ActiveSheet.Name & "|" & vbLf & wb1.Name & vbLf & ActiveWorkbook.Name

End Sub

 

[Fluff]

Re: Copy from woorkbook 2 and paste to wb1 range

That would suggest that you are no longer the out of range error, is that correct?