Posts by Royzer

    Hi.


    My workbook has over 30 sheets. Whenever the printer icon is pressed I need the active worksheet to hide columns L and K, print the sheet, then unhide the columns. This code worked perfectly last week but now it doesn't and I can't think of anything that's changed. I would greatly appreciate any help you could give me. Thank you.


    Code
    Private Sub Before_Print()
    
    
    Range("K:K,L:L").EntireColumn.Hidden = True
    ActiveSheet.PrintOut
    Range("K:K,L:L").EntireColumn.Hidden = False
    End Sub

    Hi. I am using the code below to insert a row by double-clicking a cell. The code then copies formulas (and apparently dates) from the original row to the new row. Is there any way for me to adjust this code so the cell in column A is blank after the insert? If so, I need it to work like this for all 30+ pages of the workbook. Here's the code I have in ThisWorkbook:




    Thank you.

    Hi. I have 32 sheets in a workbook. The only way I've been able to use the code below is to put it on a single worksheet. It runs perfectly on a particular sheet when it's associated with that sheet. I tried putting it as a module on the file and also in ThisWorksheet, but I can't get it to work for every sheet. Is there a way to get this to run for every sheet in the file without copying it to each sheet?




    Thanks!

    Hi. I have a worksheet with around 25 rows that will already have a formula in them that adds today's increase or decrease to the previous day's balance.


    Column A is for daily entries (values)
    Column B is the running total(formulas)


    For example,
    B1 would be the beginning balance for the month (500, a value).

    A2
    would be the first day's amount: 250.


    B2 would be =B1+A2 (500+250=750)

    A3
    would be following day's amount: -100

    B3
    would be =B2+A3 (750-100=650)


    A4 would be the following day's amount: 50

    B4
    would be =B3+A4 (650+50=700)



    When a cell is double-clicked, the code below inserts a new row and copies the formula down to the new row. If I insert a row at Row 2, the inserted row becomes row 3 and the formula is fine. BUT the formula in row 4 (which was row 3 before the insert) does not adjust the first cell reference in the formula the way it is supposed to. Example:


    Row 2 =B1+A2


    Inserted row, Row 3 =B2+A3

    But the row that moved down(formerly row 3) Row 4, =B2+A4 instead ofB3+A4


    Is there any way to make this work properly? Thanks! [Blocked Image: http://www.mrexcel.com/forum/images/smilies/icon_smile.gif]




    I have the following code to hide two columns only when printing the active sheet. Is there any way to tie this command to the Excel printer icon so it performs this action without having to use a command button on the sheet? I've got 32 sheets in a workbook that will need to be printed like this at random times by various users. I'd like to avoid putting 32 command buttons in the file if I can. Thanks!





    Code
    Sub Before_Print()
    
    
    Range("K:K,L:L").EntireColumn.Hidden = True
    ActiveSheet.PrintOut
    Range("K:K,L:L").EntireColumn.Hidden = False
    
    
    End Sub

    Re: Freeze panes Shared workbook


    This has been resolved. This macro runs on wb open and sets all frames at that point. Once the shared workbook is open and the frames set, subsequent users' sheets have frames when they enter the wb.


    Hi.


    We have a shared workbook (version 2003) on a shared drive that sometimes has up to 10 people accessing it at the same time. I have set the frames on all the worksheets to B9 and saved the file. The first person to open the file has frames. Subsequent (simultaneous) users do not have frames. It appears that this occurs every time the file is accessed by multiple users at once--whomever opens the file first gets the frames and no one else does. Is there any way to change this so everyone who is in the file at the same time has frames on the worksheets they are working in? Here is the code I used to set the frames:


    Code
    Sub freezeframesatB9()
    Dim vWks As Variant
    
    
        For Each vWks In Array("KAKE", "KBTX", "KKCO", "KKTV", "KOLN", "KOLO", "KWTX", "KXII", "WBKO", "WCAV", "WCTV", "WEAU", "WHSV", "WIBW", "WIFR", "WILX", "WITN", "WJHG", "WKYT", "WMTV", "WNDU", "WOWT", "WRDW", "WSAW", "WSAZ", "WSWG", "WTAP", "WTOK", "WTVY", "WVLT", "WYMT", "TV3")
            Application.Goto Worksheets(vWks).Range("B9")
            ActiveWindow.FreezePanes = False
            ActiveWindow.FreezePanes = True
        Next vWks
    End Sub


    Thanks!

    Re: VBA-Find/Replace two strings--worked yesterday but not today


    Thank you, nmhung49. Just before I saw your post I found out that the code was only looking at the first sheet. This fixed it:


    Hi.


    I have a workbook with 40 sheets. I need to run vba code to perform a find and replace across five specific sheets for a couple of different strings. The code worked perfectly yesterday, but now it doesn't do the Find/Replace, it just jumps the active cell to B25 (where it should be after replacing everything). The command button is located in the first sheet, named "NOP". The next five sheets need the find and replace. Thanks!


    The code below works perfectly for what I need to do, which is to open and copy (or move) the first sheet of each of the workbooks in a single folder to a master WB that resides outside of that folder, then break the links to the source workbooks. However, every time I run the code, twenty-seven of the workbooks open and close before the next one opens (as they should), but the same five workbooks stay open after the code completes. The first sheet of each of these moves to the master WB like all the others, but they do not close after the move. I can't see any obvious difference between the WBs that stay open and the ones that close. Could someone please point me in the general direction of where the problem lies? I am using Excel 2007 but this needs to also run on 2003. Thanks! (All my codes are piecemeal, as I am a beginner who finds code on online and adapts it to what I need to do, so that's why it looks like it does.):)


    Here's the code:


    Re: Search folder and copy sheets from multiple files into a master file with formatt


    I found some code that works exactly like I described in my OP. Here it is:




    Re: Search folder and copy sheets from multiple files into a master file with formatt


    Thank you, Ger Plante. Over the past couple of days I have reviewed all the threads I could find related to copying sheets from multiple workbooks into a master file (including those below), but it appears that the sticking point is my need for the combination of file search, pasting the copied sheet with formatting intact, and giving the new sheet the name of the source sheet.

    I have found code on the site that is similar to what I need, but have been unable to adapt any of it to make it work. Please help me find a way to:


    (1) Search a single folder for .xls files


    (2) Copy the first sheet of each file (there is only one sheet in each file) and paste them into individual sheets in a master workbook with the original formatting and sheet name on the tabs. They do not have to be pasted as values.


    The closest thing I have found (that works for me) is the very basic code below, but this requires all of the workbooks to be open and the names of sheets and workbooks within the code. I'm working with 32 workbooks, so I had hoped I could find a way to automate the whole process. Finally, it needs to be compatible with Excel 2003. Thank you. :smile:



    I have found code on the site that is similar to what I need, but have been unable to adapt any of it to make it work. Please help me find a way to:


    (1) Search a single folder for .xls files


    (2) Copy the first sheet of each file (there is only one sheet in each file) and paste them into individual sheets in a master workbook with the original formatting and sheet name on the tabs. They do not have to be pasted as values.


    The closest thing I have found (that works for me) is the very basic code below, but this requires all of the workbooks to be open and the names of sheets and workbooks within the code. I'm working with 32 workbooks, so I had hoped I could find a way to automate the whole process. Finally, it needs to be compatible with Excel 2003. Thank you.



    I have found code on the site that is similar to what I need, but have been unable to adapt any of it to make it work. Please help me find a way to:


    (1) Search a single folder for .xls files


    (2) Copy the first sheet of each file (there is only one sheet in each file) and paste them into individual sheets in a master workbook with the original formatting and sheet name on the tabs. They do not have to be pasted as values.


    The closest thing I have found (that works for me) is the very basic code below, but this requires all of the workbooks to be open and the names of sheets and workbooks within the code. I'm working with 32 workbooks, so I had hoped I could find a way to automate the whole process. Finally, it needs to be compatible with Excel 2003. Thank you.