Copying and pasting data using VBA code

Question

I have a button on a spreadsheet that, when pressed, should allow the user to open a file, then copy columns A-G of the spreadsheet "Data", then paste the data from those columns on the current sheet.

I have a logic error in the code; it runs, but it pastes the selection in the wrong place.

I am having trouble referencing the two workbooks.

Here is my code:

Sub Button1_Click()
    Dim excel As excel.Application
    Dim wb As excel.Workbook
    Dim sht As excel.Worksheet
    Dim f As Object

    Set f = Application.FileDialog(3)
    f.AllowMultiSelect = False
    f.Show

    Set excel = CreateObject("excel.Application")
    Set wb = excel.Workbooks.Open(f.SelectedItems(1))
    Set sht = wb.Worksheets("Data")

    sht.Activate
    sht.Columns("A:G").Select
    Selection.Copy
    Range("A1").Select
    ActiveSheet.Paste

    wb.Close
End Sub

Lance Roberts · Accepted Answer · 2013-11-26 16:19:12Z

22

Use the PasteSpecial method:

sht.Columns("A:G").Copy
Range("A1").PasteSpecial Paste:=xlPasteValues

BUT your big problem is that you're changing your ActiveSheet to "Data" and not changing it back. You don't need to do the Activate and Select, as per my code (this assumes your button is on the sheet you want to copy to).

edited Nov 26, 2013 at 16:19

answered Jul 21, 2011 at 16:02

Lance Roberts

22.9k32 gold badges115 silver badges132 bronze badges

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

1 Comment

Lance Roberts Over a year ago

@spookymodem, you can click the arrow and accept the answer if it did the trick for you.

D1g1t4Lnrg · Accepted Answer · 2016-01-09 06:54:24Z

1

'So from this discussion i am thinking this should be the code then.

Sub Button1_Click()
    Dim excel As excel.Application
    Dim wb As excel.Workbook
    Dim sht As excel.Worksheet
    Dim f As Object

    Set f = Application.FileDialog(3)
    f.AllowMultiSelect = False
    f.Show

    Set excel = CreateObject("excel.Application")
    Set wb = excel.Workbooks.Open(f.SelectedItems(1))
    Set sht = wb.Worksheets("Data")

    sht.Activate
    sht.Columns("A:G").Copy
    Range("A1").PasteSpecial Paste:=xlPasteValues


    wb.Close
End Sub

'Let me know if this is correct or a step was missed. Thx.

answered Jan 9, 2016 at 6:54

D1g1t4Lnrg

111 bronze badge

Collectives™ on Stack Overflow

Copying and pasting data using VBA code

2 Answers 2

1 Comment

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Linked

Related