Posts by Royzer

    Hi. We have many files that are used on a monthly basis and at the end of the month the user performs a SAVE AS to set up the next month's file. Normally this means manually changing the dates on each tab to match the new month in the mm-dd format. The code below will skip the first sheet of each file (the Summary sheet) and rename the next 31 tabs. This does exactly what I need it to do except one thing: I'd like to use an input box for the user to specify the month number and have that number used in the formula where the number 9 is at the moment. Any help would be greatly appreciated. Thanks!


    Code
    Sub renametabs()
    
    
    For i = 1 To 31: Worksheets(i + 1).Name = "9-" & i: Next
    
    
    End Sub

    Hi. I have the following code to rename worksheet tabs each month, but the first sheet in every file will always be SUMMARY, with the following sheets numbered "month-day" (9-1 for example), so I need this code to skip the first tab and begin with on the second tab with (in this example) "9-1". Is there a way I can do this? Thanks!




    Re: Can macro run automatically in another file from Personal when other file opens?


    Quote from royUK;677829

    Not without code in the open event of that workbook that would run the code from your personal workbook.



    Thanks Roy. To be clear: there is no way to run any code for a workbook without first saving some code in that workbook? You couldn't say, put some logic code in the Personal macro wb that said if the other open workbook was xxxx.xls run this macro in xxxx.xls?

    My company has files that are already in use. I don't know too many details about how they work, but somehow saving the file will screw it up and my boss has to go back and reset something or other to correct it. Obviously it's connected to some other software somewhere. The code below will block Save and Save-As. BUT how do I get the file to hold onto the code without actually saving the file after the code is added (since the file shouldn't be saved)?


    Re: Loop to find string, copy down until it sees the next (diff) string from list


    Is there some way I can mark this thread "SOLVED"?



    Quote from Royzer;674938

    I could really use some help with this. I have revenue reports for around 90 stations and each report will have a variable number of accounts (rows). I need a loop that will identify the station's call letters and copy them down until it sees the call letters of the next station on the list, then switch to filling with that new stations's name. I don't have to use a station list for reference, but it's the only way I can figure out for the loop to know when to change the fill string to the new station. I've attached a workbook that shows what I'm trying to do. Thanks! forum.ozgrid.com/index.php?attachment/55062/

    Re: Loop to find string, copy down until it sees the next (diff) string from list


    Nilem, I don't know if anyone has told you lately, but you are AWESOME. That's exactly what I needed. Can't thank you enough!





    Quote from nilem;674944

    Hi Royzer,
    try it

    I could really use some help with this. I have revenue reports for around 90 stations and each report will have a variable number of accounts (rows). I need a loop that will identify the station's call letters and copy them down until it sees the call letters of the next station on the list, then switch to filling with that new stations's name. I don't have to use a station list for reference, but it's the only way I can figure out for the loop to know when to change the fill string to the new station. I've attached a workbook that shows what I'm trying to do. Thanks! forum.ozgrid.com/index.php?attachment/55062/

    I have two columns with auto mileage numbers. Each has a different set of numbers. We have not been sorting on column G. I've been sorting column H highest mileage to lowest.
    This works perfectly, for example:


    Column H after sort


    Mileage
    250,000
    200,000
    185,000
    101,000


    But if I change the letter "h" to "g" in the code the sort puts a particular number in the top cell, the same number every time. The rest of the numbers under it appear to have sorted properly.

    Column G
    after sort


    Mileage
    101,000
    250,000
    200,000
    185,000


    Code that works:

    Code
    Selection.Sort Key1:=Range("h5"), Order1:=xlDescending, Header:=xlGuess, _
            OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
            DataOption1:=xlSortTextAsNumbers


    Code that doesn't work:

    Code
    Selection.Sort Key1:=Range("g5"), Order1:=xlDescending, Header:=xlGuess, _
            OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
            DataOption1:=xlSortTextAsNumbers


    I've tried changing the formatting and checked to make sure the number that keeps coming up at the top doesn't have a single quote , but I am really stumped. I'd appreciate any help you can give me. I need to wrap this project up as soon as I can. Thanks!

    Re: Macro to find substring, insert row, move text from adjacent cell to new row


    Thank you, Oracle2057. This code is doing exactly what I needed it to do, except it is stopping short of the last section or so when I use multiple stations. I've tried different numbers of stations (like my data will be), but for some reason it stops short. There is an example in the file you sent me (attached, but renamed.)



    forum.ozgrid.com/index.php?attachment/54392/


    Thanks again!

    I could really use some help. I need a macro that will look for the word "Station" at the beginning of each cell in column A and insert a row beneath it when it is found, then move the Account Type text from the adjacent cell in column B to the new row, in column A.


    The report will vary in length each month, so the macro needs to find the bottom of the data each time to make sure nothing is missed


    I've attached a file forum.ozgrid.com/index.php?attachment/54380/ with before and after examples. Thanks so much!

    Hi. This code deletes rows from four sheets before moving on to something else. BUT when I add the code to a Command Button (2003 and 2007) the "Rows("2:600").Select" lines give a Runtime error 1004: Select Method of Range Class Failed error message. Any help would be GREATLY appreciated. :confused: