Add column headers to newly created worksheet and add formatting to all columns

**moosetales** · 01-16-2016, 10:44 AM

Good morning. I have a master workbook that contains two columns with headers; client names (column A) and the number of approved miles (column B). I need to copy those two columns and paste them automatically in a new excel workbook. The code I currently have in my master workbook is as follows:

Please Login or Register  to view this content.

The code works great for copying from my master and pasting into a newly created workbook. Now I can't figure out how to do the following in the newly created workbook:

add two new column headers in column C (mileage on time card) & D (overage)
automatically adjust the column width to fit the content
add the following function to column D =SUM(B2-C2) and only show the value if it's a negative number

Here is a sample of the workbook I'm creating. Thanks in advance for feedback.

Mileage Approval Dummy Worksheet.xlsm

**JBeaucaire** · 01-16-2016, 12:00 PM

1. add two new column headers in column C (mileage on time card) & D (overage)

Please Login or Register  to view this content.

2. automatically adjust the column width to fit the content

Please Login or Register  to view this content.

3. add the following function to column D =SUM(B2-C2) and only show the value if it's a negative number

Please Login or Register  to view this content.

**moosetales** · 01-16-2016, 02:01 PM

JBeaucaire ,

Brilliant. Here's how I put the additional code into the existing code.

Please Login or Register  to view this content.

It does everything I need with the exception of the formula. When I enter a value in column C no value populates in column D. Here's the workbook with the above code. Thanks.

Mileage Approval Dummy Worksheet.xlsm

**JBeaucaire** · 01-17-2016, 04:24 AM

So maybe that code should be putting that formula in column D instead of column C.

**moosetales** · 01-17-2016, 11:15 AM

JBeaucaire,

Yes, of course. Changed code to reflect the desired column and it worked like a charm. The following is the code as it stands currently.

Please Login or Register  to view this content.

I'm still coming up short on how to adjust my code to adjust what is copied and pasted from the original workbook to the newly created workbook. This is what I'm trying to accomplish:

From the Original Workbook to the New Workbook

Column A-Copy and Paste ALL
Column B-Copy and Paste ALL
Column C-Copy and Paste ALL
Column D-Copy borders & cell background color only and Paste borders & cell background color only
Column E-Copy borders & cell background color only and Paste borders & cell background color only
Column F-Copy borders & cell background color only and Paste borders & cell background color only
Column G-Copy borders & cell background color only and Paste borders & cell background color only

Thanks again for any feedback you may be able/willing to offer.

Here's my Dummy workbook with the above code at work. Thanks.

Mileage Approval Spreadsheet DUMMY.xlsm

Matthew

**JBeaucaire** · 01-17-2016, 02:13 PM

This is how I would do that with your code:

Please Login or Register  to view this content.

**moosetales** · 01-17-2016, 02:23 PM

Thank you kindly. With your suggested code the first run worked perfectly. I'm going to run it through its paces and report back in a bit. Thanks again.

Matthew

**JBeaucaire** · 01-17-2016, 02:30 PM

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

**moosetales** · 01-17-2016, 02:46 PM

JBeaucaire,

After trialing the workbook with new code I decided to make columns A:G AutoFit (see code below). Everything copies over to the new workbook perfectly but when I type in the columns of the new workbook the columns do not re-size to fit the newly entered text. Did I adjust the code incorrectly? Thanks.

Please Login or Register  to view this content.

**JBeaucaire** · 01-18-2016, 03:18 AM

Typing in the columns after the macro has run does not retrigger the autofit, it only autofits based on the content of the columns at the time the macro runs.

**moosetales** · 01-18-2016, 07:21 AM

That makes sense. Thanks for all your help.

Matthew

Add column headers to newly created worksheet and add formatting to all columns

LinkBack

Thread Tools

Rate This Thread

Display

Add column headers to newly created worksheet and add formatting to all columns

Re: Add column headers to newly created worksheet and add formatting to all columns

Re: Add column headers to newly created worksheet and add formatting to all columns

Re: Add column headers to newly created worksheet and add formatting to all columns

Re: Add column headers to newly created worksheet and add formatting to all columns

Re: Add column headers to newly created worksheet and add formatting to all columns

Re: Add column headers to newly created worksheet and add formatting to all columns

Re: Add column headers to newly created worksheet and add formatting to all columns

Re: Add column headers to newly created worksheet and add formatting to all columns

Re: Add column headers to newly created worksheet and add formatting to all columns

Thread Information

Users Browsing this Thread

Similar Threads

Copy columns with specified names to a newly created Sheet.

macro to add conditional formatting to a newly created sheet

macro code to activate the newly created worksheet in excel.

Linking newly created worksheet names to cells?

[SOLVED] Use Current Worksheet Name in Formula to Rename Newly Created Worksheet

Updating data on newly created worksheet

Referencing a newly created worksheet

Bookmarks

Bookmarks

Posting Permissions