Posts by cw_lynn

    I have a spreadsheet with dates listed across the top and values listed below. To the right of the data, I have a formula summing up each quarter. As I go back and add in the values, the first quarter forumla automatically updates to include all of the cells in which I entered data.


    See Example:


    Jan Feb Mar Apr May, etc. tot q1


    1 2 6 2 4 , etc. =sum(a3:c3)



    As I soon as I enter the value for Apr (2 in this case), the formula changes to =sum(a3:d3) and does so for each additional value entered. I assume that this is a setting that can be turned off. For now, I just added absolutes to the formula, but I'm curious to know how to turn off the option. Thanks for any input.


    Chris

    I have a data query in Excel that updates regularly and I would like this information to be automatically posted to the web. Is there a slick way of pointing a website to this data and have it update at the same time as the data query without using a macro? Thanks for the help.


    Chris

    I have a script in Excel that creates and sends emails (through Outlook) out to a group of individuals. I would like to be able to add attachments to these emails, but the problem I have is that I have several files to attach that have various names. All the files are in the same folder, so I need to have something that goes in and attaches all files in folder 1 to email 1 and so on. Is there a way to tell Excel to attach all files in a particular folder to an email?


    Thanks for your help and ideas!


    Chris

    I want to filter a range on all sheets in a workbook, but it's possible that additional sheets would be added later. I tried adding in an absurd amount of sheets to loop through, but I don't want the error popping up at the end. I would like either to End Sub on error or have a slick way of looping where it ends with the last sheet.


    Thanks a bunch for your help!


    Chris

    Thanks for the reply. I've been looking at this all afternoon and came up with the same answer. I ended up using a send key which is displayed below.


    Thanks again.


    Chris


    Sub SendEmail()


    Application.ScreenUpdating = False


    Dim OutlookApp As Outlook.Application
    Dim MItem As Outlook.MailItem
    Dim cell As Range
    Dim Subj As String
    Dim EmailAddr As String
    Dim Recipient As String


    Set OutlookApp = New Outlook.Application


    For Each cell In Columns("G").Cells.SpecialCells(xlCellTypeConstants)
    If cell.Value Like "*@*" Then
    Subj = "Monthly Market Survey"
    Recipient = cell.Offset(0, -1).Value
    EmailAddr = cell.Value

    Msg = "Dear " & Recipient & vbCrLf & vbCrLf
    Msg = Msg & "You have received this automated message because your Market Survey has not yet been logged in. Please submit to me at [email protected]. If you have any questions or believe you received this message in error, please contact me at (123) 456-7890." & vbCrLf & vbCrLf
    Msg = Msg & "Thanks." & vbCrLf & vbCrLf
    Msg = Msg & "Chris"



    Set MItem = OutlookApp.CreateItem(olMailItem)
    With MItem
    .To = EmailAddr
    .Subject = Subj
    .Body = Msg
    .Display
    SendKeys ("%{s}")

    End With

    newHour = Hour(Now())
    newMinute = Minute(Now())
    newSecond = Second(Now()) + 1
    waitTime = TimeSerial(newHour, newMinute, newSecond)
    Application.Wait waitTime


    End If
    Next
    End Sub

    I have a script (shown below) which allows me to send a mass mailing from Excel, but I am required to click yes in Outlook stating that I am indeed trying to send a message. The help section indicates that this is due to the use of the Item.Send function. Is there a way to turn this off or automate the Yes button? Funny thing is the button will not allow you to press Yes for 5 seconds after the msg appears. I have 200+ emails to send, so that's like 15+ minutes of pushing yes...


    Thanks for any ideas.


    Chris




    Sub SendEmail()


    Dim OutlookApp As Outlook.Application
    Dim MItem As Outlook.MailItem
    Dim cell As Range
    Dim Subj As String
    Dim EmailAddr As String
    Dim Recipient As String


    Set OutlookApp = New Outlook.Application


    For Each cell In Columns("G").Cells.SpecialCells(xlCellTypeConstants)
    If cell.Value Like "*@*" Then
    Subj = "Monthly Market Survey"
    Recipient = cell.Offset(0, -1).Value
    EmailAddr = cell.Value

    Msg = "Dear " & Recipient & vbCrLf & vbCrLf
    Msg = Msg & "You have received this automated message because your Market Survey has not yet been logged in. Please submit to me at [email protected]. If you have any questions or believe you received this message in error, please contact me at (123) 456-7890." & vbCrLf & vbCrLf
    Msg = Msg & "Thanks." & vbCrLf & vbCrLf
    Msg = Msg & "Chris"

    Set MItem = OutlookApp.CreateItem(olMailItem)
    With MItem
    .To = EmailAddr
    .Subject = Subj
    .Body = Msg
    .Send
    End With
    End If
    Next
    End Sub

    Thanks guys!


    The method I used was to change the sheet Temp (2) to a the new file name in each loop. Then, the next sheet created would be Temp (2) also.


    As for the pivot table suggestion, I needed all of these to be in seperate sheets for distribution purposes.


    Thanks again for the responses.


    This really is a terrific forum!


    Chris

    This is probably an easy answer... I have a template sheet that performs a calculation dependent upon a unit number. I want to loop through all unit numbers and create copy of the template sheet for each unit. I believe I can make everything work except for naming the newly created sheet. I don't know what generic name Excel will give it, so I need to name the active sheet. Activesheets.Name didn't work. The name will be the unit name listed in cell B2.


    Thanks for any help.


    Chris

    I have a formula in one workbook that pulls data from another workbook. I would like to be able to specify which workbook to pull from by listing the workbook name in a separate cell and linking the formula to it.


    For Example:


    Cell A1 contains the text File 1.


    Cell A2 contains the formula
    =IF('[File 1.xls]Quarterly'!$G$29>0,'[File 1.xls]Quarterly'!$G$29,0)


    Is it possible to do something like this:
    =IF('[A1]Quarterly'!$G$29>0,'[A1]Quarterly'!$G$29,0)


    Thanks for your help.


    Chris

    I have a rather large spreadsheet that has three sections.


    1. Pull data from a linked source. This is a retrieve formula to pull from Hyperion.


    2. Forecast based on data from linked source.


    3. Pull forecasted numbers to the Export section for transfer back to Hyperion.


    The issue is that everytime I make a change, I think the retrieve and export formulas are slowing down the calc process. They aren't actually retrieving or exporting unless directed, but I still think they are slowing everything else down. Is there a way to label those cells as non calc cells while I'm changing the forecast variables? The effect I'm looking for is having the 1st and 3rd set act as values only, without actually deleting the formulas and putting them back in.


    I know that I could calculate only one sheet at a time, but I'd like to keep the three sections on one sheet, as I have many pages set up the same way, just pulling other accounts.


    Ideas?


    Thanks.


    Chris

    Dear Guru's,


    I'm sure I'm not the only one who has run into this problem.... I have a script that deletes a row if a zero is found in a certain column, which works fine. However, when a row is deleted, the next row is skipped because the script thinks that it already looked at it.


    Example:


    A 0 is found in row 10, so delete row 10.
    If a 0 is also in row 11, it is not deleted because upon deletion of row 10, row 11 became row 10.


    Is there a better way to tackle this problem besides my method of stringing together several repeating scripts?


    Thanks for your help!


    Chris


    'Begin Variance Section


    For x = 7 To 104
    y = Range("o" & x)


    If y = 0 Then
    Rows(x).Select
    Selection.Delete Shift:=xlUp
    Else
    Range("a1").Select

    End If


    Next


    'End Variance Section


    'Second Variance Section


    For x = 7 To 104
    y = Range("o" & x)


    If y = 0 Then
    Rows(x).Select
    Selection.Delete Shift:=xlUp
    Else
    Range("a1").Select

    End If


    Next


    'End Second Variance Section

    Greetings all!


    I am attempting to create a code that will look through column A to find a particular phrase ( -Variance). Once found, it should select cells c1:o1 (the row number = i), copy and paste them into the new workbook in the same order as the template. I can make it select the first cell (c & i), but can't seem to make it work for the entire range (c & i : o & i). Help!


    Thanks.


    Chris the new VBA user





    Sub Variance()


    'Paste the variance formulas into the new templates


    For i = 1 To 200

    If y = " -Variance" Then
    Range("C" & i).Select
    Selection.Copy
    Windows("Book1").Activate
    Range("C" & i).Select <--- needs to be C & i : O & i
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Windows("ActionPlan v3").Activate
    Else
    Range("a1").Select
    End If
    Next

    End Sub