Hello friends,

I want to extract the selected column data from sheet ‘Access’ to sheet ‘Data.
There is a master data on sheet ‘Access’ from range ‘B8:Q3000’. This table has a data entry time to time which is not fixed in range. The range of entered data is changing time to time. The main table range is ‘B2:Q3000’. I don’t want to import all the data to the ‘Data’ sheet. At present there are about 500 entries which may increase day by day. The range of data entered is not fixed so I have a problem of giving cell range for pasting the formula on ‘Data’ sheet. Now only first entry is imported from the ‘Access’ sheet to ‘Data’ sheet because I’m unable to fix the paste range.

Option Explicit

Sub RefreshData()

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

Worksheets("Data").Activate

    Range("B2").FormulaR1C1 = "=IF(Access!R[6]C="""","""",Access!R[6]C)"
    Range("C2").FormulaR1C1 = "=IF(Access!R[6]C[4]="""","""",Access!R[6]C[4])"
    Range("D2").FormulaR1C1 = "=IF(Access!R[6]C[8]="""","""",Access!R[6]C[8])"
    Range("E2").FormulaR1C1 = "=IF(Access!R[6]C[8]="""","""",Access!R[6]C[8])"
    Range("F2").FormulaR1C1 = "=IF(Access!R[6]C[8]="""","""",Access!R[6]C[8])"
    Range("G2").FormulaR1C1 = "=IF(Access!R[6]C[8]="""","""",Access!R[6]C[8])"
    Range("H2").FormulaR1C1 = "=IF(Access!R[6]C[8]="""","""",Access!R[6]C[8])"
    Range("I2").FormulaR1C1 = "=IF(Access!R[6]C[8]="""","""",Access!R[6]C[8])"
    
    With Range("B2:I3000")
    .Value = .Value
    End With
    
With Application
.EnableEvents = True
.ScreenUpdating = True
End With

    MsgBox "Done", 64
    
End Sub
Any help will be highly appreciated.

Thanking you in anticipation.