0

I am trying to figure out how to consolidate current worksheet. It would be impossible for me to list everything I've tried but I'll break it down as best I can.

Macro Recorder recorded this when consolidating:

Range("G1").Select
    Selection.Consolidate Sources:= _
        "'C:\Users\sognibene\Desktop\[Current needs 9-19-2019 working.xlsx]PO'!C1:C2" _
        , Function:=xlSum, TopRow:=True, LeftColumn:=True, CreateLinks:=False

This works if I'm using the workbook called "Current needs 9-19-2019 working.xlsx", but won't work when I change workbooks or worksheets. I need the code to work on what ever workbook and preferably the worksheet I'm currently on.

I've tried this:

Range("G1").Select
    Selection.Consolidate Sources:= _
        "'C:\Users\sognibene\Desktop\" & "["&ActiveWorkbook.Name&"]" & "PO'!C1:C2" _
        , Function:=xlSum, TopRow:=True, LeftColumn:=True, CreateLinks:=False

That didn't work. I've tried it like this:

Range("G1").Select
    Selection.Consolidate Sources:= _
        "'C:\Users\sognibene\Desktop\" &ActiveWorkbook.Name& "PO'!C1:C2" _
        , Function:=xlSum, TopRow:=True, LeftColumn:=True, CreateLinks:=False

And I've tried a hundred other things. I've tried using ActiveSheet, ActiveWorkbook, and what ever else I could think of.

halfer
  • 19,824
  • 17
  • 99
  • 186
Sabatino Ognibene
  • 142
  • 13

1 Answers1

3

One must put spaces around the & in vba.

This worked:

 Range("G1").Select
    Selection.Consolidate Sources:= _
        "'C:\Users\sognibene\Desktop\" & "[" & ActiveWorkbook.Name & "]" & "PO'!C1:C2" _
        , Function:=xlSum, TopRow:=True, LeftColumn:=True, CreateLinks:=False
Scott Craner
  • 148,073
  • 10
  • 49
  • 81
Sabatino Ognibene
  • 142
  • 13
  • 4
    Note that this is because the `&` in `foo&` gets parsed as a [type hint](https://stackoverflow.com/a/41044332/1188513) for a `Long`. With the space, `&` is the string-concatenation operator. Also note that [VB.NET also supports them](https://learn.microsoft.com/en-us/dotnet/visual-basic/programming-guide/language-features/data-types/type-characters), so this isn't just "in VBA". – Mathieu Guindon Sep 26 '19 at 20:47