+ Reply to Thread
Results 1 to 3 of 3

Save an area and later paste it (normal and only format)

  1. #1
    Registered User
    Join Date
    02-12-2016
    Location
    Sweden
    MS-Off Ver
    2016
    Posts
    8

    Save an area and later paste it (normal and only format)

    Hi everyone,
    *** I have an urgent topic - I hope someone can help ***

    As part of a longer vba code (too difficult to give the whole picture), I want to save an array and later paste it (normal and later only format).
    I can't figure out the syntax for this quite "simple" operation.

    With the help of some other code, these variables have the follwing values (just examples):
    StartRow = 11
    EndRow = 14
    StartColumn = 1
    EndColumn = 20

    PasteRow1 = 100
    PasteColumn1 = 1

    PasteRow2 = 120
    PasteColumn2 = 1


    Steps that I want to do:
    1) I first want to copy the area defined by StartRow,StartColumn & EndRow,EndColumn into a variable named "SavedArray" (which then will contain "4 x 20 cells" in this example)
    2) Then I want to paste "SavedArray", starting at cell PasteRow1, PasteColumn1. Like in normal Excel go to this specifik cell and press CTRL-V.
    3) Then I will change some of the cell values within "StartRow,StartColumn & EndRow,EndColumn" (this I know how to do). However, the content of "SavedArray" must NOT be affected!
    4) Then I want to "paste special" (only formats) the "SavedArrey" starting at cell PasteRow2, PasteColumn2. Like in normal Excel go to this cell and press "Paste Special" / "Formats". Since I meanwhile have changed the content of the cells from which "SavedArray" was copied, it must be the original content of SavedArray.

    Many thanks in advance!
    Bjorn
    Last edited by AliGW; 01-04-2018 at 02:09 AM.

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,086

    Re: URGENT: Save an area and later paste it (normal and only format)

    Easy enough to save the data to an array but you can't save the formatting. You would be better off saving the cells, complete with formatting, to a hidden temporary worksheet.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Registered User
    Join Date
    02-12-2016
    Location
    Sweden
    MS-Off Ver
    2016
    Posts
    8

    Re: URGENT: Save an area and later paste it (normal and only format)

    Thanks!
    Could you please also advice me how to create a hidden temporary worksheet and paste it there, and how to delete this temp. worksheet when done?
    (sorry, I am quite bad at vba syntax)

+ 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. Replies: 2
    Last Post: 10-11-2015, 11:13 AM
  2. Replies: 3
    Last Post: 02-23-2015, 06:51 AM
  3. [SOLVED] Convert Pivot Table Format to Normal Format
    By mapleaes in forum Excel Charting & Pivots
    Replies: 6
    Last Post: 04-08-2014, 05:51 AM
  4. Replies: 3
    Last Post: 05-13-2011, 03:02 AM
  5. normal save but with modified filename
    By johnp in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-04-2010, 07:01 AM
  6. Cut area & Paste area size problem
    By noidea in forum Excel General
    Replies: 1
    Last Post: 07-19-2007, 12:35 PM
  7. Replace normal paste with Paste As Text
    By [email protected] in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-03-2006, 09:30 AM

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