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
ret = Isworkbookopen("C:\Test\MASTER_ROTA.xls")
If ret = False Then
Set wkbSource = Workbooks.Open("C:\Test\MASTER_ROTA.xls")
Else
Set wkbSource = Workbooks("MASTER_ROTA.xls")
End If
ret = Isworkbookopen("C:\Test\WK33.xls")
If ret = False Then
Set wkbDest = Workbooks.Open("C:\Test\WK33.xls")
Else
Set wkbDest = Workbooks("WK33.xls")
End If
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