Copy Paste error message

[22strider]

Hello Friends

Could you please check why am I getting error message "Script out of Range" for the following code for copying and pasting the data to another workbook?

Sub CreateWorkBook()

    Dim i As Long
    Dim strPath As String
    
    strPath = "C:\Rajesh\"
        
        For i = 1 To 10
        
      [Color=blue]Workbooks.Add.SaveAs (strPath & "Load" & i) [/Color]
            
            ThisWorkbook.Sheets("DataSheet").Range("A1,G20").Copy Workbooks(strPath & "Load" & i & ".xlsx") _
            .Sheets(1).Range("A1")
                        
        Next i

End Sub

The code works upto the line highlighted in blue and then gives the error message.
I am using this code in another bigger code.

Thanks for your help
Rajesh

 

[Norie]

Try this.

Sub CreateWorkBook()
    Dim wbNew As Workbook
    
    Dim i As Long
    Dim strPath As String
    
    strPath = "C:\Rajesh\"
        
        For i = 1 To 10
        Set wbNew = Workbooks.Add
        
        wbNew.SaveAs strPath & "Load" & i
            
            ThisWorkbook.Sheets("DataSheet").Range("A1:G20").Copy wbNew.Sheets(1).Range("A1")
            
                        
        Next i
End Sub

Note I've changed the range you are copying - you would get an error with Range("A1, G20").

If you do want to copy just cells A1 and G20 then you would need to alter the code further.

 

[22strider]

Hello Norie

The code worked very well.

Thanks
Rajesh