+ Reply to Thread
Results 1 to 12 of 12

VBA to copy sheet (i) of workbook (z) to sheet (j) in new workbooks (n) with values only

  1. #1
    Registered User
    Join Date
    01-09-2015
    Location
    Germany
    MS-Off Ver
    MS Office 2010
    Posts
    33

    VBA to copy sheet (i) of workbook (z) to sheet (j) in new workbooks (n) with values only

    Dear all
    I am not skilled enough in VBA codes.
    I am looking for a simple VBA code which does the following:

    1. It exists a workbook Z with 10 sheets (a, b, c, d, e, ...)
    2. The macro should now copy sheet (a) of workbook Z in another new workbook 1; copy sheet (b) of workbook Z into another workbook 2; copy sheet (c) of workbook Z into another workbook 3; copy sheet (d) of workbook Z into another workbook 4 and so on.
    Copy paste should be only with values.
    And the new workbooks should be saved automatically in a certain path.

    Thank you for your help.

    I do not attach (yet) an excel, as I believe that the description should be hopefully clear enough.

    Best regards
    Heinz
    Last edited by megaheinz; 04-30-2021 at 10:00 AM.

  2. #2
    Forum Expert
    Join Date
    05-29-2020
    Location
    NH USA
    MS-Off Ver
    365
    Posts
    2,102

    Re: VBA to copy sheet (i) of workbook (z) to sheet (j) in new workbooks (n) with values on

    Try

    Please Login or Register  to view this content.

  3. #3
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    5,496

    Re: VBA to copy sheet (i) of workbook (z) to sheet (j) in new workbooks (n) with values on

    No copy and paste.
    Please Login or Register  to view this content.
    For saving as a macro enabled workbook, change the ".xlsx" to ".xlsm" and the FileFormat to 52

  4. #4
    Registered User
    Join Date
    01-09-2015
    Location
    Germany
    MS-Off Ver
    MS Office 2010
    Posts
    33

    Re: VBA to copy sheet (i) of workbook (z) to sheet (j) in new workbooks (n) with values on

    Thank you all for the quick reply.
    Both macros work as such but both unfortunately do not keep the format (color/borderlines etc).
    Secondly: how to modify the macro if I want to copy only certain sheets out of the existing 10 for example.
    Let's say: sheet 1 has name "1", sheet 2 has name "2", sheet 3 has name "3" and so on until "10" but I want to copy only sheet "1", sheet "3", sheet "5" and sheet "7" (as example)?
    Best regards

  5. #5
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    5,496

    Re: VBA to copy sheet (i) of workbook (z) to sheet (j) in new workbooks (n) with values on

    Do the formulae in these sheets have links to other sheets? (In Sheet1 zum beispiel =Sheet2!A6)

  6. #6
    Forum Expert
    Join Date
    05-29-2020
    Location
    NH USA
    MS-Off Ver
    365
    Posts
    2,102

    Re: VBA to copy sheet (i) of workbook (z) to sheet (j) in new workbooks (n) with values on

    One option is to use the code below. Make sure to enter values for each sheet s highlighted in red below.

    Please Login or Register  to view this content.

  7. #7
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    5,496

    Re: VBA to copy sheet (i) of workbook (z) to sheet (j) in new workbooks (n) with values on

    In the InputBox, enter the sheet names as they appear on the Tabs comma separated (Sheet1, Sheet2, Expenses, 2, 4 usw)
    Please Login or Register  to view this content.

  8. #8
    Registered User
    Join Date
    01-09-2015
    Location
    Germany
    MS-Off Ver
    MS Office 2010
    Posts
    33

    Re: VBA to copy sheet (i) of workbook (z) to sheet (j) in new workbooks (n) with values on

    @ jolivanes : Yes, there are links to other sheets

  9. #9
    Registered User
    Join Date
    01-09-2015
    Location
    Germany
    MS-Off Ver
    MS Office 2010
    Posts
    33

    Re: VBA to copy sheet (i) of workbook (z) to sheet (j) in new workbooks (n) with values on

    Dear maniacb, thanks again a lot. This modified macro indeed cpies then only the selected sheets of the original workbook, but still it unfortunately does not keep the formatting.
    Best regards
    Heinz

  10. #10
    Forum Contributor
    Join Date
    02-09-2021
    Location
    New York
    MS-Off Ver
    Office 2019
    Posts
    145

    Re: VBA to copy sheet (i) of workbook (z) to sheet (j) in new workbooks (n) with values on

    Hi, megaheinz,

    Please try:
    You must enter the file path and place in the array the sheetnames you want to keep.

    Please Login or Register  to view this content.
    EDITED: ADDED CLOSE AND SAVE FILE
    Last edited by D13L; 04-30-2021 at 05:11 AM.

  11. #11
    Registered User
    Join Date
    01-09-2015
    Location
    Germany
    MS-Off Ver
    MS Office 2010
    Posts
    33

    Re: VBA to copy sheet (i) of workbook (z) to sheet (j) in new workbooks (n) with values on

    Dear D13L
    Thanks a lot.
    It works!!!
    Great.
    Thank you all.
    Best regards
    Heinz

  12. #12
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    5,496

    Re: VBA to copy sheet (i) of workbook (z) to sheet (j) in new workbooks (n) with values on

    Have you tried the code from Post #7?

+ 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 first sheet from different workbooks to target workbook
    By kaushalkbc in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 09-19-2019, 10:29 AM
  2. [SOLVED] Copy cell values from one sheet and paste it in another sheet of the same workbook
    By Tauheed Ahmed in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-13-2016, 08:51 AM
  3. Loop through workbooks and copy just the first sheet only to master workbook.
    By swade730 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-24-2014, 10:36 AM
  4. How to copy a sheet from one workbook as a sheet onto multiple workbooks
    By hld142 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-25-2014, 10:11 AM
  5. vba to copy from multiple workbooks to a summary sheet in another workbook.
    By lisadykstra in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 07-22-2014, 03:36 PM
  6. Copy a first sheet of a workbook in the first sheets of other workbooks
    By khracking in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-29-2012, 05:10 PM
  7. [SOLVED] VBA: Copy First Sheet of a List of Workbooks to New Workbook
    By DHartwig35805 in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 09-12-2012, 06:05 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