Need some help geting VBA code to work back to back.

[BrettRCourtney]

I have two sets of code. The first sets of code takes seperate excel spreadsheets and consolidates it into one excel spreadsheet with seperate tabs. The second code takes the tabs C1's thru C10's B32:B3032 Coloumns and sets them as seperate coloumns in a tab that i have to create called C. I would like to make it so I don't have to use two sets of code but don't know how. If anyone has the solution, I would greatly appreciate it.

Please Login or Register  to view this content.

[Norie]

Which workbook do you want to run SummurizeSheets on?

[BrettRCourtney]

I would like to run summerizeSheets on Consolidated.xlsx

[mehmetcik]

I do not see your problem.

All you do is paste the code within the second macro into the bottom of the first.

Am I missing something?

Please Login or Register  to view this content.

[Norie]

You should just be able to add this at the end of Macro1.

Please Login or Register  to view this content.

Mind you, instead of copying the worksheet from the workbooks you are opening you could just copy the range.

Please Login or Register  to view this content.

PS Could you please add code tag? It was hard to see where one sub ended and the other started, tags would make that easier.

Oh, and I think adding code tags is kind of a rule too.

[BrettRCourtney]

Thanks for the response and I'll remember to use code tags for future postings. I'm not an experienced code writer and when I tried using your suggested code below I got the following error. Run-time error '9': Subscript out of range. It didn't like newWb.Sheets(ctr).Name = CStr(elem)

Option Explicit

Please Login or Register  to view this content.

[Norie]

That line isn't needed any more as you aren't copying sheets from the workbooks.

[BrettRCourtney]

It's almost seems to be working correctly. However, in the new created workbook Consolidated it listed A1:B3032 from C1 instead of B32:B3032 from C1. C2, C3, C4, C5, C6, C7, C8, C9, and C10 are shown correctly shows data B32:B3032. Also, C2 overlaps B Coloumn data in B1. Do you have any idea what could be causeing this?

[BrettRCourtney]

I think what it's doing is not creating a seperate sheet. It's overlaping the data B32:B3032 from C2, C3, C4, C5, C6, C7, C8, C9, C10 into the sheet C1 starting in coloumn B. How do I go about fixing this?

Thanks again,
Brett

[Norie]

Brett

A new workbook is created by copying the first sheet of the first workbook.

Then instead of copying worksheets from the rest of the workbooks just the range is copied from them to then new workbook.

That might need a bit of a tweak to work properly, I can't tell without knowing more about the layout of the worksheets you are copying from.

Did you try my initial suggestion?

[BrettRCourtney]

Thank you very much Norie. You've been a lot of help. I just added a blank workbook to the folder and everything works okay now.

Thanks again,
Brett