VBA Macro

Hi there,

i am a bit new to VBA coding and i have been having an issue

i have been trying to link an active cell after filtering through a data to a new sheet.

I would like to make it dynamic since the active cell would change from week to week

after sorting through a data i stored the active cell by the code below

a = ActiveSheet.AutoFilter.Range.Offset(1).SpecialCells(xlCellTypeVisible).Cells(1, 1).Select

then i want to link that active cell to a new sheet in A2 with a sheet name "Sheet1" which i followed up with this code

Sheets("Sheet1").Select
Range("A2").Select

all of the above works out and i am having trouble in the final step where i would like a to be linked in A2 in Sheet1

Any suggestions would be greatly appreciated

Thank you for your time.

thank you royUK.

I meant to link the cell in the main data tab to a new tab named "Sheet1" where it would always pull from the data tab. For example if the data I need is in A423846 in the data tab after being filtered, I would type in =Data!A423846 in A1 of "Sheet1".

In the next week that data tab would update and the data in A423846 would move to A423850. This is an example it might move 2 to 5 columns down.

I would have to filter the data tab every single week to find a specific countries and where the data starts.

I picked up the Autofilter from another thread since it related to my problem if there is a better code I am open to suggestions.

I will attach a file shortly.

file attached with the macro in it.

Thank you for your help

OWID_Data1.xlsb