Posted By: Anonymous
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
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).