I am confused about a macro I have built.

[Marq]

using 365

I have a work book that I use to track progress at work. I have a macro built in it where I populate about 10 cells on one sheet, then click a macro button and the data I populated in the 10 cells automatically populates my headers (left, center and right).

So I will open up the workbook which is titled "Blank Progress"...I will load the data in the cells, click the button anticipating the headers populate and then id save it as the new jobs name. (I automated my headers because I have to put the same headers on multiple sheets and got tired of manually doing it)

Last week I edited the macro in VB to include some new header info I need to start seeing and ever since then when I click the button, another workbook that's already populated for a job opens up and only then will the automated header button work in the Blank Progress.

ALSO....when I open the blank progress work book and go to view the macro, it isn't showing up..i got to View/Macros/ and its chosen on "This Work Book" and its blank...there are no macros to view.

The only time I can see the macro in the View/Macros pop up window is if that other work book that somehow opens up whenever I click the button on the Blank progress work book.

I hope I did not confuse anyone with my description but I could use some help figuring out how to fix this.

 

[Marq]

I have figured out what the issue is (I think)...its a matter of space. I am limited to the amount of characters I can put in a line for a header.

So if you look at the attached pic, you will see my permanent line items, that are on every left header.

The cells I populate then run the macro then feed to each individual line...so for example I write out only a few lines of the left header as to what it will look like once macro is ran.

Before marcro is ran its:

OPS-PO
Enclose/PC:
RemoveINS:
InstallINS:

AFTER the macro is ran its:

OPS-PO
Enclose/PC: 0010-00100
RemoveINS: 0020-00200
InstallINS: 0030-00300

Well, I have 7 lines in that left header (as shown in the pic) and being I already have some permanent text in the header, it means im limited to what I can add AFTER the colons. I can remove the space from between the : and the fist digit, but that still wont free me up enough real-estate to fill in the rest of the XXXX-XXXXX for all seven lines.

So now the issue turns into "how can I get all lines to give me enough space to have the XXXX-XXXXX next to it.".

Would a smaller font work??? I have abbreviated as much as I can for the permanent text so shortening the words wont help.

so the code is good..i just don't have enough left header space. Putting it on the right header or center is out of the question....I MAY be able to move one to the center and still achieve what I want. let me try that...I will report back.

 

[Marq]

ok..here ya go...I was able to move the ASBESTOS? permanent line to the center header, which then freed up left header character space.

I got the left header to accept all of the XXXX-XXXXX's

But NOW the center header is limited to character space.

So apparently if you run a VB code for an excel header you are limited to the amount of characters you can use overall in all 3 sections. Freeing up space in one part of the header removes space from another header section.

Using a footer is not an option..everything needs to be at the top of the sheet.

Figuring out how to pull this off is a tough one.

 

[Marq]

I figured out how to shorten things to give me room. I made Insulation progress into INS Progress.

I also remove the day of the week code in the right header.

Doing those two things freed up the exact amount of characters I needed for all 3 header sections.