-1

Hi everyone and thanks in advance for your time.

I am not a huge specialist in vba but sometimes it helps me to gain some good times on repetitive tasks I am working on.

I would like to gathered information from various files to one "recap" file. I named some ranges (one cell each) to ease the lookup for the these informations in all the files.

Here are the lines I began to write:

Sub collectdata()
  Set a = Workbooks.Open("C:\full address\Project1.xlsx")
  Set b = Workbooks.Open("C:\full address\Project2.xlsx")
  Set c = Workbooks.Open("C:\full address\Project3.xlsx")

  ThisWorkbook.Sheets("Data").Range("C3") = a.Application.Range("Project1_Total")
  ThisWorkbook.Sheets("Data").Range("C3") = b.Application.Range("Project2_Total")
  ThisWorkbook.Sheets("Data").Range("C3") = c.Application.Range("Project3_Total")

  a.Close savechanges:=False
  b.Close savechanges:=False
  c.Close savechanges:=False
End Sub

I am stuck with error 1004. I think I know why but I don't know how to correct that.

With one "project" everything everything is okay.
But once I add a second file I get Error 1004.

With one named range from one project it is okay. But once I add a second named range from the same file I get Error 1004.

Thanks for your time.

JensS
  • 1,151
  • 2
  • 13
  • 20
Jeff D.
  • 3
  • 1
  • You need a.Sheets(), b.Sheets() etc... like you do on the left hand side — rather than a.Application, b.Application, etc... – CallumDA Oct 04 '17 at 12:18

1 Answers1

0

This would work under the assumption that the ranges are on the first sheet:

ThisWorkbook.Sheets("Data").Range("C3") = a.Sheets(1).Range("Project1_Total")
ThisWorkbook.Sheets("Data").Range("D3") = b.Sheets(1).Range("Project2_Total")
ThisWorkbook.Sheets("Data").Range("E3") = c.Sheets(1).Range("Project3_Total")

I assumed that you didn't want to overwrite C3 multiple times. If you don't know which sheet the ranges are on, you could use find to find a range in a workbook.

JensS
  • 1,151
  • 2
  • 13
  • 20