Copy cell to accommodate cell variable content in VBA

    I have a list of some 50 different numbers. Weekly a single report is run in which only some of the names produce a report. That's the base. I can produce a list of the names that actually report data. I can create a macro that copies a cell (with a name in it) and run what I want it to do. I can do this for successive cells but it seems to record the cell data in the syntax rather than just the cell. I need the macro to see the cell and copy what ever is is the cell (which changes week to week). Is this possible?

    Re: Copy cell to accommodate cell variable content in VBA


    I honestly cant decipher anything in your thread. Sorry. I dont understand what it is you need help with. it seems to be this key line:


    Quote

    ...seems to record the cell data in the syntax rather than just the cell. I need the macro to see the cell and copy what ever is is the cell...


    Try variations of

    Code
    msgbox range("A1").text
msgbox range("A1").Value
msgbox range("A1").Value2


    All are blind guesses without a clear understanding of your problem, but maybe one of them is what you need.


    It is unlikely I or anyone else can help further unless you attached some sample workbook, with sample code and identify the expected output that you need.


    Thanks
    Ger

    _______________________________________________
    There are 10 types of people in the world. Those that understand Binary and those that dont. :P


    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25... ;)

    _______________________________________________

    Re: Copy cell to accommodate cell variable content in VBA


    Hi sorry I'm a bit new to this. You are right the critical part of my mail is "I need the Macro.....Blah! blah!"


    here is the first part of the code



    First line selects a cell, whose data can change. Line 4 above shows the data in the cell which in this case is "006".


    I need it to just copy the cell F2 whatever the data in the cell is.


    Hope this explains. Maybe I should say I am a novice at writing the macro. I create macros by recording the task as I perform it. I can read and see the language in the macro but am not confident at writing it.


    Thanks for your interest.

    Re: Copy cell to accommodate cell variable content in VBA


    Hi Legin,


    Thanks for posting the code. You were missing a forward slash (/) in your ending code tag and I added that for you. But thanks for trying anyway.


    The code from the Macro recorder is actually a good place to start, so there is no harm in using this. I use it sometimes myself for stuff that I just cant immediately remember. You need to realise though that the Macro recorder puts in a lot of extra "stuff" that just isnt needed, most especially things like ".select". I'll get back to that in a second.


    So I think your problem is you want to filter the column of data based on the value stored in F2?


    If so, I would do it like this:

    Code
    dim my_value as variant
my_value = range("f2").value 'stores the value of F2 in a variable.
Sheets("Import Sheet").Select 
ActiveSheet.Range("$A$2:$Q$23").AutoFilter Field:=2, Criteria1:="=*" & my_value & "*", Operator:=xlAnd  'filter the column for *the value stored in my_value*


: rest of your code goes here.
:
:


    As I mentioned, The macro recorder uses .select way too much. I have not seen an instance where there was a need for .select in VBA code. The above code could be written as:

    Code
    dim my_value as variant
my_value = range("f2").value 'stores the value of F2 in a variable.
Sheets("Import Sheet").Range("$A$2:$Q$23").AutoFilter Field:=2, Criteria1:="=*" & my_value & "*", Operator:=xlAnd  'filter the column for *the value stored in my_value*


    I just removed the .select line and referenced the sheet & range in the next line. The same goes for every other select in your code. You either dont need them at all, or you just need to change your references in the following lines like I have done above.


    If you are not sure, remove each line that has a ".select" one by one and step through your code by pressing F8 and see the effect each line of code has on your sheet. Chances are you wont need any .select at all.


    Hope this helps.


    Ger

    _______________________________________________
    There are 10 types of people in the world. Those that understand Binary and those that dont. :P


    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25... ;)

    _______________________________________________

    Re: Copy cell to accommodate cell variable content in VBA


    Ger, sorry for not posting a thanks but I am the primary carer for my mother who has MND and it means that sometimes I don't get a lot of time.


    Anyway thanks for this it works and does 'see' what ever value is in cell F2 and then runs the report, just fine.


    However there is a secondary issue.


    Once the above macro has selected the F2 data and run the report, it then sends the reported data to a new workbook. Here the sheet tab is renamed by using the contents of G2 on the same sheet as the F2 cell. Unfortunately your suggestion doesn't use the value in G2 it just uses the "006WK20" See line 15 in my original code (4 line section starting "Range("G2").Select". I tried adding to your code by changing "range("f2")" to "range("f2:g2") - but this did not work. I feel I am within touching distance but I am staggering to the finish line. Can you help?


    With kind regards and hope
    Legin

Participate now!

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

Register Yourself Login