Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: Excel VBA
Hi
 

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")
Else
'Just make it active
 'Workbooks("C:\stack\file1.xlsx").Activate
 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")
Else
'Just make it active
 'Workbooks("C:\stack\file2.xlsx").Activate
 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
Posted 19-Aug-14 14:35pm
SebSCO744
Comments
Richard MacCutchan at 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 at 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 at 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 at 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

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

You are trying to set range object to variable type worksheet.
'definition
Dim shttocopy As Worksheet
'usage:
Set shttocopy = wkbSource.Sheets("sheet3").Range("F65:N89")
 
Change shttocopy variable to proper type, for example:
'definition
Dim shttocopy As Range
'usage:
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.
  Permalink  

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

  Print Answers RSS
0 OriginalGriff 520
1 Maciej Los 290
2 Richard MacCutchan 265
3 BillWoodruff 265
4 Suraj Sahoo | Coding Passion 155
0 OriginalGriff 8,764
1 Sergey Alexandrovich Kryukov 7,437
2 DamithSL 5,639
3 Maciej Los 5,279
4 Manas Bhardwaj 4,986


Advertise | Privacy | Mobile
Web03 | 2.8.1411028.1 | Last Updated 17 Sep 2014
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100