0

I am a very new user and am trying to open up three workbooks and copy the same cell range of the three books to three different ranges in a fourth book.

I have done a good amount of troubleshooting but I cannot find anything to help fix this.

The problem points to Sub WeeklyLeagues()

Option Explicit

    Sub WeeklyLeagues()

    Dim x_both As workbook_both
    Dim x_sf As workbook_sf
    Dim x_mf As workbook_mf
    Dim y As workbook_final

    Set x_both = Workbooks.Open("Sheet1 Path")
    Set x_sf = Workbooks.Open("Sheet2 Path")
    Set x_sf = Workbooks.Open("Sheet3 Path")
    Set y = Workbooks.Open("Sheet4 Path")

    x_both.Sheets("Request 4 Rank 1").Range("A2:E18").Copy
    y.Sheets("Source").Range("M2:Q18").PasteSpecial

    x_sf.Sheets("Request 4 Rank 1").Range("A2:E18").Copy
    y.Sheets("Source").Range("A2:E18").PasteSpecial

    x_mf.Sheets("Request 4 Rank 1").Range("A2:E18").Copy
    y.Sheets("Source").Range("G2:k18").PasteSpecial

    'Close x:
    x_both.Close
    x_sf.Close
    x_mf.Close

    End Sub
EJO
  • 1
  • 6
    All your Dim statements are in error. The As part needs to be `As Workbook` – Scott Craner Jul 20 '16 at 16:39
  • What is a `workbook_both`? Have you not defined that anywhere? – rory.ap Jul 20 '16 at 16:42
  • Also I believe you are going to need to add the full file path and name to the Open lines. – Scott Craner Jul 20 '16 at 16:42
  • @ScottCraner I see what you mean. I was mistaken and misread the naming syntax. The files open now but they do not paste into the file. I get "Run-time error '91': Object variable or With block variable not set. – EJO Jul 20 '16 at 16:52
  • @EJO you need to add your workbook path, like `Dim wb_path as String` , and then for example use `wb_path = "\\C:\"` – Shai Rado Jul 20 '16 at 17:36

0 Answers0