CODEWITHSUNDEEP
vbaexcel
FrasercT
FrasercT

Reputation: 55

Copying a range into a new spreadsheet

I have found a solution for the basic part of this in this thread Copying range and pasting into new workbook

However when I try to give it my range of

wsI.Range("A1:Q1500").Copy

I am returned "Run-time error 1004".

Do I need to use some other method of copying the range?

For ease of use here is the full slightly edited code from the post I linked

Option Explicit

Sub Sample()
Dim wbI As Workbook, wbO As Workbook
Dim wsI As Worksheet, wsO As Worksheet

'~~> Source/Input Workbook
Set wbI = ThisWorkbook
'~~> Set the relevant sheet from where you want to copy
Set wsI = wbI.Sheets("SICAV")

'~~> Destination/Output Workbook
Set wbO = Workbooks.Add

With wbO
    '~~> Set the relevant sheet to where you want to paste
    Set wsO = wbO.Sheets("Sheet1")

    '~~>. Save the file
    .SaveAs Filename:="Pictay.xls", FileFormat:=56

    '~~> Copy the range
    wsI.Range("A1:Q10").Copy

    '~~> Paste it in say Cell A1. Change as applicable
    wsO.Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
End With
End Sub

Upvotes: 1

Views: 141

Answers (1)

Paras Parmar
Paras Parmar

Reputation: 343

Just saw your question and ran the code. It worked fine. You must be intrigued by what caused the error in the first place. Some research helped with this.

The very simple answer to it is that a problem like this may occur when you give the workbook a defined name and then copy the worksheet several times without first saving and/or closing the workbook.

for eg:-

For i = 1 To 10
   wsI.Range("A1:Q1500").Copy
   wsO.Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
   SkipBlanks:=False, Transpose:=False
Next i

Microsoft recommends, you save and close the workbook periodically while the copy process is occurring, as in the following sample code:

For i = 1 To 10
    wsI.Worksheets(i).Copy After:=wsO.Worksheets(i)        
    'Save, close, and reopen after every 100 iterations:
    If i Mod 100 = 0 Then
        wsO.Close SaveChanges:=True
        Set wsO = Nothing
        '' please change the path to your workbook below.
        Set wsO = Application.Workbooks.Open("c:\wsO.xls")
    End If
Next

Upvotes: 1

Related Questions