+ Reply to Thread
Results 1 to 8 of 8

Copy Worksheet to another Worksheet Preserving all Formats - VBA Code Almost There!!!!!!!

  1. #1
    Registered User
    Join Date
    06-13-2011
    Location
    Bournemouth
    MS-Off Ver
    Excel 2010
    Posts
    19

    Copy Worksheet to another Worksheet Preserving all Formats - VBA Code Almost There!!!!!!!

    Guys,

    I almost have what i'm looking for, but not quite there, and the final hurdle is that existing VBA code below copies all of the worksheet data required, but does not copy formats or page breaks which kind of defeats the object of what i'm trying to achieve if i have to manually set formats for 100 worksheets individually.

    I have discovered some VBA code that does seem to do a direct copy and preserve formats and pagebreaks, but my limited VBA knowledge prevents me from merging the 2 to produce a result.

    Existing Code
    Sub dingbat4()
    Dim ws As Worksheet 'source sheet'
    Dim ws2 As Worksheet 'target sheet'
    Dim i As Long
    Application.ScreenUpdating = False
    Set ws = Sheets("Template") 'source worksheet'
    For i = 101 To 200 'cycle thru to insert 100 worksheets'
    Sheets.Add.Name = i 'add sheet name starting 101 ending 200'
    Set ws2 = ActiveSheet 'inserted sheets'
    ws.UsedRange.Copy ws2.Range("A1") 'copy used range to start cell "A1" new sheet'
    ws2.Range("e9").Value = ws2.Name 'insert sheet name in cell "e9" on each sheet'
    ws2.Move After:=Sheets(Sheets.Count) 'sheets numbering starts at 101'
    Set ws2 = Nothing
    Next i
    Application.ScreenUpdating = True
    End Sub

    Code that copies sheet and preserves formatting
    Sub Copier()
    Dim x As Integer
    x = InputBox("Enter number of times to copy Sheet1")
    For numtimes = 1 To x
    ActiveWorkbook.Sheets("Sheet1").Copy _
    After:=ActiveWorkbook.Sheets("Sheet1")
    Next
    End Sub

    Thanks in anticipation

    Dingbat

    (PS - Thanks to John H Davis for getting me this far...)

  2. #2
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: Copy Worksheet to another Worksheet Preserving all Formats - VBA Code Almost There!!!!

    Please use code tags when posting.

    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    06-13-2011
    Location
    Bournemouth
    MS-Off Ver
    Excel 2010
    Posts
    19

    Re: Copy Worksheet to another Worksheet Preserving all Formats - VBA Code Almost There!!!!

    John,

    Thanks again, this revised code does copy sheet formatting across, but does not seem to be page break friendly, any ideas?

    Dingbat.

  4. #4
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: Copy Worksheet to another Worksheet Preserving all Formats - VBA Code Almost There!!!!

    Not sure if this will do it. Try on a copy first.

    Please Login or Register  to view this content.

  5. #5
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    work: 2016 on Win10 (notebook), private: 2019 on Win10 (desktop), 2019 on Win11 (notebook)
    Posts
    8,197

    Re: Copy Worksheet to another Worksheet Preserving all Formats - VBA Code Almost There!!!!

    Hi, dingbats,

    just for being curious: wouldn´t it be easier to copy the template instead of copying/pasting the cells from that sheet?

    Please Login or Register  to view this content.
    Ciao,
    Holger
    Use Code-Tags for showing your code: [code] Your Code here [/code]
    Please mark your question Solved if there has been offered a solution that works fine for you

  6. #6
    Registered User
    Join Date
    06-13-2011
    Location
    Bournemouth
    MS-Off Ver
    Excel 2010
    Posts
    19

    Re: Copy Worksheet to another Worksheet Preserving all Formats - VBA Code Almost There!!!!

    Guys,

    Thanks for your input, unfortunately neither option provides the solution i'm looking for, as follows;

    JOHN H DAVIES - This option does not retain the page break formats of the "template document"

    HOLGER - Code runs and produces VBA error.

    The attached spreadsheet indicates what i am trying to achieve under "required" sheet, and what is occurring under the "result" sheet.

    Thanks

    Dingbat.
    Attached Files Attached Files

  7. #7
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: Copy Worksheet to another Worksheet Preserving all Formats - VBA Code Almost There!!!!

    Appears to work for me. Try:

    Please Login or Register  to view this content.

  8. #8
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    work: 2016 on Win10 (notebook), private: 2019 on Win10 (desktop), 2019 on Win11 (notebook)
    Posts
    8,197

    Re: Copy Worksheet to another Worksheet Preserving all Formats - VBA Code Almost There!!!!

    Hi, dingbat,

    first of all you still need to add code-tags to the opening post.

    Please Login or Register  to view this content.
    Ciao,
    Holger

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] VBA code to copy certain cells from one worksheet into a single row in another worksheet
    By thedefense in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-08-2013, 06:11 PM
  2. Copy worksheet values and formats into another workbook
    By bishwino in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-28-2005, 01:19 PM
  3. Copy worksheet to new workbook : Values & Formats
    By Franck in forum Excel General
    Replies: 1
    Last Post: 10-26-2005, 10:05 AM
  4. [SOLVED] VBA Code to copy values &formats to the next worksheet
    By bobby in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 10-19-2005, 10:05 AM
  5. copy worksheet (keeping formats)
    By Ken in forum Excel - New Users/Basics
    Replies: 3
    Last Post: 02-16-2005, 12:45 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1