Click here to Skip to main content
15,943,477 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:

I'm using this Macro to copy past data from one workbook to another
but i'm geting a run time error 9 subscript out of Range
on this line :

Set shttocopy = wkbSource.Sheets("sheet3").Range("F65:N89")

Any one with good knowlage of VBA know what mistake i done ??

I just want copy data from one range on sheet3 to another in sheet51 with past special just values

I actualy need to Copy two ranges F65:N89 from sheet3 to sheet51 c8
and f93:m94 in sheet3 to c66 in sheet51

Sub copydata()
Dim wkbSource As Workbook
Dim wkbDest As Workbook
Dim shttocopy As Worksheet
Dim wbname As String

' check if the file is open
ret = Isworkbookopen("C:\Test\MASTER_ROTA.xls")
If ret = False Then
' open file
Set wkbSource = Workbooks.Open("C:\Test\MASTER_ROTA.xls")
'Just make it active
 Set wkbSource = Workbooks("MASTER_ROTA.xls")
 End If

' check if the file is open

ret = Isworkbookopen("C:\Test\WK33.xls")
If ret = False Then
' open file
Set wkbDest = Workbooks.Open("C:\Test\WK33.xls")
'Just make it active
 Set wkbDest = Workbooks("WK33.xls")

End If

'perform copy
Set shttocopy = wkbSource.Sheets("sheet3").Range("F65:N89")
shttocopy.Copy: wkbDest.Sheets("Sheet51").Range("C8").PasteSpecial xlPasteValues

End Sub

Function Isworkbookopen(filename As String)
Dim ff As Long, ErrNo As Long
Dim wkb As Workbook
Dim nam As String

wbname = filename
On Error Resume Next

ff = FreeFile()
Open filename For Input Lock Read As #ff
Close ff
ErrNo = Err
On Error GoTo 0
Select Case ErrNo
Case 0: Isworkbookopen = False
Case 70: Isworkbookopen = True
Case Else: Error ErrNo
End Select

End Function

Thank You in Advance
Richard MacCutchan 20-Aug-14 4:13am    
Use the built in macro recorder in Excel to generate the code for you. You can then edit it and modify to your own needs.
SebSCO 20-Aug-14 4:37am    
It's a reason I'm using This I need to make sure the vba will open the right workbook and if the workbook is not ther trow me an error the master rota is not changing but wk33 next week will be wk34 ect I will add to the vba something like Dim WK as String and the WK = Range("A1") and in A1 i will be able to manipulate with the name of the WK and change it in this moment the VBA will trow me an error if the Wk34 was not created and its not in the folder ect

You can achive it by simple macro and i get what you mean by record macro and then edit it to ur needs but macro recorder will just use some think like windows("wk33.xls").activate ect what is no good for me and I will just stock in the same situation i will need to write all this code to push variables to ("") ect
PhilLenoir 20-Aug-14 9:31am    
Do as Richard suggests. I've worked with Office automation for years and it's exactly what I do. Understand the code generated and modify to your needs, including your validation code. Anything you don't understyand, search for help on the class and its members. Please note that you are trying to set a worksheet object as a range (shttocopy). Declare the receiving variable as a range object (e.g. Dim rangetocopy as Range)!
SebSCO 20-Aug-14 15:57pm    
Thenx for pointing the range out i was so focus on other thinks i totaly miss that :)

Now I modifie all and and its working the mistake I place shttocopy as workbook was big :)

Thank you :)

1 solution

You are trying to set range object to variable type worksheet.
Dim shttocopy As Worksheet
Set shttocopy = wkbSource.Sheets("sheet3").Range("F65:N89")

Change shttocopy variable to proper type, for example:
Dim shttocopy As Range
Set shttocopy = wkbSource.Sheets("sheet3").Range("F65:N89")

BTW: do not use macro! Reason is here: 6 Reasons Not To Use Excel VBA (Macros)[^]. I agree with the most of them.
Share this answer

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900