Posts by mjschukas

    I'm using the following (works well, but doesn't trigger when the sheet is protected):

    thank you.

    Re: running macros when "Edit Workbook" is not selected and macro does not run

    Thank you...

    i agree with your point and doubled checked, but i don't think this is the case here... I changed to:

    Sheet27.Visible = xlSheetVisible

    and it still errored out (i.e., when the "Security Warning Some actie content has been disabled. Click for more details. Enable Content" and I chose not to click and Enable. When I do Enable, the code works...)?

    thank you.

    n.b., this is the only code that doesn't work when not Enabled.

    I have a xlsm on SharePoint that the user's select and open (often they work in the template first and later save to their PC's if they decide to)..

    so the "Server Read-Only this workbook was opened from a server in read-only mode Edit Workbook" defaults at the top of the of the sheet.

    all the macros work...except one that i added that opens other sheets (for subsquent calculations)...

    Worksheets("a").Visible = True    'works
    Worksheets("b").Visible = True    'works
    Worksheets("c").Visible = True    'doesn't work (Error message - but i do see the tab name, when i stop the code (debug) and right click on a worksheets tab (right click and i can manually unhide

    i'm not sure what the cause may be (when I enable, all the macros work)...?

    thank you.

    Re: pivot table copy and paste values AND formating...

    and thanks to Debra Dalgleish, i used:

    this works if copied to anther range, but not if i try to copy, paste values and formats to itself (same range) and "break" pivot table...?

    thank you.

    Re: pivot table copy and paste values AND formating...

    thank you.

    from your example, the copy does work (to another range) and the formatting stays, but so does the pivot table.
    (i'm trying to copy the values of the pivot table (this works) and also copy the pivot table formatting (.TableStyle2 = "PivotStyleLight8") - this doesn't in my example)...


    thank you.

    in excel 2010, I'm using the following to copy and paste values and formating from a pivot table, but i lose the formatting (TableStyle2 = "PivotStyleLight8"):

            Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False

    i have tried to add, xlPasteFormats, but to no avail...?

    thank you.

    Re: only copy certain columns and rows from filtered range

    works like a charm....thank you!

    here's my code:

    thank you...

    only copy certain columns and rows from filtered range

    after i filter a range, I would like only copy the filtered range from columns F:J and only the rows after the header and the first row (i.e., not the first two)...?

    presently i'm coping the whole filtered range and the code i'm using to filter (works well):

    Set rFilterHeadsar = Range("a1", Range("a1").End(xlToLeft))
    With wSheetAR
        .AutoFilterMode = False
        rFilterHeadsar.AutoFilter Field:=1, Criteria1:="=" & strCriteriaAR
        Set toCopyRangeAR = rFilterHeadsar.Offset(1, 0).SpecialCells(xlCellTypeVisible)
    End With
    toCopyRangeAR.Copy Sheets("mm").Range("A25")  'here is where i would like to only copy in columns F:J, rows after first two


    thank you.

    i'm using a pivot table to filter data and return results (works well).

    the filtering works fine (see sample below) , but i'm not sure of the "best" way to update the table (i have found the following
    three ways)
    --and i should make sure that the underlying data source is refreshed-at the least for the first time I run the pivot table filter

    Worksheets("Calc").PivotTables("PT").PivotFields("Model").CurrentPage = rca

    thank you.

    i have done in Word VBA successfully (basically, change the Style for a certain number of lines):

    Dim myRange As Range
    Set myRange = ActiveDocument.Range(ActiveDocument.Range.Start, ActiveDocument.Range.Start)
        Selection.MoveDown Unit:=wdLine, Count:=4
        Selection.MoveDown Unit:=wdLine, Count:=2, Extend:=wdExtend
        Selection.Style = ActiveDocument.Styles("No Spacing")

    and I would like to add to the following Excel VBA (opening Word and coping out text works well):

    Set WordApp = CreateObject("word.Application")
       With WordApp.Selection
        .typetext Text:=text2Prtint
        .Style = ("Normal")


    thank you.

    Re: .find method on a fitlered range

    well (finally back to this...)

    i added (.SpecialCells(xlCellTypeVisible)): (and error out on this line "method not correct..."

    Set aCell = oshtsp.Range("ac2:ac" & LastRowsp).SpecialCells(xlCellTypeVisible).Find(What:=strSearch, LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False, After:=oshtsp.Range("ac" & LastRowsp))

    ?is the syntax correct?
    (n.b., i only want to use the .fiind method on a filtered worksheet...)

    thank you.

    i'm using the following to read public folder emails (orders) and copy to Excel (works well):

    but after the user is done with Excel (closes the App), goes back to Outlook, etc., and later in the day copies out to Excel agian (code below), Excel won't function properly (when i look in Task Manager, the Excel is not listed under the Application tab, but it is still listed in Processes)...?


    thank you...