VBA - macro, help

[Camel123]

Hi,

I am about to use VBA for the first time. I have several excel files with information in a specific sheet ("sheet 2") under specific columns (Q:AG). I want to create a VBA macro in a separate excel file that gather all this information in a table basically; in order to avoid waste of time in manual copy paste work. How hard is it to get started and form such a macro for a rookie? I'd appreciate some guidance

 

[wrightyrx7]

Hi,

I am about to use VBA for the first time. I have several excel files with information in a specific sheet ("sheet 2") under specific columns (Q:AG). I want to create a VBA macro in a separate excel file that gather all this information in a table basically; in order to avoid waste of time in manual copy paste work. How hard is it to get started and form such a macro for a rookie? I'd appreciate some guidance

Are all the files in the same folder?
Is there any other excel files in this folder where you do NOT want to copy the data?
Is the name of the worksheet the same in every Workbook?

 

[Camel123]

Hello again wrightyrx!

1. Yes.
2. No.
3. No.

 

[wrightyrx7]

Hello again wrightyrx!

1. Yes.
2. No.
3. No.

So 3 is going to be the problem. We can code it to loop through each of the Excel Workbooks in a folder but we need a way to determine which worksheets to copy the data from. If the worksheet names where the same that would of made this a lot easier.

You could either open each of the workbook and put some sort of word or special character in the worksheet name of the one that needs copying and make it the same in all workbooks. Then the code could look for this word or character when it opens the workbook.

Or you could create a list with all the workbooks in one column and the worksheet name

Workbook	Worksheet name
Workbook 1.xlsm	Customers
Workbook 2.xlsm	Sales
Workbook 3.xlsm	Cars

<colgroup><col><col></colgroup><tbody>
</tbody>

And we could get the code to go through this list.

 

[Camel123]

I think, in order to make it easier, I could name the sheets the same in all excels. I am aware of which one is which anyway, the only problem is if someone else looks into the files but no one else than me should have interest in those so.