vba: copy sheet contents (not sheet)

Question

I want to create a copy of a sheet from a different Workbook First I open the Workbook

' Open Workbook in background
Dim wbImport As Workbook
Set wbImport = Workbooks.Open(Filename:=strFile, UpdateLinks:=True, ReadOnly:=True)
wbImport.Windows(1).Visible = True ' set to false

Then I try to copy it. The current code is based on the macro recorder. I also like to get hints how to improve this code

' Copy first sheet in Import Workbook
With wbImport.Sheets(1)
    .Range("A1").Select
    .Range(Selection, Selection.End(xlToRight)).Select
    .Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
End With
ThisWorkbook.Sheets(outsheet).Select
Range("A1").Select
ActiveSheet.Paste

The problem is, that the select function fails with error 1004. What is wrong?

The clue is to set references to workbook objects, sheets and ranges so you won't have to `.Select`, `.Activate`, `.Copy` nor `.Paste` them. A very good starting point for you would be to read [this](https://stackoverflow.com/q/10714251/9758194) post and it's answers. — JvdV, Sep 09 '19 at 08:40
Use "Option Explicit" in all your vba coding! Which "Select" does actually fail? Is the variable 'outsheet' defined? — simple-solution, Sep 09 '19 at 08:46
See 5 copy alternatives: [https://stackoverflow.com/questions/54473767/how-to-fix-graphs-not-copying-over-to-new-sheet-with-pastespecial/54477636#54477636] — simple-solution, Sep 09 '19 at 09:07

score 1 · Accepted Answer · answered Sep 09 '19 at 08:43

1

Try This:

wbImport.Sheets(1).Cells.Copy
'Hard Copy the values like this:
ThisWorkbook.Sheets(outsheet).Range("A1").PasteSpecial xlPasteValues
ThisWorkbook.Sheets(outsheet).Range("A1").PasteSpecial xlPasteFormats

answered Sep 09 '19 at 08:43

MGP

2,480
1
18
31

vba: copy sheet contents (not sheet)

1 Answers1