VBA to automatically copy paste the specific data from over 10 excel files based off fixed column range (or date) and key figures from various columns

georgejay

New Member
Joined
Dec 14, 2020
Messages
1
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Hello Everyone!

I have to copy data from 10+ workbooks and paste it into a master workbook. I need to first of all create the layout like this for the master workbook


VersionCodeKey Figure202004202005202006202007202008202009202010202011202012202101202102202103202104202105202106202107202108202109202110202111202112202201202202202203

Then, all the workbooks are located in a folder on my desktop: D:\Desktop\Macros 2\psi flash file

All the workbooks contain a sheet named 'model koro', I have to open each workbook, go to sheet 'model koro’, filter the column B to "Curr. Flash" and go to column F to filter it to "Sell Out (Market)" & "Sell Out (disty)" & "Channel Inv (excl.disty)" & "Channel Inv (inc.disty)" and then select columns range B to BO (hide the columns anything ealier than 202004) starting from row 11 to row ‘x'(the last row can vary in each workbook), ctrl+v (copy), and paste the data range into master worksheet. In the master worksheet, I paste the data in Columns A to AA and continue pasting/appending the data as I copy data from more workbooks. Eventually, the master workbook has the data in columns A to AA from every workbook in one sheet.

The columns range remains constant in all the sheets (model koro ) of every workbook. Each workbook contains multiple sheets, but I am only interested in sheet ‘model koro’

I have to repeat the same steps for 10-30 workbooks and continue pasting/appending the data into master sheet. So, I was wondering if someone could please help me to create a VBA code for this? I'm really new to VBA and would really appreciate your help!

Please let me know if you require any clarification.

Many thanks! =)
 

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Watch MrExcel Video

Forum statistics

Threads
1,123,281
Messages
5,600,720
Members
414,401
Latest member
grenona2020

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