Posts by nightkil

    Re: Update a named range vba


    Hello Robert,


    your code worked great. thanks.


    RoyUK, the reason I had to go with VBA was because the sheet clears everything after row 18 before getting new data. I didn't make it I'm just using it to take data and imported into access.


    Thank you all.

    Hello,


    I'm trying to use a vba to update a named range on sheet2 when new rows are added. I would like the code to find the last row and define the name range called "traffic" to add the new rows.


    Thank you in advance.

    Hi,


    I try searching for this and was not able to find it...


    I'm looking to have a Button on my sheet that will open the Find box so people do not have to go to the menu to find it. Is there a code that can open this?


    Thank you for your help

    Re: Paste A Cell Value To Another Cell With Code


    Thank you both. I tried both codes and I was getting some errors so I expanded the field and used the old code and everything seems to be working fine. Since this is not a big deal I might just leave it as is for now and move on to bigger things lol


    Thank you both and Maxims thank you for explaining why I was getting the error in the first place.

    Hello everyone,


    I have this code that I have been using fine until today when it just stop working for me... It's a simple copy and paste using VBA Code. The code finds the last row in Column A and copies the cell to L1. Below you will find the code I'm using and the line in yellow is where I'm getting the error.



    This codes runs as soon as the worksheet opens and it has worked for over a year. I'm using MS Excel 2007. Please let me know what you guys think I have tried pretty much everything I can think of but i dont really know what i'm doing when it comes to VBA.


    Thank you

    Re: Copy Data From Multiple Sheets To One Master Sheet


    Lets hope this is the last error lol.


    There are some records from "SR3-BK5" sheet that are showing with the "SR2-FC1" sheet name on the Master List. There are other ones like this. I'm leaving now and I not sure when this weekend I will want to see this again but I'll keep my eye out for your response.


    Have a good Friday :)

    Re: Copy Data From Multiple Sheets To One Master Sheet


    StephenR,


    In every sheet with data in column B has to have data in the other columns. After a bit more research I notice that the code that Dave posted is only getting the data from column B.


    Is not 1 sheet thats the problem it seems to be all sheets (I worded that wrong before).


    I hope that helps.

    Re: Copy Data From Multiple Sheets To One Master Sheet


    Thank you Dave,


    After making that change the data that did transfer actually looked pretty good. But now I get a new error.


    The error is "Run-Time error '1004': Application-defined or object-defined error".


    If I debug it brings me the orange line below.




    Thank you again.


    Actually after looking at the data again I notice I'm only getting the data for column B. I still need to have all the data in B:K. I'm not sure if this is because of the error above.

    Re: Copy Data From Multiple Sheets To One Master Sheet


    I'm sorry to keep doing this to you. That works better than all the other codes but a new problem came with this one. Now for some sheets its pasting the data from column A when it should be pasting pasting column B.


    ie. Sheet 1 to 5 are done correctly (the code takes B:K and puts it correctly in the master list) but for sheet 6 column A was also pasted. I'm not even sure how its getting the data from column A since we're looking for B.

    Re: Copy Data From Multiple Sheets To One Master Sheet


    that should be correct. I mean at this point I dont think we will reach more than 50.


    To answer you other question I have attached a preview of what some of the sheets might look like. We might have data in row1, row5, row10 in one sheet. In other sheets we might have data in rows1-25. So the eisier way to do this (in my mind) was to have a code that takes the first 50 rows on each sheet but then deletes any row that has column B blank. The code you posted works but for some reason the rows are not getting deleted when column B is blank.


    I hope I did a better job at explaining it this time.


    Thank you for all your help and time.

    Re: Copy Data From Multiple Sheets To One Master Sheet


    Thats right. The reason is coded that way is because there is some empty rows in between the data in each sheet. But I want to make sure this code gets all that data and puts it in the Master List sheet. Is there a better way to do this?


    Thank you.

    Re: Delete A Form Text Box With A Macro


    That one actually selected the the right form field but I get an error in the statement below


    Code
    Selection.Delete Unit:=wdCharacter, Count:=1


    The error is "this command is not available" "Run-Time Error '4605".

    Re: Delete A Form Text Box With A Macro


    PeteNZ,


    Thanks for trying but didnt work. I try a few things my self but it deletes all the formfields.


    Code
    ActiveDocument.Unprotect
    'Selection.FormFields(text3).Delete
    'or
    Word.FormField(text3).Delete
    ActiveDocument.Protect wdAllowOnlyFormFields

    Re: Copy Data From Multiple Sheets To One Master Sheet


    StephenR


    First let me thank you for your time.


    The code worked for the problem explained but now there is another issue. With the code you posted we always have 49 entries for every sheet name in column A.If the sheet does not have 49 records then records from other sheets will be place in those rows. Does that make any sense?