+ Reply to Thread
Results 1 to 7 of 7

VBA to Copy Paste and Format Destination sheet

  1. #1
    Forum Contributor
    Join Date
    03-09-2014
    Location
    Telford
    MS-Off Ver
    Microsoft 365
    Posts
    101

    VBA to Copy Paste and Format Destination sheet

    Hi All

    I have the code to copy and paste multiple columns from one sheet to another. I want to tweak the code to format the heading cells (A1:D1) of the destination Sheet ( Final) as follows:

    1. AutoFit Column Width
    2.Cell Colour= Moderate effect- Blue, Accent 1
    3. Font size = 12
    4.Font Colour= White
    5.Bold font

    Any ideas on how to do this is much appreciated. Many thanks



    Please Login or Register  to view this content.

  2. #2
    Valued Forum Contributor
    Join Date
    09-19-2008
    Location
    It varies ...
    MS-Off Ver
    Office365 - 64bit
    Posts
    862

    Re: VBA to Copy Paste and Format Destination sheet

    Try this in your code:
    Please Login or Register  to view this content.
    Set the colour of a cell to the "Moderate effect- Blue, Accent 1" colour, select it, go to the immediate window and type ?activecell.interior.colorindex and replace the 33 with whatever number that returns.

    MM
    MatrixMan.
    --------------------------------------
    If this - or any - reply helps you, remember to say thanks by clicking on *Add Reputation.
    If your issue is now resolved, remember to mark as solved - click Thread Tools at top right of thread.

  3. #3
    Forum Contributor
    Join Date
    03-09-2014
    Location
    Telford
    MS-Off Ver
    Microsoft 365
    Posts
    101

    Re: VBA to Copy Paste and Format Destination sheet

    Sorry MatrixMan, could you please explain where in my code should I place your code?

  4. #4
    Forum Expert
    Join Date
    10-06-2017
    Location
    drevni ruchadlo
    MS-Off Ver
    old
    Posts
    2,143

    Re: VBA to Copy Paste and Format Destination sheet

    Quote Originally Posted by FredFitzgerald View Post
    ... I have the code to copy and paste multiple columns ...
    1. Why "On Error Resume Next", and without an "If" statement ?
    You are not sure if the given column exists ? And why in the loop, not in front of it ?

    2. Why "EntireColumn.Copy" ?
    All 65536 or 1048576 rows, even if only 200 will be used ?

    3. Why "wsF.Cells(1, i + 1)"
    What if there really is not a header/column ?
    The column will not be copied, and in the next rotation of the loop the next column will be copied one space further (i + 1) and there will be a "hole/gap" between the headers/columns.

  5. #5
    Forum Contributor
    Join Date
    03-09-2014
    Location
    Telford
    MS-Off Ver
    Microsoft 365
    Posts
    101

    Re: VBA to Copy Paste and Format Destination sheet

    Porucha thanks for the feedback. Apologies, this is a code given to me and i'm seeking help to tweak it to suit my needs. With your feedback I'm learning a great deal from it.

  6. #6
    Forum Expert
    Join Date
    10-06-2017
    Location
    drevni ruchadlo
    MS-Off Ver
    old
    Posts
    2,143

    Re: VBA to Copy Paste and Format Destination sheet

    Instead of:
    Please Login or Register  to view this content.
    use
    Please Login or Register  to view this content.
    Constant and declarations of variables:
    Please Login or Register  to view this content.
    Formatting the headers row:
    Please Login or Register  to view this content.

  7. #7
    Forum Contributor
    Join Date
    03-09-2014
    Location
    Telford
    MS-Off Ver
    Microsoft 365
    Posts
    101

    Re: VBA to Copy Paste and Format Destination sheet

    Brilliant Porucha, really appreciate this. This is exactly what I want. Thanks

+ 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. Copy from static destination & paste to dynamic destination
    By Marbleking in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 12-31-2015, 09:31 AM
  2. Copy and Paste w/ Paste Destination determined by Cell Value
    By jeremy9er7 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-10-2014, 10:42 AM
  3. Problem with destination format paste
    By leafs4life22 in forum Excel General
    Replies: 9
    Last Post: 07-13-2013, 02:50 AM
  4. Replies: 0
    Last Post: 03-05-2013, 07:34 PM
  5. Get data from source workbook and paste to destination in change format
    By farrukh in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-03-2012, 01:47 AM
  6. [SOLVED] Copy data in one format from Sheet1 and paste to another sheet in another format.
    By FergusC in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-06-2012, 08:36 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