Could someone please edit this code for me? Thank for helping!

[pedie]

Hi,

I am running this code from book1. trying to copy from book1 and then paste it in book2.

Macro3 is to simply copy from region "A3 to F3"

I am getting Runtime error '9'
subcript out of range

Please help!

I understand, I think you guys must be tired of my thread but I would request not to. I need help

Pedie

Sub Test2()
    Dim wb As Workbook, MyFile As String
    MyFile = "C:\book2.xls"
    On Error Resume Next
        Set wb = Workbooks.Open(Filename:=MyFile[COLOR=red], Password:="123"[/COLOR]) 
        On Error GoTo 0

    
    If wb Is Nothing Then MsgBox "Couldn't locate " & MyFile: Exit Sub
  
call Macro3
    
With wb
       With sheet1
            .Range("A3").Select
             Do Until ActiveCell.Value = ""
             ActiveCell.Offset(1, 0).Select
             Loop
             ActiveSheet.Paste            
 
            End With
        .Save
        .Close
    End With
End Sub

 

[pedie]

Now I am gettng runtime error '1004'
pastespecial method of range class failed

Sub Test2()
    
    Application.ScreenUpdating = False
    ThisWorkbook.Sheets("Sheet1").Range("A3:F3").Copy
    Dim wb As Workbook, MyFile As String
    MyFile = "C:\book2.xls"
    On Error Resume Next
        Set wb = Workbooks.Open(Filename:=MyFile, Password:="123")
        On Error GoTo 0
    If wb Is Nothing Then MsgBox "Error occured": Exit Sub
        
    With wb
        .Unprotect Password:="123"
    With Sheets("Sheet1")
            .Range("A3").Select
             Do Until ActiveCell.Value = ""
             ActiveCell.Offset(1, 0).Select
             Loop
             Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
             
                       
        End With
            .Save
            .Close
    End With
    ThisWorkbook.Sheets("Sheet1").Select
    Application.CutCopyMode = False
    Cells(1).Select
    
    Application.ScreenUpdating = True
    
End Sub

 

[VoG]

pedie

You haven't copied anything. That's why the pastespecial fails.

 

[pedie]

pedie

You haven't copied anything. That's why the pastespecial fails.

I thought the code below has already copied to be pasted...
Vog, so what do i do?

ThisWorkbook.Sheets("Sheet1").Range("A3:F3").Copy

 

[pedie]

This code works fine. but I dont know how to apply this in my code...

Sub try()
Windows("Book1.xls").Activate
Sheet1.Range("A3:F3").Copy
    Windows("Book2.xls").Activate
    Sheets("Sheet2").Select
    cells(1).select
    ActiveSheet.Paste
    Windows("Book1.xls").Activate
    Application.CutCopyMode = False
    Range("A3").Select
End Sub

 

[pedie]

Got it working now. thanks

Thanks Vog!