VBA to copy heading when new sheet is created via userform


New member
Jan 29, 2013
Reaction score
Here is what I have so far:

-- I created a userform (truck hauling data) that uses the data entered to either create a new job sheet if there isn't one yet or go to a job sheet that has already been created. The data also goes to a master sheet which then populates a pivot table. It is all working great at the moment except for a couple of minor details that I would like to fix.

What I am looking for:

-- The title block that I put on "sheet 1" to appear on the new sheets when a job is created. I cannot seem to get it to go into the macros right without messing everything up.

-- I changed the format of my user form (fonts, sizes, added a picture) and now it works but an error message pops up wanting to debug. (ctrl.Value = "") in the cmdSubmit_Click() sections of code at the bottom.

-- I would also like to compile a list of ferrous vs nonferrous materials into a new sheet with the title block that I listed into sheet 2. Is there someway based on the product name to link it and the data that I need on those title pages into a seperate sheet. In macros is there a way to list all the possible data (product) entries so that if one of the name pops up it will go to a predetermined sheet (either the ferrous sheet or non-ferrous sheet based on the name) and take the select data with it?

--I would like to change the name of the "Main" sheet to "Master List" and the name "PT" to "Scrap Report". I tried messing with it but couldn't get it without messing everything up.

View attachment Example (2).xlsm

As you all can probably tell, I am not good at this. I need your all's help! Thank you for helping!