Posts by mjschukas

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

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Application.ScreenUpdating = False
Application.DisplayAlerts = False


Dim rng As Excel.Range
    Const conCOL_I = 9      'Column I
    
    Set rng = Range(Target.Address)
    
    If rng.Column = conCOL_I And Val(rng.Value) > 0 Then
    'etc.

thank you.

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

thank you.

well, i'm still curious why these won't open and other do...but

here's what i read when i open from SharePoint: "Server Read-Only This workbook was opened from a servr in read-only
mode. Edit Workbook"

can i use VBA to make this SharePoint site a "Trusted Location" ?

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:

Set pt = ActiveCell.PivotTable
Set rngPTa = pt.PageRange


    Set rngPT = pt.TableRange1
    lRowTop = rngPT.Rows(1).Row
    lRowsPT = rngPT.Rows.count
    Set ws = Worksheets("abc")
    Set rngCopy = rngPT.Resize(lRowsPT - 1)
    Set rngCopy2 = rngPT.Rows(lRowsPT)
    
    rngCopy.Copy Destination:=ws.Cells(lRowTop, 11)
    rngCopy2.Copy Destination:=ws.Cells(lRowTop + lRowsPT - 1, 11)

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.CurrentRegion.Select
        Selection.Copy
        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

thank you again...

one more question, what do you think about using: Set sh = ActiveSheet vs naming the sheet explicitly
(e.g., Set sh = Worksheets("sheet2").UsedRange)...?

thank you.

Re: only copy certain columns and rows from filtered range

works like a charm....thank you!

here's my code:

Dim st As Long
    Dim sh As Worksheet
    Dim lr As Long
    Dim str As String
     
    Set sh = ActiveSheet                                                    'copy filter range minus top two rows (header and first record)
        lr = sh.Range("A" & Rows.Count).End(xlUp).Row
            sh.AutoFilterMode = False
            Range("A1", Range("A" & Rows.Count).End(xlUp)).AutoFilter 1, "=" & str
            Range("A2", Cells(Rows.Count, "A").End(xlUp)).SpecialCells(12).Cells(2, 1).Select
            st = Range("F2", Range("F" & Rows.Count).End(xlUp)).SpecialCells(12).Cells(2, 1).Row
        Range("F" & st & ":J" & lr).Name = "abc"  'and then i paste later

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
    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.

Re: change the Style for a certain number of lines in a Word doc from Excel VBA (2010

thank you...

i'll give it a try...

and would you have an example/www for me to review the concept?

thanks.

Re: update pivot table (excel 2010)

thank you. (and updating code...)

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").ClearAllFilters
Worksheets("Calc").PivotTables("PT").PivotFields("Model").CurrentPage = rca
'...    
        Worksheets("Calc").PivotTables("PT").RefreshTable
        Worksheets("Calc").PivotTables("PT").Update
        Worksheets("Calc").Calculate

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)
myRange.Select
    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")
   WordApp.Documents.Add
   With WordApp.Selection
    .typeParagraph
    .typetext Text:=text2Prtint
    .WholeStory
    .Style = ("Normal")
    '????

???

thank you.

Re: change column General format yyyy/mm/dd to date mm/dd/yyyy

thank you!

works great...(and no formula needed)

i recieve a column extract as yyyy/mm/dd (general format)

I would like to change this to a date fromat of mm/dd/yyyy (with the corresponding display)...?

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.

Re: open Excel from Outlook-upon close, Excel still listed in Processes tab-Task Mana

thank you...

?could you explain further (unqualified object references)...?

?can i do this before the user closes Excel (Set xlApp = Nothing)?

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)...?

'take counts of Orders (in textboxes) and copy to predetermined xls
'open excel from outlook, take counts from public folders,
'leave Excel open for user to work in (user closes Excel and goes back to 
'Outlook, etc.


    Dim xlApp As Object
    Dim sourceWB As Workbook
    Dim sourceSH As Worksheet


    Set xlApp = CreateObject("Excel.Application")


    With xlApp
        .Visible = True
        .EnableEvents = False
    End With


    strFile = "C:\temp\ex_with_Outlook.xlsx"


    Set sourceWB = Workbooks.Open(strFile, , False, , , , , , , True)
    Set sourceSH = sourceWB.Worksheets("OF")
    sourceWB.Activate
    Range("a1").Select
    Range("A65536").End(xlUp).Select
    ActiveCell.Offset(1, 0).Select
    
    ActiveCell = Now()
    ActiveCell.Offset(0, 1).Value = txtInboxTodaysDate
    ActiveCell.Offset(0, 2).Value = txtIDeletedToday
    ActiveCell.Offset(0, 3).Value = txtIDeletedYesterday
    'keep workbook open for user to peruse, etc.

???

thank you...

Re: .find method on a fitlered range

ah...

interesting...i will try tomorrow...

thank you.