Copy Worksheet Between Books and Retain Formatting

P

PatK

I have the following code that copies a specific worksheet from one workbook,
to another. Problem is, that the format of the worksheet is lost during the
copy. Is there a way to make sure that, when copied, the worksheet I have
just pasted looks exactly like the original?

Thanks for any help! Patk

Sub SheetCopyBetweenFiles(sourcefile, destfile, sheetnametocopy, aftersheet)

Dim srcbook, destbook As Workbook
Dim copysheet, newsheet As Worksheet

Workbooks(sourcefile).Activate
Set srcbook = ActiveWorkbook
Set copysheet = srcbook.Worksheets(sheetnametocopy)
Set destbook = Workbooks(destfile)

With destbook
.Worksheets.Add after:=.Worksheets(.Worksheets.Count)
.ActiveSheet.Name = sheetnametocopy
copysheet.Cells.Copy .Worksheets(.Worksheets.Count).Cells

End With
Set srcbook = Nothing
Set copysheet = Nothing
Set destbook = Nothing
End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top