Free Microsoft Excel 2013 Quick Reference

Copy worksheet and KEEP formatting

Hi all, can someone tell me how to copy an Excel worksheet to a different workbook worksheet, the worksheet will have multiple tabs in it already, and keep the format. When I do it, I lose all of my formatting and have to tell it to reformat. I am doing this in a DTS package/VB6. This is what I currently have:

objExcelDetail.Worksheets.Item(1).Range("A:W").Copy objExcelMaster.Worksheets.Item(strBranchID).Range("A:W")

Please let me know if you need more code than this, but I think this gives you the meat of the problem.


Post your answer or comment

comments powered by Disqus
How do I copy a worksheet and keep the header/footer etc?


I am trying to export 3 of my worksheets into a new workbook with vba code via a commandbutton. I have that part working only the column width and the row height as well as the print margins and print orientation does not get copied to the new workbook.

Is there any way to make this work without having to go back and re-format everything on all three sheets?



I am using this macro to merge worksheets into one master worksheet.

Sub CopyFromWorksheets()
Dim wrk As Workbook 'Workbook object - Always good to work with object
Dim sht As Worksheet 'Object for handling worksheets in loop
Dim trg As Worksheet 'Master Worksheet
Dim rng As Range 'Range object
Dim colCount As Integer 'Column count in tables in the worksheets

Set wrk = ActiveWorkbook 'Working in active workbook

For Each sht In wrk.Worksheets
If sht.Name = "Master" Then
MsgBox "There is a worksheet called as 'Master'." & vbCrLf & _
"Please remove or rename this worksheet since 'Master' would be"
& _
"the name of the result worksheet of this process.", vbOKOnly +
vbExclamation, "Error"
Exit Sub
End If
Next sht

'We don't want screen updating
Application.ScreenUpdating = False

'Add new worksheet as the last worksheet
Set trg = wrk.Worksheets.Add(After:=wrk.Worksheets(wrk.Worksheets.Count))
'Rename the new worksheet
trg.Name = "Master"
'Get column headers from the first worksheet
'Column count first
Set sht = wrk.Worksheets(1)
colCount = sht.Cells(1, 255).End(xlToLeft).Column
'Now retrieve headers, no copy&paste needed
With trg.Cells(1, 1).Resize(1, colCount)
.Value = sht.Cells(1, 1).Resize(1, colCount).Value
'Set font as bold
.Font.Bold = True
End With

'We can start loop
For Each sht In wrk.Worksheets
'If worksheet in loop is the last one, stop execution (it is Master
If sht.Index = wrk.Worksheets.Count Then
Exit For
End If
'Data range in worksheet - starts from second row as first rows are
the header rows in all worksheets
Set rng = sht.Range(sht.Cells(2, 1), sht.Cells(65536,
1).End(xlUp).Resize(, colCount))
'Put data into the Master worksheet
trg.Cells(65536, 1).End(xlUp).Offset(1).Resize(rng.Rows.Count,
rng.Columns.Count).Value = rng.Value
Next sht
'Fit the columns in Master worksheet

'Screen updating should be activated
Application.ScreenUpdating = True
End Sub

This works great. I have one question, is it possible to keep the same
format on the master worksheet that I have on the worksheets that I am
merging? The headers are the same, but the column width and the boarder
under the headers are not. Any help is appreciated.



I fear the answer to this question very simple and i've overlooked something obvious.

I have a simple macros which copies a worksheet and "exports" it to a new workbook. It works on the whole, but in the original document there is a picture/logo at the top, which doesn't get copied to the new workbook.

Any ideas how to get it to do this?

Here's the code i am currently using

    Application.ScreenUpdating = False 
    Sheets("Client version").Select 
    Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False _ 
    , Transpose:=False 
    ActiveWindow.Zoom = 75 
    Application.CutCopyMode = False 
    Application.DisplayAlerts = False 
    Application.DisplayAlerts = True 
    Application.ScreenUpdating = True 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Any help ould be appreciated :-)


I want to create a macro that copies the exact format of a sheet in my workbook called sheet "2". Sheet 2 contains formulas that reference sheet "1." Sheet 1 looks similar to what is shown here:

Sheet 1

1 A............B......C............. D............. E.............F
2 Color.....Item#..Unit.....Unit Price.......Qty.........Cost
3 Red..........1.....LS.....$29,000.00........1......$29,000.00
4 Blue ........2.....LF......$15.00............15........$225.00
5 Green.......3.....LF......$2.75............4263....$11,723.25

Sheet 2 summarizes each item in Sheet 1 like this:

2...ITEM NO...............................1
6...UNIT COST..........................$29,000.00

The number of items on sheet 1 can be as many as 200 and the items change from project to project so I want my macro to copy sheet 2's format and formulas except I want it to have all of the information for the next item down on the list in sheet 1...e.g instead of red, I want sheet 3 to have the information for "Blue", sheet 4 for green and so on.

I also want the macro to successively name the worksheets automatically, i.e sheet 3 should be named "3" and so on.

