Solved: Macro Help

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

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?!?!?!
 

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,641
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
 

Attachments

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?
 

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,641
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.
 

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!
 

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,641
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.
 

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,641
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
 

Attachments

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

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 807,865 other people just like you!

Latest posts

Staff online

Members online

Top