Posts by palchy

    Hi there,

    I have one year of Dates between B2:OF2

    I have one year of Data between B6:OF6

    In one cell I would like to take the average of data from months between November 1 - March 31.

    In another cell I would like to take the average of data from months between April 1 - Oct 31.

    The spreadsheet will continuously have one year of data so right now the spreadsheet has April 1, 2020 to April 30, 2021. Next month I'll drop April 2020 and add May 2021. So I'm hoping to do this without having to adjust the formula going forward.

    Thanks for your help.

    Hi Roy,

    That code worked when I manually ran it....also worked with the time.value function.

    Last question....will the macro continue to work when I save a new copy of the worksheet. New month requires a new sheet.

    Thanks again for your help.

    Hi there,

    I have a macro that works as it should when I manually run it. I've tried using Application.Ontime to have it run at 3pm but so far that hasn't worked.



    When I use the following code it works instantaneous but two things. I need to run the code between P76:P500 (not just the P column), then I need to toggle back to unhide the zeros by running the same code. Is that possible?


    Hi there,

    What I am attempting to do is create one VBA script attached to a button that will toggle to either hide or unhide cells based on it containing zero. I have created the simply script below that works but it takes to long to run as it goes line by line which you can see happening right before your eyes.

    Sub ToggleHideRows()
        Dim c As Range
        For Each c In Range("P76:P500")
            If Not IsEmpty(c) And c.Value = 0 Then
                c.EntireRow.Hidden = Not c.EntireRow.Hidden
            End If
    End Sub

    To try and speed things up I pieced together the following script but I could only get it to hide ALL the zeros in column "P" instead of a selected portion such as P76:P500. Then it would need to have code to toggle back and forth between hiding and unhiding zeros which I couldn't figure out.

    Thank You and look forward to your response.


    All I'm attempting to do, is take files from a folder and attach them to an email, then delete those files from the folder. The code that I have pieced together will attach the file(s) to outlook with the mailing address, subject heading and my company signature but it doesn't delete the file(s) from the folder. At the moment I don't want it to automatically send as I would like to check it over first but in the future I would like to switch to automatically send.

    I'm hoping that I'm added the code tags to this post correctly.

    Thanks in advance for your help.

    Re: Attach Excel File with Links and Formula's to Email using VBA

    Here it is....just need this code to paste values and not the formulas.

    Re: Attach Excel File with Links and Formula's to Email using VBA

    Sorry, hope this is what you're looking for.....

    Attempting to take a tab out of a current excel spreadsheet and attach it to email and send it. I have copied and pasted some VBA together and it works. The issue I'm having is the tab contains external links and formula's and would need values only. Currently its attaching and sending the tab as is so the recipient is receiving the external formulas and not the Values.

    Sub SendWorkSheet()

    Dim xFile As String
    Dim xFormat As Long
    Dim Wb As Workbook
    Dim Wb2 As Workbook
    Dim FilePath As String
    Dim FileName As String
    Dim OutlookApp As Object
    Dim OutlookMail As Object
    On Error Resume Next
    Application.ScreenUpdating = False
    Set Wb = Application.ActiveWorkbook
    Set Wb2 = Application.ActiveWorkbook
    Select Case Wb.FileFormat
    Case xlOpenXMLWorkbook:
    xFile = ".xlsx"
    xFormat = xlOpenXMLWorkbook
    Case xlOpenXMLWorkbookMacroEnabled:
    If Wb2.HasVBProject Then
    xFile = ".xlsm"
    xFormat = xlOpenXMLWorkbookMacroEnabled
    xFile = ".xlsx"
    xFormat = xlOpenXMLWorkbook
    End If
    Case Excel8:
    xFile = ".xls"
    xFormat = Excel8
    Case xlExcel12:
    xFile = ".xlsb"
    xFormat = xlExcel12
    End Select
    FilePath = Environ$("temp") & ""
    FileName = Wb.Name & Format(Now, "dd-mmm-yy")
    Set OutlookApp = CreateObject("Outlook.Application")
    Set OutlookMail = OutlookApp.CreateItem(0)
    Wb2.SaveAs FilePath & FileName & xFile, FileFormat:=xFormat
    With OutlookMail
    .To = "[email protected]"
    .CC = ""
    .BCC = ""
    .Subject = "Read Me"
    .Body = "Please check and read this document."
    .Attachments.Add Wb2.FullName
    End With
    Kill FilePath & FileName & xFile
    Set OutlookMail = Nothing
    Set OutlookApp = Nothing
    Application.ScreenUpdating = True
    End Sub

    Thanks in advance for any help.