Any help would be appreciated.


Hi all

I have been trying to copy some formulas from a tab to another in the same
worksheet. what i want to do is to keep the original references of the
formulas. If i do a simple copy and paste, the resulting formulas will point
to different cells.

One way i devised to overcome this was to
1) cut and paste the formulas into the other tab
2) change all formulas into strings
3) copy strings back to the original place
4) back from strings to formulas

but this has the negative effect that all other cells linking to the ones i
cut, will be re-referenced to the new ones, that is something i dont want.

I hope this is not too confusing, but is there any way to copy formulas and
keep where they originally pointed to?
many thanks for your help!!


I want to copy the entire worksheet and paste it into new
worksheet in new workbook. I want to copy everything
except formulas. I am able to do it though Paste special ,
values, format , comments everythings goes very well to
new worksheet except the Images. I have some images in
some cells in my workbook.

How to copy and paste images too..though VBA programming.

Thanks in advance.

when I copy using the paste links function I lose the format. How do I paste
links and keep the format?

Hi, All.

I am trying to write a macro that copies/duplicates a worksheet named
"TG18" in the workbook, and assign new name as "TG19" and "TG20",
"TG21", "TG22" and so on ......
In this group, I have found some macro that does duplicates last
worksheet in the workbook, but it requires the name of worksheet must
be only in number format...
And I feel somehow this code is very complicated..
Any help would be appreciated. Thank you for taking your time.

The code I am using now ( which supports worksheet name as only number)

Option Explicit

Private Sub CopyLastSheet()

Dim LastN As Long
Dim N As Long
Dim S As Long
Dim SheetCount As Long

'1st task is to find the last sheet, the one
'with the highest name/number
SheetCount = ThisWorkbook.Worksheets.Count
LastN = 0
For S = 1 To SheetCount
N = CLng(Worksheets(S).Name)
If N > LastN Then LastN = N
Next S

'the next line copies the sheet with the highest number
'if the macro has been run at least once before, that sheet is
'a copy and has no formulas
'are you sure this is the sheet you want to copy?
Worksheets(CStr(LastN)).Copy After:=Worksheets(SheetCount)

'are you sure you don't want something like this instead of the
'Worksheets("Master").Copy After:=Worksheets(SheetCount)

SheetCount = SheetCount + 1
With Worksheets(SheetCount)
LastN = LastN + 1
.Name = Format$(LastN)
With .Range("A1:H67")
.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
End With
End With
Application.CutCopyMode = False
End Sub

I have a spreadsheet with about 800 rows. In column D, there is a variety of numbers from 013 to 080. There could be anywhere from 2 to 100 rows to each number. Everytime the number changes in column D, I would like to copy that group of rows to either another tab or to another worksheet. So, Rows 1-5 have 013 and rows 6-20 have 018 in column D and rows 21-81, have 025 in column D. Is is possible to have it moves rows 6-20 to one worksheet and rows 21-81 to another worksheet and keep going until there is no more data?

Hi there

I am trying to create a macro that copys a certain worksheet and places it second to last everytime the macro is recorded.

I have tried doing this using the record macro facility, but this places it in the different positions everytime I try.

Has anyone ever managed to do this?


I want to copy a named range and past on another sheet. I want the name to be applied to the pasted range on the new sheet. Possible?

Perhaps naming the rows again after the insert? How do I keep them active after an insert and then name?

Private Sub CommandButton1_Click()
Sheets("Mail").Rows("1:1").Insert Shift:=xlDown
Sheets("Mail").Range("INSERT").Insert Shift:=xlDown
End Sub

That is my current code. I want to name the Rows (9:12) and keep that name when they are inserted or just name the rows when they are inserted. REason is I want to be able to Delete these rows and the numbers are gonna change.


I am trying to figure out how to input data into a master worksheet (sheet 1)
and have that data filtered into separate worksheets and keep newest data at
the top of other worksheets (sheets 2 and higher).

For example if I have 5 categories of information A,B,C,D,E and on worksheet
1 i input a new data for category A, I want that data to show up on worksheet
2 which is a compilation of all category A data inputs.

Is this possible with a formula?

How can I copy a worksheet and rename at the same time?

