Hyperlinking data in other sheets from one sheet where data is imported by Macro

[masud8956]

Hi,

In sheet 1 of my worksheet, I have imported data from multiple worksheets using the following VBA provided by a valued Board Regular of this forum (@mumps):

Sub Test()    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Sheets("DATA").Cells.ClearContents
    Dim LastRow As Long
    Dim firstRow As Long
    Dim myDir As String, fn As String, n As Long, t As Long, Cell As String
    Const wsName As String = "Summary of the Year"
    Const myRng As String = "G77:U796"
    myDir = "D:\Aircrew_Flying_Hour"
    fn = Dir(myDir & "\*.xlsx")
    If fn = "" Then MsgBox "No files in the folder": Exit Sub
    With Range(myRng)
        n = .Rows.Count: t = .Columns.Count
        Cell = .Cells(1).Address(0, 0)
    End With
    Do While fn <> ""
        With Sheets("Data").Range("a" & Rows.Count).End(xlUp)(1).Resize(n, t)
            
            .Formula = "=if('" & myDir & "\[" & fn & "]" & wsName & "'!" & Cell & "<>""""," & _
                       "'" & myDir & "\[" & fn & "]" & wsName & "'!" & Cell & ","""")"
            .Value = .Value
        End With
        fn = Dir
    Loop
    firstRow = Sheets("DATA").Range("A1:A" & Sheets("DATA").Rows.Count).Cells.SpecialCells(xlCellTypeBlanks).Row
    LastRow = Sheets("DATA").Cells(Rows.Count, "A").End(xlUp).Row
    Sheets("DATA").Range("A" & firstRow & ":A" & LastRow).AutoFilter Field:=1, Criteria1:="="
    Sheets("DATA").Range("A" & firstRow & ":A" & LastRow).SpecialCells(xlCellTypeVisible).EntireRow.Delete
    If Sheets("DATA").AutoFilterMode Then Sheets("DATA").AutoFilterMode = False
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
End Sub

The code allows all previous data to be cleared (ClearContents) from Sheet 1 and import fresh data every time I run the code.

Now in Sheet 2, 3 and 4, I am trying to do some calculations using these 'imported data at Sheet 1' through regular excel formulae. But when the code clears all previous data and import fresh data, all cells in Sheet 2,3 and 4 where I have used any type of formulae linking to Sheet 1 turns into "#REF!"

What modification do I need to keep my formulae intact in Sheet 2, 3 and 4 while importing the data in Sheet 1 using VBA? Is there any other way around without modifying the macro?

Thanks in advance.

 

[daverunt]

Any use?

https://stackoverflow.com/questions/4430755/excel-clearcontent-preserving-formulas

 

[offthelip]

I think your problemn is because you are deleting rows entirely with this line. :

Sheets("DATA").Range("A" & firstRow & ":A" & LastRow).SpecialCells(xlCellTypeVisible).EntireRow.Delete

This will cause the Ref errors. I suggest you change this to clearcontents, then it should avoid the ref errors.

 

[masud8956]

I think your problemn is because you are deleting rows entirely with this line. :

Sheets("DATA").Range("A" & firstRow & ":A" & LastRow).SpecialCells(xlCellTypeVisible).EntireRow.Delete

This will cause the Ref errors. I suggest you change this to clearcontents, then it should avoid the ref errors.

Thanks for the tip. Actually, it kinda worked. I tried the following as you suggested

    Sheets("DATA").Range("A" & firstRow & ":A" & LastRow).SpecialCells(xlCellTypeVisible).EntireRow.ClearContents

So the ref error problem is solved.

Now the problem is that the previous code allowed me data import while excluding the blank rows from input files. But now that feature is gone! I would like to exclude the blank rows from input ranges.

 

[masud8956]

Any use?

https://stackoverflow.com/questions/4430755/excel-clearcontent-preserving-formulas

No sir! Could not make that option work.