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


    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.

    Edited once, last by royUK: add code tags ().

  • Welcome to Ozgrid. Please read the Forum Rules to understand how to use the Forum. I have added code tags this time.

    I don't understand what you mean by linking the cell to another sheet.

    What has AutoFilter got to do with this when you mention sort?

    An example workbook might help

  • 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.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!