+ 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
    2010
    Posts
    5

    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 2003,2007,2010
    Posts
    27,013

    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 - Excel Aid

    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
    2010
    Posts
    5

    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)

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