VBA- Paste Values error

Question

I am trying to paste values from another excel worksheet but my code below keeps returning an error. What am i doing wrong ?

Set tempWB = Application.Workbooks.Open(vrtSelectedItem) 'This OPENS the reference workbook

Sheets("Portfolio Worksheet 8.8").Select 'SELECT  A SHEET if you need to
Range("B16:B30").Select 'select SOME RANGE
Selection.Copy 'COPY SOME RANGE

mainWB.Activate 'activate your main workbook
Sheets("Sheet1").Select 'select target sheet
Range("C20").Select 'select target cell
ActiveSheet.PasteSpecial xlPasteValues 'paste the data from the reference worksheet

tempWB.Save 'save and close the reference workbook
tempWB.Close

Tim Williams · Accepted Answer · 2014-09-12 15:41:27Z

1

No need for any selecting or copy-pasting

Set tempWB = Application.Workbooks.Open(vrtSelectedItem) 

With tempWB.Sheets("Portfolio Worksheet 8.8").Range("B16:B30")

    mainWB.Sheets("Sheet1").Range("C20").Resize(.Rows.Count, _
                                                .Columns.Count).Value = .Value
End With

tempWB.Save 'save and close the reference workbook
tempWB.Close

answered Sep 12, 2014 at 15:41

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ian Over a year ago

Thanks this worked after changing the Sheet1 Range to Range("B16:B30")

Uberzen1 · Accepted Answer · 2014-09-12 15:36:21Z

0

It doesn't look like you have declared your variable 'mainWB' ?

answered Sep 12, 2014 at 15:36

Uberzen1

4157 silver badges18 bronze badges

2 Comments

Ian Over a year ago

Oh sorry i didn't put the whole code since it would take space code works with just the paste function, when i do paste values it gives me an error

Uberzen1 Over a year ago

Try: Sheets("Sheet1").Range("C20").PasteSpecial xlPasteValues Application.CutCopyMode = False

Ian · Accepted Answer · 2014-09-12 16:20:57Z

This below worked, thanks for suggestions, however if i want to copy paste several ranges at the same time. So the code below returns a "Next without for" error. I think this second part of the code in ** is causing the problem

Set tempWB = Application.Workbooks.Open(vrtSelectedItem) 'This OPENS the reference workbook

                ' Sheets("Portfolio Worksheet 8.8").Select 'SELECT  A SHEET if you need to


               With mainWB.Sheets("Sheet1").Range("B16:B30")

                tempWB.Sheets("Sheet5").Range("B16:B30").Resize(.Rows.Count, _
                                           .Columns.Count).Value = .Value
                **With mainWB.Sheets("Sheet1").Range("D16:D30")
                tempWB.Sheets("Sheet5").Range("D16:D30").Resize(.Rows.Count, _
                                           .Columns.Count).Value = .Value**

                End With

                tempWB.Save 'save and close the reference workbook
                tempWB.Close

       Next vrtSelectedItem

    Else 'The user pressed Cancel.
    End If
End With

Set fd = Nothing 'Set the object variable to Nothing.

End Sub

Collectives™ on Stack Overflow

VBA- Paste Values error

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related