pasting formats with vba
VBA Telemetry pings you when your VBA projects fail
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: pasting formats with vba

  1. #1
    Guest

    Default

     
    When I copy a worksheet and then paste it to another, some formatting doesn't follow. ex. column width etc. What code do I use to make the column width paste too. Used this code to copy.

    Worksheets("template").UsedRange.SpecialCells(xlCellTypeVisible).Copy
    Worksheets(2).Activate
    Range("A1").Select
    ActiveSheet.Paste

  2. #2
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    It's a separate procedure, add the code to the end of your current procedure. May make your workbook big:

    application.screenupdating = false
    Worksheets("template").Cells.Select
    Selection.Copy
    [a1].select
    Worksheets(2).Activate
    Cells.Select
    Selection.PasteSpecial Paste:=xlFormats
    [a1].select
    application.screenupdating = true

    Cheers, Nate

    [ This Message was edited by: NateO on 2002-03-12 16:23 ]

  3. #3
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    To paste the column widths use this

    Const xlColumnWidths = 8

    Selection.PasteSpecial Paste:=xlColumnWidths

    OR

    Selection.PasteSpecial Paste:=8


    Ivan


  4. #4
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    On 2002-03-12 18:32, Ivan F Moala wrote:
    To paste the column widths use this

    Const xlColumnWidths = 8

    Selection.PasteSpecial Paste:=xlColumnWidths

    OR

    Selection.PasteSpecial Paste:=8


    Ivan

    I should have explained further.
    The pastespecial xlcoulmnwidths does not
    work properly in excel2000.
    Try it and see ie record a macro using the
    pastespecial columnwidths.
    Then run it.....you will receive and error.
    The code I gave is a work around to this problem.


    Ivan

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com