CODEWITHSUNDEEP
excelvba
Matthias Pospiech
Matthias Pospiech

Reputation: 3494

vba: copy sheet contents (not sheet)

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?

Upvotes: 0

Views: 75

Answers (1)

MGP
MGP

Reputation: 2551

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

Upvotes: 1

Related Questions