Posts by JimFuller1

    Re: automatically filling in data from another place?


    Just select (click on) the cell with the formula in it. Position your cursor on the "SHEET1!A3:B45" part of the formula between the A and the 3 and hit your [F4] key on the keyboard. When the formula looks like this $A$3, move your cursor between the B and the 45 and do the same. When you are done, hit enter and you should have the following "SHEET1!$A$3:$B$45".


    What that means is you now have an absolute reference in the formula. When you copy it down, it will not change.

    Re: chart copying without link update


    sum06,


    If I understand you correctly, there is only one range of data. The reason all the charts reflect what is in that range is because they have to have data in order to be charts.


    The solution may lie in creating different ranges for the data.


    Jim

    Re: Grouping Worksheets


    Hi James,


    What if you put some code in the "This Workbook" section of the VBA editor that executed when the worksheet was selected? You could just unhide your list of worksheets and hide all the others.


    Jim

    Re: Using VLookup in VBA


    I used DIM ok. I added SET and it crashes. The msg box returns the following:


    Workbooks(sTrafficFileName).Sheets(1).Range("$H$3:$H$1009")


    Thanks,
    Jim

    Re: Using VLookup in VBA


    Quote from Seti

    Are you sure that there are matches? Is the invoice number a number in one sheet and text in another? You can use a blank cell and type =ISTEXT(cell reference) and see if it is FALSE for the same value on both sheets. Do you have trailing or leading spaces in one of the invoice numbers? If you find a match, lets say that Sheet1!H3 should match Sheet2!H50, then =Sheet1!H3=Sheet2!H50 should return TRUE. Does it?


    The ISTEXT test returns false on both sheets. The second test returns true for the matching items. There are no trailing or leading spaces.


    Is it possible that the lookup range is in the wrong format in the formula?


    Thanks,
    Jim

    I'm getting a type mismatch when I run the code below. Is it the way I'm passing the lookup address?




    Thanks,
    Jim

    Re: Workbook Save, Make sure active cell is A1


    It apparently takes two lines.



    Code
    ThisWorkbook.Sheets(1).Select
    ThisWorkbook.Sheets(1).Range("A1").Select


    Scrolling is a different matter. Selecting a particular cell will not mean it's visible when you click on that tab next time. Maybe someone else has that code handy.

    Re: Workbook Save, Make sure active cell is A1


    Place this code in the ThisWorkbook object after you start the VBA editor.


    Code
    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
            ThisWorkbook.Sheets(1).range("A1").Select
    End Sub


    This will select cell A1 on the first sheet in the workbook.

    Re: Refer to Sheet by Secret Name


    norie,


    Thanks for the help but, I don't think the rest of the code is important. I don't just want to make the code work. I guess I'm trying to understand when I need to activate and when I don't.


    Thanks,
    Jim

    Re: Refer to Sheet by Secret Name


    Here's the code:


    Code
    Workbooks(sTrafficFileName).Activate
        sOrdersFB.Cells.Clear
        Workbooks(sBobName).Sheets("Orders").Cells.Copy sOrdersFB.Range("A1")
        sOrdersFB.Cells.Copy
        sOrdersFB.Range("A1").PasteSpecial Paste:=xlPasteValues
        Application.CutCopyMode = False
        sOrdersFB.Range("A4").Select


    It works but, when it is called from another module, do I need to activate before I clear sOrdersFB?


    Also, the last line doesn't seem to work if the macro runs while another sheet is selected.


    Thanks,
    Jim

    Re: Refer to Sheet by Secret Name


    I'm trying to combine the following two statements.


    Code
    Workbooks(sTrafficFileName).Activate 
    sOrdersFB.Cells.Clear


    Because, those statements come between parts of code that acts on other workbooks. I thought if I just use the second line for clearing while the focus is on another workbook, it would crash. No?


    Jim

    Re: Refer to Sheet by Secret Name


    Ok. How do I know that the code will know which workbook to find sOrdersFB in when I'm jumping back and forth between workbooks in other parts of the code. I thought I would have to activate the workbook to be sure I don't erase another sheet.


    Thoughts?


    Jim

    Re: Refer to Sheet by Secret Name


    sOrdersFB is the code name of the sheet in question.
    sTrafficFileName is the workbook sOrdersFB is in.


    This works:

    Code
    Workbooks(sTrafficFileName).Activate
    sOrdersFB.Cells.Clear


    This doesn't:

    Code
    Workbooks(sTrafficFileName).Sheets(sOrdersFB).Cells.Clear


    Any ideas?


    Thanks,
    Jim