+ Reply to Thread
Results 1 to 23 of 23

VBA help, Copy and Paste with maintain format table , but not formulas in the table

  1. #1
    Registered User
    Join Date
    10-09-2018
    Location
    USA
    MS-Off Ver
    2013
    Posts
    12

    Question VBA help, Copy and Paste with maintain format table , but not formulas in the table

    Hello,

    I am trying to create VBA code that can copy sheet and paste on the different sheet.
    but when this happen I want to paste everything except formulas I used to get the data.


    this is code i made.

    and this is file.

    thank you so much for reading my post.
    I am very new to vba, so forgive me.

    once again thank you so much


    Please Login or Register  to view this content.
    Attached Files Attached Files

  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,091

    Re: VBA help, Copy and Paste with maintain format table , but not formulas in the table

    See if this works for you:

    Please Login or Register  to view this content.
    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
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,113

    Re: VBA help, Copy and Paste with maintain format table , but not formulas in the table

    Hello asweare,

    TMS had given you some awsome Code. Here is a small change to cancel Copy and Paste, and to clear the Data if you so wish.

    Please Login or Register  to view this content.
    Regards.
    Last edited by Winon; 01-27-2019 at 11:42 AM. Reason: text
    Please consider:

    Be polite. Thank those who have helped you. Then Click on the star icon in the lower left part of the contributor's post and add Reputation. Cleaning up when you're done. If you are satisfied with the help you have received, then Please do Mark your thread [SOLVED] .

  4. #4
    Registered User
    Join Date
    10-09-2018
    Location
    USA
    MS-Off Ver
    2013
    Posts
    12

    Exclamation Re: VBA help, Copy and Paste with maintain format table , but not formulas in the table

    WOW, Thank you so much for helping me out!

    but unfortunately it didn't really worked as I was hoping for.

    when it paste I wanted it still maintain format of table. but unfortunately it only got looks.

    if you look at carefully you can notice there is arrow down, with it i can make detail pivot table.

    I edited my file so you can see the result I am really hope for.

    once again thank you so much for helping me out ^^
    Attached Files Attached Files

  5. #5
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: VBA help, Copy and Paste with maintain format table , but not formulas in the table

    Try this:

    Please Login or Register  to view this content.
    If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)

    You can't do one thing. XLAdept

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin

  6. #6
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,113

    Re: VBA help, Copy and Paste with maintain format table , but not formulas in the table

    @ xladept,

    I HATE You!

    Please DON'T do that to me!

    This is absolutely BRILLIANT!

    Here is what I would offer asweare;

    Please Login or Register  to view this content.
    Regards.

  7. #7
    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,091

    Re: VBA help, Copy and Paste with maintain format table , but not formulas in the table

    And my updated offering ( )

    Please Login or Register  to view this content.
    Last edited by TMS; 01-27-2019 at 03:30 PM.

  8. #8
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: VBA help, Copy and Paste with maintain format table , but not formulas in the table

    @ Winon - Hey, old buddy that ought to muddy it up

  9. #9
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,113

    Re: VBA help, Copy and Paste with maintain format table , but not formulas in the table

    @ xladept,

    Nah, I think you like it my friend!

  10. #10
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,113

    Re: VBA help, Copy and Paste with maintain format table , but not formulas in the table

    @ TMS,

    Hi Trevor,

    This is what I finally came up with tweaking your Code and creating a Customized Table for the "Paste" Sheet.

    Please Login or Register  to view this content.
    Regards.

  11. #11
    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,091

    Re: VBA help, Copy and Paste with maintain format table , but not formulas in the table

    Hopefully, between us, we will habe produced a solution that works for the OP

  12. #12
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: VBA help, Copy and Paste with maintain format table , but not formulas in the table

    @ TMS - Hopefully!

  13. #13
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,113

    Re: VBA help, Copy and Paste with maintain format table , but not formulas in the table

    @ xladept and TMS,

    Come on now you guys! Of course you have provided perfect solutions. Kudos to both of you!

  14. #14
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: VBA help, Copy and Paste with maintain format table , but not formulas in the table

    @ Winon - Why us? Why not we???

  15. #15
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,113

    Re: VBA help, Copy and Paste with maintain format table , but not formulas in the table

    @ xladept - You know that song - "Oh Lord, its hard to be hum...." ?

  16. #16
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: VBA help, Copy and Paste with maintain format table , but not formulas in the table

    @ Winon - "Oh Lord it's hard to be humble When you're perfect in every way. I can't wait to look in the mirror Cause I get better looking each day To know me is to love me I must be a hell of a man. O Lord it's hard to be humble We are doing the best that we can." - Mac Davis. "It's a blessing and a curse" - Monk.

  17. #17
    Registered User
    Join Date
    10-09-2018
    Location
    USA
    MS-Off Ver
    2013
    Posts
    12

    Talking Re: VBA help, Copy and Paste with maintain format table , but not formulas in the table

    WOW! this really works, great

    thank you so much for all the help!!

    Can I add another question to this?

    what if I had more than one table at sheet?

    is it possible to read the whole sheet and figure out how many tables there are

    and then copy and paste ?

    I made another example.
    Attached Files Attached Files

  18. #18
    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,091

    Re: VBA help, Copy and Paste with maintain format table , but not formulas in the table

    If it were me, I'd just delete the "Paste" sheet, if it exists, copy the "Copy" Sheet and name the copy as "Paste". Then you can select all the data on the new "Paste" sheet and use .Value = .Value to convert everything in the Tables to values.

  19. #19
    Registered User
    Join Date
    10-09-2018
    Location
    USA
    MS-Off Ver
    2013
    Posts
    12

    Re: VBA help, Copy and Paste with maintain format table , but not formulas in the table

    i don't really understand...
    you mean like this?

    Please Login or Register  to view this content.
    but this code destroy the format of table

  20. #20
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: VBA help, Copy and Paste with maintain format table , but not formulas in the table

    Thanks for the rep!

  21. #21
    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,091

    Re: VBA help, Copy and Paste with maintain format table , but not formulas in the table

    You're welcome. Thanks for the rep.

    i don't really understand...
    you mean like this?

    Please Login or Register  to view this content.
    but this code destroy the format of table

    No, not quite ... bit more complicated. Like this:


    Please Login or Register  to view this content.

  22. #22
    Registered User
    Join Date
    10-09-2018
    Location
    USA
    MS-Off Ver
    2013
    Posts
    12

    Re: VBA help, Copy and Paste with maintain format table , but not formulas in the table

    OH MY GOD , TMS , THIS is exactly I really want to accomplished
    thank you so much for your great help.

    it really means a lot thank you!!

  23. #23
    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,091

    Re: VBA help, Copy and Paste with maintain format table , but not formulas in the table

    You're welcome. Thanks for the rep.



    If you are satisfied with the solution(s) provided, please mark your thread as Solved.


    New quick method:
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

    Or you can use this way:

    How to mark a thread Solved
    Go to the first post
    Click edit
    Click Go Advanced
    Just below the word Title you will see a dropdown with the word No prefix.
    Change to Solved
    Click Save


    You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

+ 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] Copy paste data from table to another table then delete data without losing formulas
    By dps9460 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-11-2019, 03:14 AM
  2. maintain format on pivot table
    By Hassan1977 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-22-2016, 04:53 PM
  3. Copy Paste From a Question That is in a table format to individual rows
    By ExcelNoob243 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-16-2015, 10:57 PM
  4. [SOLVED] Allow ONLY Copy/ Paste Value Function to Maintain Cell Format
    By katkth7533 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-17-2015, 07:06 PM
  5. [SOLVED] How to Copy Paste Only Table Format,exclude the data using VBA
    By shelbymarsa in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-08-2013, 11:42 PM
  6. Copy and Paste Table into String in HTML format
    By Jay59 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-31-2008, 12:32 PM
  7. [SOLVED] Maintain Format of Pivot Table
    By Cash in forum Excel General
    Replies: 1
    Last Post: 03-30-2006, 02:25 PM

Tags for this Thread

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