1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Macro Help

Discussion in 'Business Applications' started by jo15765, Oct 29, 2011.

Thread Status:
Not open for further replies.
Advertisement
  1. jo15765

    jo15765 Thread Starter

    Joined:
    Oct 11, 2011
    Messages:
    307
    I Have about 15 workbooks each containing 2 sheets. I want to take each of the two sheets in this workbook, and combine them into one "Master" workbook. I have been working on a macro and am almost there, but am stumped. This is the code that I have thus far:
    Code:
    Dim varBooks
    Dim varBook
    Dim SName
    Dim wb As Excel.Workbook
    
    varBooks = Array(All my workbook names)
    
        For Each varBook In varBooks
            Set wb = Workbooks.Open(Filename:="Workbooks location" & varBook)
            With wb
            ActiveWorkbook.Sheets.Select
            ActiveWindow.SelectedSheets.Copy
            With ActiveWorkbook
            .Close& ".xls"
            End With
    Next varBook
    
    This will open each workbook in my array, and will copy the sheets into a new workbook. The problem is, it opens a new workbook for each workbook in the array. I want it to copy each sheet into ONE workbook.

    Can someone please point out the error in my ways?!?!?!
     
  2. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    I just finished this one for another post
    The only restriction is that the files have to be in the same folder.
    Give it a shot.
    I'm attaching the 2003 version since you omitted to mention which version of Excel you're using, but it'l work with every version

    Just open the file and press the START button, a new workbook will be created containing all the selected workbooks and sheets
     

    Attached Files:

  3. jo15765

    jo15765 Thread Starter

    Joined:
    Oct 11, 2011
    Messages:
    307
    That combined all sheets...but it totally butchered my source formatting! I have to keep the same formatting (I.E. it changed fonts, it shrunk cells, etc etc). Also, I am assuming to get the sheets in the order that I need them in, they have to be in that same order from within the source file?
     
  4. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    Wel, then you have to edit the macro to also include the formatting, you had some code that didn't work, I just offered a solution for someone that needed it.
    All cod needs editting so well, go for it.
    It's just one or two lines of code, but this was just to help you and see how you could put them all in a ne workbook.
    Sorry that I sent it.
     
  5. jo15765

    jo15765 Thread Starter

    Joined:
    Oct 11, 2011
    Messages:
    307
    No I am very appreciative for the assistance. I am just learning VBA and don't understand a lot of advanced coding techniques that the more seasoned VBA programmers use. I didn't see the line of code in yours that you sent, that stated what order to use them in. But again thank you for the code!
     
  6. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    I'll check about the formatting, what kind of sheets may I expect, this code was originally for simple xls sheets with data.
    Can you give me an idea of what type of formatting you're talikng about?
    Keep in mind that formulás ir they refer to another sheet will not work.
    If you could post a sheet you want to red I'll give it a shot.
     
  7. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    Try this one, the processing is different, maybe it works better

    I tested it and kept the formatting in some of my sheets, so?
    Let me know,
    and excuse my tone when I answered you the first time, it wasn't meant the way it sounded, but I'm no expert coder either I just use examples and work them out to suit my needs or try new things.
    Trial-and-error and debugging....:)


    BTW the version number will be shown on the application bar
     

    Attached Files:

  8. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1024619

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice