VBA code to copy and paste as values multiple sheets into a different workbook

  • Hi

    I have a workbook with the price lists for various customers and I am trying to copy into a different workbook certain tabs only. I was able to separate by using the below code, but I also want to paste as values only. Please could you help me to adjust the below code?



  • 31 posts, have you not read the Forum Rules yet?

    Please read the Forum Rules to understand how the Forum works and why I have added Code Tags to your post

    All VBA code posted in the forum must be wrapped in code tags, which you omitted, including single-line code snippets.Be sure to use them in future posts.

    How to use code tags

    Note: no apostrophe in the tags, just used for demonstration here.


    your code goes between these tags


    Or, just highlight all of the code and press the <> in the post menu above button to add the code tags.


  • Try this

  • Hi Roy,

    Apologies, I usually attach the file that I am working on and was rushing late last night :) Going forward I will make sure that I have wrapped the VBA code in code tags.

    I have tried copying your code to my attached file but it didn't seem to want to work.

    Please could you have another look?



  • It sees that it is not possible to copy more than one sheet to a new workbook or existing workbook if there is a List (Excel 2003) or a Table (Excel 2007+) in one of the worksheets. I've adapted a workaround posted by Ron de Bruin.