Good to see I'm not the only one thinking it's an area for improvement.
Jon Peltier wrote:
> I've suggested it....
> - Jon
> WP wrote:
>> Thanks to all that replied. As usual, a lot of good information.
>> Maybe this could be a feature enhancement request for the next Excel.
>> CNUK wrote:
>>> I have a worksheet setup that graphs data contained in two dynamic
>>> named ranges. The graph is an object within the worksheet and not a
>>> separate graph sheet. The named ranges are defined as follows using
>>> the dynamic range name addin for Excel:
>>> Pressure
>>> ='150 GPM'!$F$12:OFFSET('150 GPM'!$F$12,COUNTA('150
>>> GPM'!$F$12:$F$65536)-1,0)
>>> RPM
>>> ='150 GPM'!$B$12:OFFSET('150 GPM'!$B$12,COUNTA('150
>>> GPM'!$B$12:$B$65536)-1,0)
>>> I want to use this sheet (including the embedded graph) as a template
>>> that can be copied to quickly process other data series.
>>> The problem is that when the worksheet is copied, the graph source
>>> data on the copied sheet still refers to the original sheet named
>>> ranges. My intent was to have it update to refer to the named ranges
>>> on the new sheet. I thought this used to work in a previous version
>>> of Excel but it could just be my memory playing tricks on me.
>>> Can anyone offer tips on how to get the graph source data series to
>>> update to refer to the correct named ranges ?
>>> Thank You


I am using the following code to insert 'x' number of new rows dependant on value 'y' and it does this just how I want, however, I also want the formulae and conditional formating from the original row to be present in all of the new rows because at the moment only the regular formatting is copied.

Any help is greatly appreciated.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Lst As Long
Dim Rw As Long

' limit to column D
If Intersect(Target, Columns("D")) Is Nothing Then Exit Sub

Application.EnableEvents = False
On Error Resume Next

Lst = Range("D" & Rows.Count).End(xlUp).Row
'For Rw = Lst To 1 Step -1
'    With Range("D" & Rw)
    With Range("D" & Lst)
        .Offset(1).EntireRow.Resize(.Value - 1).Insert
        .Offset(1, 1).Resize(.Value - 1) = .Offset(, 1).Value
    End With
'Next Rw

On Error GoTo 0
Application.EnableEvents = True

End Sub

Please open attached file.
When I press "commandbutton1", I need "userform1" to view and insert a name in the text box.
When I press OK in "userform1" I need a macro to copy worksheet "s0" into a new worksheet and rename this worksheet with the name typeed in "userform1".
Also I need to view the name of all worksheet copied using "userform1" in column H in worksheet final.

I am a beginner to vba and I never had used a userform
Thank you!

I designed a worksheet with a number of option buttons (ActiveX). I would like to make copies of this sheet in the same workbook, but when I do the option buttons don't work correctly. Is there something special I need to do when I make the copies? or Is there something I need to do to the code? Any help would be appreciated.


Hi all,

I hope there is someone out there who can help me.
I am able to create some simple Macros, but nothing complex as I need it now. So any help you guys could provide would really be appreciated. I work with Excel 2010.

What I have so far:
I have 2 Worksheets.
Worksheet no. 1 is named "List"
Worksheet no. 2 is named "Template"

Currently worksheet no. 1 is empty. It will be filled line by line.
A1 will be "0001", B1 will be "name1", C1 will be "name2"

What the makro is doing right now:
1.) Copy worksheet "Template" put it before "Template" and name it according to what is in A1 (=0001)
2.) Copy B1 into G1 of the new worksheet "0001"
3.) Copy C1 into G4 of the new worksheet "0001"

That's how far I got on my own and it works!

But now comes the challenge:
Once a new line is added in worksheet "List" I want to use the macro again to:
1.) Copy worksheet "Template" put it before "Template" and name it according to what is in A2 now (which then would be 0002)
2.) Copy B2 into G1 of the new worksheet "0002"
3.) Copy C2 into G4 of the new worksheet "0002"
4.) Once another line 3 is added in worksheet "list", do step 1-3 again, and so on (each time a new line is added in worksheet "list").

Ideally I would have a button in my worksheet "List" that I can press once I added a new line and then what I described above happens automatically.

I hope I explained everything well enough. In case I did not and there are open questions, please do not hesitate to contact me!

Thanks in advance for your help!!!

I am trying to concatenate two columns and keep the formatting. The first column is bold and the second column is regular. How do I concatenate and keep the first column bold? Thanks.

Hello all,

Is it possible to insert a row and keep all formats and formulas?

Thanks for any help,


Hi , is there a way to convert my xls to html and keep the conditional formatting ?
I have about 20 columns, 5 of them have conditional formatting on them.

Could someone please provide me with the code (or example code) for the following:

1. Code that will copy worksheets with the same name (e.g. "Sheet1") from all workbooks within a folder to an existing ?

2. Code that will move sequentially through worksheets within a workbook until it reaches the last worksheet




I have a macro to copy worksheets and consolidate them into one. However I need to either convert the formulas in the source worksheet to values before I copy them or paste values only when I paste them. Anyone?

Below is what I have as the code...
The top just let's you know what is the destination of the copy. Below that is the paste. (1) is original code (2) is how I tried to use paste special an it bombed.

'Set the destination cells

Set destCell = _
.Cells(.Cells.SpecialCells _
(xlCellTypeLastCell).Row + 1, "A")


.Range(.Cells(HeaderRows.Rows.Count, 1), _
.Cells.SpecialCells(xlCellTypeLastCell)).Copy _

.Range(.Cells(HeaderRows.Rows.Count, 1), _
.Cells.SpecialCells(xlCellTypeLastCell)).Copy _
PasteSpecial.Paste:=xlPasteValues, _

No luck finding an answer? You could always try Google.