Copying and pasting data using VBA code
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).
'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.
精彩评论