Creating new WB, then combining WS'

[heist]

Hello all,

I got caught up in my code and was wondering if anyone could help me out.

The code is supposed to create a new Workbook and then open worksheets within a folder with the same beginning and then save the workbook as the original save-as when it was created.

I am able to create and save the new workbook, but unable to get CombineFormats(X) to work for me.

Any suggestions are greatly appreciated

Please Login or Register  to view this content.

[JBeaucaire]

Stepping away from your code... what is the logic you're trying to accomplish?

1) Create a new empty workbook
2) Open all the workbooks in a given folder and copy each sheet into the new workbook as a new sheet of its own. So if there were 10 wbs with 3 sheets in each, the new workbook would have a total of 30 sheets in it when done.
3) None of the original workbooks are changed


Or

1) Create a new empty workbook
2) Open all the workbooks in a given folder and copy each sheet into the new workbook on the same sheet. So if there were 10 wbs with 3 sheets in each, the new workbook would have a single sheet with all the data from those 30 sheets stacked into a single reference sheet
3) None of the original workbooks are changed


Or something else?

[heist]

Hey JBeaucaire,

Option #1 is spot on.

Here's the logic:
1. I already have a portion of my script that cleans up .csv and formats them into columns, it saves them with default .csv name.
2. This broken portion creates a new WorkBook that I will set as a variable between two names. It then finds all WS' matching X + "*" and adds them to the created Workbook and saves the file with save.

Thanks for the looks

My Background: Proficient in all web-designing languages, I can hang with Python. VB is new to me, this is my first macro.

[JBeaucaire]

What is the value of X ? I don't see the variable referenced and filled earlier.

If you put OPTION EXPLICIT at the top of your code modules, life gets easier in VBA, it will point out most syntax errors and spelling errors or missing variable declarations.

[heist]

What is the value of X ? I don't see the variable referenced and filled earlier.

If you put OPTION EXPLICIT at the top of your code modules, life gets easier in VBA, it will point out most syntax errors and spelling errors or missing variable declarations.

Hey JBeaucaire,

I'll post the full script when I get back in the office. I think that will clear up a lot of questions and such.

[heist]

Here's the full code that I have created. I renamed some of the variables and such for privacy issues.

The part that I am having a lot of trouble with is creating the new workbook, naming it and then adding sheets to the new workbook and saving it. I believe it has a lot to do with the new workbook that I am creating being active and being correctly called into "CombineFormats".

Once again, help is greatly appreciated.

Please Login or Register  to view this content.

[JBeaucaire]

Ok, here are my suggested edits so far. The problem with X in the Combine macro is that it hasn't been defined yet. I follow everything up until you CALL the CombineFormats and you use MasterWB1 as the variable to feed in as "X". That variable has not been declared yet and has no value.

Please Login or Register  to view this content.