Paste Special (xl Values) End With Next End Sub Sub New WBand Paste Special ALLSheets() Dim wb As Workbook Dim wb New As Workbook Dim sh As Worksheet Dim sh New As Worksheet Set wb = This Workbook Workbooks. Copy 'add new sheet into new workbook with the same name With wb New. Add Set wb New = Active Workbook For Each sh In wb. Screen Updating = False On Error Resume Next Set Auto Fit Rng = Range(Range(str01). I hope you had a safe and happy New Year's Eve celebration, and are off to a good start in 2012. To see the steps for copying the code into your worksheet, and an explanation of how the code works, you can watch this short Excel video tutorial.To fix the worksheet, so the merged cells adjust automatically, you can add event code to the worksheet.
Paste Special (xl Values) End With Next End Sub Sub Create_new_file() Application.
In the example shown below, there is an order form, and space for a note about the order.
If the note will always be short, there's no need to merge the cells – just let the text flow across the columns.
However, if the notes will be two or more lines, you'll need to merge the cells, and turn on Wrap Text.
Adjusting the column width would affect the product list that starts in row 12, so that's not an option. Screen Updating = True End If End Sub Screen updating is turned off while the code runs, and it all happens in the blink of an eye.