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!

 

[WWII_Buff]

BINGO! This is awesome! Last ask!... I hope.

Where you added the cost center in Column A, there was data in that column that was over written - can you post everything starting in column "B" in the master. Then add the cost center in the empty Column "A".

Also, if I needed to exclude more sheets, it's a matter of just adding then like you did the other 2 sheets?

 

[offthelip]

Try this, but I might note that your requirements have not been specified very well, i.e. incomplete and ambiguous

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, 7) = "Cost Center Type"
    firstt = False
   End If
    lastrow = Cells(Rows.Count, "A").End(xlUp).Row
    inarr = Range(Cells(5, 1), Cells(lastrow, 54))
    For i = 1 To lastrow - 4
     For j = 1 To 53
     retr = "r" & (4 + i) & "C" & j
     inarr(i, j + 1) = "=" & 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, 2), .Cells(4, 54)) = headers
 Range(.Cells(1, 1), .Cells(1, 1)) = "Cost Center"


End With


End Sub

 

[WWII_Buff]

@offthelip Dude - I agree with you. I and do apologize.

I think it's because I had one thing in mind - the part that was stomping me was the logic that created the direct cell reference. So I didn't pose the request well with all the information you needed. I am sorry.

Testing your code now!

I do appreciate you!

 

[offthelip]

I have spotted an error,, i didn't change the range when i write inarr back to the worksheet, the 53 should be 54

 

[WWII_Buff]

I have spotted an error,, i didn't change the range when i write inarr back to the worksheet, the 53 should be 54

This part?

[COLOR=#333333]Range(.Cells(indi, 1), .Cells(indi + lastrow - 5, 53)).Formula = inarr[/COLOR]

 

[offthelip]

Yes

 

[WWII_Buff]

Rgr that bro!

 

[WWII_Buff]

@offthelip - So my test worked!

However, when moved to my "Go Live Sheet" with more tabs, it error-ed out:

Compile error: Argument not optional here:

LastRow = Cells(Rows.Count, "A").End(xlUp).Row

Here are the edits I made:

For Each ws In ActiveWorkbook.Worksheets If (ws.Name <> "Master") Then
 If (ws.Name <> "2018_EXP") Then
 If (ws.Name <> "2017_EXP") Then
 If (ws.Name <> "2018_WASTE_VOL") Then
 If (ws.Name <> "2017_WASTE_VOL") Then
 If (ws.Name <> "Presentation") Then
 If (ws.Name <> "DATASHEET") Then
 If (ws.Name <> "SITE PCC VIEW") Then

I hope I didn't break anything

 

[offthelip]

Do you have a sheet with nothing in column a, i suspect rows.count is zero, thinking about it that can't be a compile error. Look at the line above the error

 

[offthelip]

Have you put in the correct number of end if for all the additional if statements