Posts by jhabey

    Re: Rewrite VBA Code Formatting a Worksheet


    Thank you and kudos for this solution, I updated the code in about and hour tested it and put into production in about 2 and a half hours. The solution is so streamlined and solved the problem precisely

    Hello,
    I am looking to clean up or make more efficient code that works fine but I know could be designed better.
    What I have:


    • 20 files in one folder. Each File is named for a specific Health Care Company
    • I needed to format each file into a specific format so I developed the attached code
    • So I what I did was just repeat the code but change the name of Health Care Company. This has created a really big macro



    What I am looking to do is the same thing that is currently being done but remove all of the places where I have hard coded and replaces it with variables and loop through each of the files in the folder

    Things to know:

    • Each file has a worksheet that is the named as follows: Name of the Company_Summary


    It is also the first of only two worksheets in the file


    • In the code where it says ActiveCell.FormulaR1C1 = "Healthy Montana" -->This needs to be whatever the name of the file is
    • No need to worry about what is in Call MakeReport or Call SetPrint


    I am running the below code that does some things to the header of each file in the folder. It is going though fine and processing each file and then all of a sudden I get a Data Link Properties message popping up

    Re: VBA Code to Insert PAge Breaks


    Hmmm, that is interesting. I just tested it with the code you provided and sure enough it is working to the way I intended. I wanted to get this code down as it looks more professional when it did not cut off in the middle of the Clients Data. Anyway thanks for providing feedback

    The attached file is an example of a monthly report I produce.
    - Please Select Page Break View and notice the Group Name Column and notice where the page break falls.
    - I want the code to keep the client names together. (The name of the client falls under the Group Name). So for example on page 1 you would have all of Ale House, Acfa, AHCI, CHS, BENE H, and BCare. The page break should fall under the Total in Col E.
    - page 2 you would have, Bfuel, C Inst, CFDIS, CM and CO,
    and so on.


    What I have so far:



    What the code is doing: is it works for the 1st page, but nothing after that.

    Hi,
    I have the following code which just highlights all the data in a worksheet. I want to take that data that is highlighted and use it to define a name such as CurrentMTH, I have a few things none of which are working. Any thoughts?

    Re: VBA to Open Excel File


    Very clear. Wow!
    I was wondering though how I could save the newly created files to the following folder:
    R:\Reports\Daily Reports\DailyData

    I am trying to writing code that will perform the following:
    1) Open an excel file located in this directory: C:\Users\jhaberme\Desktop\DailyFiles
    2) Insert a Colum in Column A
    2) Name the Column Group
    3) Fill in the column beginning at A2 with the Sheet Name
    4) Save and close the File as SheetName_ExportedData_Yestrday'sDate as YYYYMMDD


    What I want to happen is if a files exists in the folder Open it and do steps 1 -4, as I could have 30 - 40 files in the folder. Also with step 3 filling in the column with the sheet name, I need to fill in every row that as data in the column next to it (Col b which is called Employee SSN) So what this means is that the number of rows in which I have data varies.


    Thanks,
    John

    Hello,
    I would like to add vba code to place the current month and year in cell A2. So if I am run the vba today March 3, 2015 the cell value will be: MARCH 2015
    If I run the vba code on April 9, 2015 it will be : APRIL 2015, and so on


    I think it is somthing like this but this takes what ever month I am in and subtracts one month.

    Code
    Range("A2").Value = DateSerial(Year(DateAdd("m", -1, Now)), Month(DateAdd("m", -1, Now)), 1)
        Range("A2").Select
        Selection.NumberFormat = "[$-409]mmmm d, yyyy;@"