VBA - macro, help

Cameltoe

Board Regular
Joined
Jun 5, 2018
Messages
185
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 :)
 

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

wrightyrx7

Well-known Member
Joined
Sep 15, 2011
Messages
994
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?
 

wrightyrx7

Well-known Member
Joined
Sep 15, 2011
Messages
994
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

WorkbookWorksheet name
Workbook 1.xlsmCustomers
Workbook 2.xlsmSales
Workbook 3.xlsmCars

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


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

Cameltoe

Board Regular
Joined
Jun 5, 2018
Messages
185
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. :)
 

Watch MrExcel Video

Forum statistics

Threads
1,122,840
Messages
5,598,386
Members
414,234
Latest member
grlevesq

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top