VBA Copy Paste with Direct Cell links

[WWII_Buff]

Here is what I am trying to do.


I have 50+ worksheets with identical headers but different row lengths and data. Rows "A4:BA4" are the headers, A5:BA... is all data. I would like to combine all 50 sheets into one worksheet called "Master".


#1 , I'd like to bring over the header from the 1st worksheet as well, but not the subsequent worksheets.
#2 , I'd like to skip blank rows on all sheets
#3 , Instead of copy paste special value, I need the "Master" tab data to directly link to the specific worksheet e.g.

FormulaR1C1 = "='27900'!R[3]C[-1]"

where 27900 = a sheet name


Thank you all so much!

 

[offthelip]

Here is some code that puts the links is, I forgot about the blank line but hopefully you can do that yourself.

Sub test()
 With Worksheets("Master")
indi = 5
firstt = True
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
 If (ws.Name <> "Master") Then
     ws.Select
   If firstt Then
    headers = Range(Cells(1, 1), Cells(4, 53))
    firstt = False
   End If
    lastrow = Cells(Rows.Count, "A").End(xlUp).Row
    inarr = Range(Cells(5, 1), Cells(lastrow, 53))
    For i = 1 To lastrow - 5
     For j = 1 To 53
     retr = "r" & (4 + i) & "C" & j
     inarr(i, j) = "=" & ws.Name & "!" & retr
     Next j
    Next i
    Range(.Cells(indi, 1), .Cells(indi + lastrow - 5, 53)).Formula = inarr
    indi = indi + lastrow - 4
 End If
Next ws
 Range(.Cells(1, 1), .Cells(4, 53)) = headers
End With


End Sub

 

[WWII_Buff]

Dude! Amazing! Question - what's with the 53?

 

[offthelip]

53 is column BA

 

[WWII_Buff]

@offthelip please don't be mad but how do I also,

#1 add the contents of $B$1 from each worksheet into Column "A" of the Master sheet with the header "Cost Center"
#2 add

=IFERROR(VLOOKUP($A2,'2018_EXP'!D:I,6,0),"")

in Column "G" of the Master with the header "Cost Center Type"

Thank you so much!

 

[offthelip]

Is the $A2 in your lookup equation supposed to be referencing A2 on each worksheet?
also do you want to put that code in column G or just the result of the lookup?

 

[WWII_Buff]

Is the $A2 in your lookup equation supposed to be referencing A2 on each worksheet? No, it's referencing the Master. This was added with #1
also do you want to put that code in column G or just the result of the lookup? Wow - Umm the result of the formula

Thanks!

 

[offthelip]

So if I have got this right, what you want to do, is take the value of B1 from each worksheet look that value up in column D of 2018_EXP and put the value from column I of 2018_EXP into the appropriate row of Column G of the Master
( Note I always avoid using Vlookup in vBA , it is too slow and unflexible)
Ps I have got to go out so I can't do this at the moment, don't panic, it is quite easy

 

[WWII_Buff]

That's exactly it! I am so glad you ran across my post! have a Guinness on me bro! Truly a God Send!

 

[offthelip]

Here you are, I have added the cost center and cost center type, I also made some corrections to the index offset because it wasn't completing the loop correctly

Sub test()
 With Worksheets("2018_EXP")
 lr = .Cells(Rows.Count, "D").End(xlUp).Row
  looktbl = Range(.Cells(1, 1), .Cells(lr, 9))
 End With
 With Worksheets("Master")
indi = 5
firstt = True
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
 If (ws.Name <> "Master") Then
 If (ws.Name <> "2018_EXP") Then
      
      ws.Select
    CostC = Range(Cells(1, 2), Cells(1, 2))
    Costype = ""
    For k = 1 To lr
     If CostC = looktbl(k, 4) Then
      ' Cost centre found
       Costype = looktbl(k, 9)
       Exit For
     End If
    Next k
   If firstt Then
    headers = Range(Cells(1, 1), Cells(4, 53))
    headers(1, 1) = "Cost Center"
    headers(1, 7) = "Cost Center Type"
    firstt = False
   End If
    lastrow = Cells(Rows.Count, "A").End(xlUp).Row
    inarr = Range(Cells(5, 1), Cells(lastrow, 53))
    For i = 1 To lastrow - 4
     For j = 2 To 53
     retr = "r" & (4 + i) & "C" & j
     inarr(i, j) = "=" & ws.Name & "!" & retr
     Next j
     ' put in the cost centre and cost type
     inarr(i, 1) = CostC
     inarr(i, 7) = Costype
    Next i
    Range(.Cells(indi, 1), .Cells(indi + lastrow - 5, 53)).Formula = inarr
    indi = indi + lastrow - 4
 End If
 End If
Next ws
 Range(.Cells(1, 1), .Cells(4, 53)) = headers
End With


End Sub