Macro formula that will populate a duplicate table in another workbook, it starts at the second row of the table being imported from

[sspatriots]

I have this code that I bring into another workbook to replicate a table when I open my workbook. Then, then turns everything into all to values. So we have the latest information from the source workbook in this file every time we open the destination workbook.

The formula I'm using right now is shown below:

=IF(ISBLANK('Manufacturing Detail Schedule1.xlsx'!COMPLETE_FY_21[@[DEM Job '#]]),"",'Manufacturing Detail Schedule1.xlsx'!COMPLETE_FY_21[@[DEM Job '#]])

The problem is that when this is put in the first row of the destination table and copied down, it starts with row 2 of the source workbook. I did notice that if I go into the source workbook and insert a row above the table on that worksheet the results come out right, but I'd rather not mess with the other file if I can avoid it. Please let me know if I have left anything off here. Sometimes, I'm not sure what is needed for assistance.


Thanks, SS

 

[sspatriots]

Let me clarify, because I know Peter has mentioned this before. Too late to modify the previous post. I got called for something like this before, although this one is a little of both. So, I thought it would be prudent to explain better. The first post is indeed a formula that goes into the first column of the first row in a destination table. By habit I called it a macro formula because it is actually populated into that cell from a macro. The part of the macro that contains the formula is shown below:

tbName2 = ws1.Range("A4")
Set rng2 = Range(tbName2)
rng2.Cells(1, 1).Formula = "=IF(ISBLANK('Manufacturing Detail Schedule1.xlsx'!COMPLETE_FY_21[@[DEM Job '#]]),"""",'Manufacturing Detail Schedule1.xlsx'!COMPLETE_FY_21[@[DEM Job '#]])"
rng2.Cells(1, 2).Formula = "=IF(ISBLANK('Manufacturing Detail Schedule1.xlsx'!COMPLETE_FY_21[@[DEM Job '#]]),"""",IF(ISBLANK('Manufacturing Detail Schedule1.xlsx'!COMPLETE_FY_21[@[Finals Received]]),"""",'Manufacturing Detail Schedule1.xlsx'!COMPLETE_FY_21[@[Finals Received]]))"
rng2.Cells(1, 3).Formula = "=IF(ISBLANK('Manufacturing Detail Schedule1.xlsx'!COMPLETE_FY_21[@[DEM Job '#]]),"""",IF(ISBLANK('Manufacturing Detail Schedule1.xlsx'!COMPLETE_FY_21[@[Ship Date]]),"""",'Manufacturing Detail Schedule1.xlsx'!COMPLETE_FY_21[@[Ship Date]]))"
rng2.Formula = rng2.Value

I hope this helps...

Sorry for any confusion, SS