Run-Time Error - VBA Copy and Paste Multiple Row From One Workbook to Another

Greeting to all! I am new to VBA and also to this forum. Seeking help with vba code (see below) to copy paste data from multiple rows from one workbook to another workbook (MasterServiceHistories) located on our server on 'S' drive. I continue to get a Run-time error '1004': Method 'Open' of object 'Workbooks' failed. I am attaching a copy of my workbook (adbTESTER).

I'm trying to use this code to copy the service history from the 'Service History' tab (activesheet) according to property address by using an ActiveX control button 'Send to File' to paste to the corresponding sheet in 'Master Service Histories' workbook that is located on our server 'S' drive.

I would be very grateful for any help to resolve this issue.



[Code]

Private Sub CommandButton1_Click()
LastRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row

For i = 5 To LastRow

If Cells(i, 1) = "100 MALLARD COVE" Then
Range(Cells(i, 1), Cells(i, 18)).Select
Selection.Copy


Workbooks.Open Filename:="S:\FREDDIE MAC\Billing\MasterServiceHistories.xlsx"

Dim p As Integer, q As Integer

p = Worksheets.Count

For q = 1 To p

If ActiveWorkbook.Worksheets(q).Name = "100 MALLARD COVE" Then
Worksheets("100 MALLARD COVE").Select
End If

Next q

erow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

ActiveSheet.Cells(erow, 1).Select
ActiveSheet.Paste
ActiveWorkbook.Save
ActiveWorkbook.Close
Application.CutCopyMode = False
End If

Next i


End Sub

[Code]

Hi jxc,

Try this:

Please Login or Register  to view this content.

BTW - Your last code tag needs a leading whack i.e. [/CODE]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks for the reps