Copy from workbook 2 and paste to wb1 range

S

simmerer

New Member
Joined
Sep 6, 2017
Messages
33
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
 
Sort by date Sort by votes
Re: Copy from woorkbook 2 and paste to wb1 range

Apparently I am no longer out of range! However if I add back the last two lines
Range("A2").Select
Selection.Paste

the paste is now giving me the message "Object doesn't support this property or method".

I also tried
Application.Workbooks.Open Filename:=wb2
End If
wb1.Worksheets("PS").Activate
MsgBox "|" & ActiveSheet.Name & "|" & vbLf & wb1.Name & vbLf & ActiveWorkbook.Name

Workbooks("wb2").Worksheets("IBM Rational ClearQuest Web").Range("A2:K2").Range(Selection, Selection.End(xlDown)).Copy_
Workbooks("wb1").Worksheets("PS").Range ("A2")

thinking that it would replace the need to pre-insert the rows, but it gives the "out of range" error after your check code shows
|PS|
PSSLA.xlsm
PSSLA.xlsm
 
Upvote 0

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
Re: Copy from woorkbook 2 and paste to wb1 range

Try this
Code: 
'
' 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 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
wb1.Worksheets("PS").Activate
Range("A2").Select
[COLOR=#ff0000]Selection.PasteSpecial xlValues[/COLOR]


End Sub
 
Upvote 0
Re: Copy from woorkbook 2 and paste to wb1 range

That worked! I am very happy and thankful! However, slightly confused why paste and paste special are not handled the same. Thanks for your help!
 
Upvote 0
Re: Copy from woorkbook 2 and paste to wb1 range

Glad to help & thanks for the feedback.
Have a good weekend
 
Upvote 0
Re: Copy from woorkbook 2 and paste to wb1 range

That worked so well, that I replicated it for other columns and it failed going forward :confused:

Here is the code again. What I did was open a Workbook/Worksheet (WS), copy data, go to the first WS and paste. Where it is failing now is going back to the second WS to copy more data (non continuous columns) The line of code "wb2.Worksheets("IBM Rational ClearQuest Web").Activate" fails for "Invalid Qualifier". Removing the "wb2" produces a "Subscript out of range" error.

Sub PSSLA_CopyPaste_Raw_Data()
'
'
Dim wb1 As Excel.Workbook 'Open PSSLA workbook first
Set wb1 = ActiveWorkbook
Dim wb2 As String
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

'######### Copy/Paste Raw Data columns A:K
Worksheets("IBM Rational ClearQuest Web").Range("A2:K2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
wb1.Worksheets("PS").Activate
Range("A2").Select
Selection.PasteSpecial xlValues

'######### Copy/Paste Raw Data column L "Date Closed or CN Submitted"
wb2.Worksheets("IBM Rational ClearQuest Web").Activate
Worksheets("IBM Rational ClearQuest Web").Range("L2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
wb1.Worksheets("PS").Activate
Range("M2").Select
Selection.PasteSpecial xlValues
 
Last edited:
Upvote 0
Re: Copy from woorkbook 2 and paste to wb1 range

Try
Code: 
Sub PSSLA_CopyPaste_Raw_Data()
'
'
    Dim wb1 As Excel.Workbook 'Open PSSLA workbook first
    Dim wb2 As Workbook
    Dim Fname As String
    
    Set wb1 = ActiveWorkbook

    Fname = Application.GetOpenFilename("Excel workbooks,*.xls*")

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

    Else
    ' the user selected a file; its path+name is in wb2
    Set wb2 = Workbooks.Open(Fname)
    End If
    
    '######### Copy/Paste Raw Data columns A:K
    With Worksheets("IBM Rational ClearQuest Web")
        .Range("A2:K" & .Range("K" & Rows.Count).End(xlUp).Row).Copy
    End With
    wb1.Worksheets("PS").Range("A2").PasteSpecial xlValues
    
    '######### Copy/Paste Raw Data column L "Date Closed or CN Submitted"
    With wb2.Worksheets("IBM Rational ClearQuest Web")
        .Range("L2", .Range("L" & Rows.Count).End(xlUp)).Copy
    End With
    wb1.Worksheets("PS").Range("M2").PasteSpecial xlValues
End Sub
 
Upvote 0
Re: Copy from woorkbook 2 and paste to wb1 range

Thanks for the code! This gave an "invalid qualifier" error.
'######### Copy/Paste Raw Data column L "Date Closed or CN Submitted"
With wb2.Worksheets("IBM Rational ClearQuest Web")

Microsoft says this about invalid qualifier
qualifier does not identify a project, module, object, or a variable of user-defined type within the current scope.

That seems obvious. Why doesn't it identify the object anymore?, not so much.
 
Last edited:
Upvote 0
Re: Copy from woorkbook 2 and paste to wb1 range

Did you copy & paste the entire code I supplied in post#26?
It sounds as though you may still have wb2 as a string
 
Upvote 0
Re: Copy from woorkbook 2 and paste to wb1 range

I did not see the fname assignment. Added it and it did copy and paste correctly. However, I have to admit that I have been holding out on you. I was thinking that once I got it to work for the first two sets of copy paste, I would be able to reuse it. Well that didn't work like I had hoped.

The copy is from a continuous set of columns, but the paste is not. Some are multiple columns and some are single columns. We started with a group A:K, then L, M, then N:P, which is where i got this error " Application-defined or object-defined error". I copied the part for range a:k for this.

Here is the whole sub:

Sub PSSLA_CopyPaste_Raw_Data()
'
'
Dim wb1 As Excel.Workbook 'Open PSSLA workbook first
Dim wb2 As Workbook
Dim Fname As String

Set wb1 = ActiveWorkbook

Fname = Application.GetOpenFilename("Excel workbooks,*.xls*")

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

Else
' the user selected a file; its path+name is in wb2
Set wb2 = Workbooks.Open(Fname)
End If

'######### Copy/Paste Raw Data columns A:K
With Worksheets("IBM Rational ClearQuest Web")
.Range("A2:K" & .Range("K" & Rows.Count).End(xlUp).Row).Copy
End With
wb1.Worksheets("PS").Range("A2").PasteSpecial xlValues

'######### Copy/Paste Raw Data column L "Date Closed or CN Submitted"
With wb2.Worksheets("IBM Rational ClearQuest Web")
.Range("L2", .Range("L" & Rows.Count).End(xlUp)).Copy
End With
wb1.Worksheets("PS").Range("M2").PasteSpecial xlValues

' ######### Copy/Paste Raw Data columns M "IR/CAP Revised Y/N"
With wb2.Worksheets("IBM Rational ClearQuest Web")
.Range("M2", .Range("M" & Rows.Count).End(xlUp)).Copy
End With
wb1.Worksheets("PS").Range("O2").PasteSpecial xlValues

'######### Copy/Paste Raw Data columns N:P "IRCAP", "IR/CAP Action Status", "IR/CAP Submitted to DHCS"
With wb2.Worksheets("IBM Rational ClearQuest Web")
.Range("N2:P", .Range("P" & Rows.Count).End(xlUp).Row).Copy
End With
wb1.Worksheets("PS").Range("Q2").PasteSpecial xlValues

'######### Copy/Paste Raw Data columns Q "IR-CAP DHCS Date Of Approval"
With wb2.Worksheets("IBM Rational ClearQuest Web")
.Range("Q2", .Range("Q" & Rows.Count).End(xlUp)).Copy
End With
wb1.Worksheets("PS").Range("V2").PasteSpecial xlValues

'######### Copy/Paste Raw Data columns R:S "Test Results Action Status", "TR Date Submitted to DHCS"
With wb2.Worksheets("IBM Rational ClearQuest Web")
.Range("R2:S", .Range("S" & Rows.Count).End(xlUp).Row).Copy
End With
wb1.Worksheets("PS").Range("AB2").PasteSpecial xlValues

'######### Copy/Paste Raw Data columns T "TR Date of Approval"
With wb2.Worksheets("IBM Rational ClearQuest Web")
.Range("T2", .Range("T" & Rows.Count).End(xlUp)).Copy
End With
wb1.Worksheets("PS").Range("AF2").PasteSpecial xlValues

'######### Copy/Paste Raw Data columns U:X "CN State", "CN Action Status", "CN Date Submitted to DHCS", "CN DHCS Date of Approval"
With wb2.Worksheets("IBM Rational ClearQuest Web")
.Range("U2:X", .Range("X" & Rows.Count).End(xlUp).Row).Copy
End With
wb1.Worksheets("PS").Range("AJ2").PasteSpecial xlValues

Range("A2").Select

End Sub
 
Upvote 0
Re: Copy from woorkbook 2 and paste to wb1 range

This should hopefully do the job
Code: 
Sub PSSLA_CopyPaste_Raw_Data()
'
'
    Dim wb1 As Excel.Workbook 'Open PSSLA workbook first
    Dim wb2 As Workbook
    Dim Fname As String
    
    Set wb1 = ActiveWorkbook
    
    Fname = Application.GetOpenFilename("Excel workbooks,*.xls*")
    
    If Fname = "False" Then
    ' ' the user clicked Cancel
    Else
    ' the user selected a file; its path+name is in wb2
        Set wb2 = Workbooks.Open(Fname)
    End If
    
    '######### Copy/Paste Raw Data columns A:K
    With wb2.Worksheets("IBM Rational ClearQuest Web")
        .Range("A2:K" & .Range("K" & Rows.Count).End(xlUp).Row).Copy
    End With
    wb1.Worksheets("PS").Range("A2").PasteSpecial xlValues
    
    '######### Copy/Paste Raw Data column L "Date Closed or CN Submitted"
    With wb2.Worksheets("IBM Rational ClearQuest Web")
        .Range("L2", .Range("L" & Rows.Count).End(xlUp)).Copy
    End With
    wb1.Worksheets("PS").Range("M2").PasteSpecial xlValues
    
    ' ######### Copy/Paste Raw Data columns M "IR/CAP Revised Y/N"
    With wb2.Worksheets("IBM Rational ClearQuest Web")
        .Range("M2", .Range("M" & Rows.Count).End(xlUp)).Copy
    End With
    wb1.Worksheets("PS").Range("O2").PasteSpecial xlValues
    
    '######### Copy/Paste Raw Data columns N:P "IRCAP", "IR/CAP Action Status", "IR/CAP Submitted to DHCS"
    With wb2.Worksheets("IBM Rational ClearQuest Web")
        .Range("N2:P" & .Range("P" & Rows.Count).End(xlUp).Row).Copy
    End With
    wb1.Worksheets("PS").Range("Q2").PasteSpecial xlValues
    
    '######### Copy/Paste Raw Data columns Q "IR-CAP DHCS Date Of Approval"
    With wb2.Worksheets("IBM Rational ClearQuest Web")
    .Range("Q2", .Range("Q" & Rows.Count).End(xlUp)).Copy
    End With
    wb1.Worksheets("PS").Range("V2").PasteSpecial xlValues
    
    '######### Copy/Paste Raw Data columns R:S "Test Results Action Status", "TR Date Submitted to DHCS"
    With wb2.Worksheets("IBM Rational ClearQuest Web")
    .Range("R2:S" & .Range("S" & Rows.Count).End(xlUp).Row).Copy
    End With
    wb1.Worksheets("PS").Range("AB2").PasteSpecial xlValues
    
    '######### Copy/Paste Raw Data columns T "TR Date of Approval"
    With wb2.Worksheets("IBM Rational ClearQuest Web")
    .Range([COLOR=#ff0000]"T2", .Range[/COLOR]("T" & Rows.Count).End(xlUp)).Copy
    End With
    wb1.Worksheets("PS").Range("AF2").PasteSpecial xlValues
    
    '######### Copy/Paste Raw Data columns U:X "CN State", "CN Action Status", "CN Date Submitted to DHCS", "CN DHCS Date of Approval"
    With wb2.Worksheets("IBM Rational ClearQuest Web")
    .Range([COLOR=#ff0000]"U2:X" & .Range[/COLOR]("X" & Rows.Count).End(xlUp).Row).Copy
    End With
    wb1.Worksheets("PS").Range("AJ2").PasteSpecial xlValues
    
    Range("A2").Select

End Sub
If you look at the last 2 sections, note the difference between the parts in red.
 
Upvote 0
You must log in or register to reply here.

Similar threads

KlausW
  • Solved
copy from a closed Workbook error
Replies
6
Views
475
KlausW
KlausW
Logit
  • Question Question
Search In Other Workbook - Copy/Paste Offset Data To First Workbook - Repeat Process
Replies
7
Views
357
Logit
Logit
S
  • Question Question
VBA Copy/Paste from different workbooks
Replies
1
Views
385
NdNoviceHlp
NdNoviceHlp
G
  • Question Question
Copying and pasting between two workbooks
Replies
1
Views
702
rlv01
R
S
  • Solved
Tweak Macro that Imports specific table columns from a different workbook to filter a column first
Replies
4
Views
507
sspatriots
S

Forum statistics

Threads
1,224,827
Messages
6,181,200
Members
453,022
Latest member
RobertV1609

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top