Opening a MasterData File Read Only and Lifting Data from that into a Table into a Current Workbook using VBA Code

Ibbo1978

New Member
Joined
Mar 6, 2017
Messages
20
Hi All,

  • I have a Master Data Table, this has a huge amount of info across 500 products
  • I also have a Daily workbook that may use 50 Products and only a number of the columns of data
  • I want to ensure that the data in my Daily workbook is exactly the same as Master Data Table.
  • So when I open Daily Workbook I want to open the Master Data table read only as many other files may do the same thing concurrently.
  • I then want to lift specific data from the Master Data table that satisfy the column headers within my daily workbook.

Master Data


[TABLE="width: 450"]
<colgroup><col width="140" style="width: 84pt; mso-width-source: userset; mso-width-alt: 4096;"> <col width="108" style="width: 65pt; mso-width-source: userset; mso-width-alt: 3145;"> <col width="120" style="width: 72pt; mso-width-source: userset; mso-width-alt: 3510;"> <col width="71" style="width: 43pt; mso-width-source: userset; mso-width-alt: 2084;" span="2"> <col width="75" style="width: 45pt; mso-width-source: userset; mso-width-alt: 2194;"> <col width="75" style="width: 45pt; mso-width-source: userset; mso-width-alt: 2194;"> <col width="89" style="width: 53pt; mso-width-source: userset; mso-width-alt: 2596;"> <tbody>[TR]
[TD="class: xl147, width: 140, bgcolor: aqua"]PRODUCT CODE[/TD]
[TD="class: xl148, width: 108, bgcolor: aqua"]Customer Code[/TD]
[TD="class: xl149, width: 120, bgcolor: aqua"]Work Centre[/TD]
[TD="class: xl150, width: 71, bgcolor: aqua"]Packs / Hour[/TD]
[TD="class: xl150, width: 71, bgcolor: aqua"]Packs / Case[/TD]
[TD="class: xl151, width: 75, bgcolor: aqua"]Cases / Hour[/TD]
[TD="class: xl149, width: 75, bgcolor: aqua"]Eff'cy %[/TD]
[TD="class: xl152, width: 89, bgcolor: aqua"]Plan Rate[/TD]
[/TR]
[TR]
[TD="class: xl153, bgcolor: white"]P CODE[/TD]
[TD="class: xl154, bgcolor: white"]CUST[/TD]
[TD="class: xl154, bgcolor: white"]W/C[/TD]
[TD="class: xl154, bgcolor: white"]PPH[/TD]
[TD="class: xl154, bgcolor: white"]PPC[/TD]
[TD="class: xl155, bgcolor: white"]RATE[/TD]
[TD="class: xl154, bgcolor: white"]EFF[/TD]
[TD="class: xl155, bgcolor: white"]PRATE[/TD]
[/TR]
[TR]
[TD="class: xl157, bgcolor: yellow"]CQ6762[/TD]
[TD="class: xl156, bgcolor: yellow"]Ct[/TD]
[TD="class: xl160, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl161, bgcolor: yellow, align: right"]1,650[/TD]
[TD="class: xl158, bgcolor: transparent, align: right"]12[/TD]
[TD="class: xl159, bgcolor: yellow, align: right"]138[/TD]
[TD="class: xl160, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl161, bgcolor: yellow, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl157, bgcolor: yellow"]CT6762[/TD]
[TD="class: xl156, bgcolor: yellow"]Ct[/TD]
[TD="class: xl160, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl161, bgcolor: yellow, align: right"]2,475[/TD]
[TD="class: xl158, bgcolor: transparent, align: right"]12[/TD]
[TD="class: xl159, bgcolor: yellow, align: right"]206[/TD]
[TD="class: xl160, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl161, bgcolor: yellow, align: right"]2[/TD]
[/TR]
[TR]
[TD="class: xl157, bgcolor: yellow"]IC10008[/TD]
[TD="class: xl156, bgcolor: yellow"]Ic[/TD]
[TD="class: xl158, bgcolor: transparent, align: right"]2[/TD]
[TD="class: xl161, bgcolor: yellow, align: right"]2,200[/TD]
[TD="class: xl158, bgcolor: transparent, align: right"]10[/TD]
[TD="class: xl159, bgcolor: yellow, align: right"]220[/TD]
[TD="class: xl160, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl161, bgcolor: yellow, align: right"]2[/TD]
[/TR]
[TR]
[TD="class: xl157, bgcolor: yellow"]IC10009[/TD]
[TD="class: xl156, bgcolor: yellow"]Ic[/TD]
[TD="class: xl158, bgcolor: transparent, align: right"]2[/TD]
[TD="class: xl161, bgcolor: yellow, align: right"]2,200[/TD]
[TD="class: xl158, bgcolor: transparent, align: right"]10[/TD]
[TD="class: xl159, bgcolor: yellow, align: right"]220[/TD]
[TD="class: xl160, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl161, bgcolor: yellow, align: right"]2[/TD]
[/TR]
[TR]
[TD="class: xl157, bgcolor: yellow"]KD00051[/TD]
[TD="class: xl156, bgcolor: yellow"]Al[/TD]
[TD="class: xl158, bgcolor: transparent, align: right"]2[/TD]
[TD="class: xl161, bgcolor: yellow, align: right"]2,200[/TD]
[TD="class: xl158, bgcolor: transparent, align: right"]14[/TD]
[TD="class: xl159, bgcolor: yellow, align: right"]157[/TD]
[TD="class: xl160, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl161, bgcolor: yellow, align: right"]2[/TD]
[/TR]
[TR]
[TD="class: xl162, bgcolor: yellow"]KG10042[/TD]
[TD="class: xl156, bgcolor: yellow"]Al[/TD]
[TD="class: xl158, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl161, bgcolor: yellow, align: right"]1,500[/TD]
[TD="class: xl158, bgcolor: transparent, align: right"]12[/TD]
[TD="class: xl159, bgcolor: yellow, align: right"]125[/TD]
[TD="class: xl160, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl161, bgcolor: yellow, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl157, bgcolor: yellow"]KD10042[/TD]
[TD="class: xl156, bgcolor: yellow"]Al[/TD]
[TD="class: xl158, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl161, bgcolor: yellow, align: right"]1,500[/TD]
[TD="class: xl158, bgcolor: transparent, align: right"]12[/TD]
[TD="class: xl159, bgcolor: yellow, align: right"]125[/TD]
[TD="class: xl160, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl161, bgcolor: yellow, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl162, bgcolor: yellow"]KD10045[/TD]
[TD="class: xl156, bgcolor: yellow"]Al[/TD]
[TD="class: xl158, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl161, bgcolor: yellow, align: right"]1,500[/TD]
[TD="class: xl158, bgcolor: transparent, align: right"]12[/TD]
[TD="class: xl159, bgcolor: yellow, align: right"]125[/TD]
[TD="class: xl160, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl161, bgcolor: yellow, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl157, bgcolor: yellow"]KD10065[/TD]
[TD="class: xl156, bgcolor: yellow"]Al[/TD]
[TD="class: xl158, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl161, bgcolor: yellow, align: right"]1,500[/TD]
[TD="class: xl158, bgcolor: transparent, align: right"]12[/TD]
[TD="class: xl159, bgcolor: yellow, align: right"]125[/TD]
[TD="class: xl160, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl161, bgcolor: yellow, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl157, bgcolor: yellow"]KD10066[/TD]
[TD="class: xl156, bgcolor: yellow"]Al[/TD]
[TD="class: xl158, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl161, bgcolor: yellow, align: right"]1,500[/TD]
[TD="class: xl158, bgcolor: transparent, align: right"]12[/TD]
[TD="class: xl159, bgcolor: yellow, align: right"]125[/TD]
[TD="class: xl160, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl161, bgcolor: yellow, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl157, bgcolor: yellow"]KD90014[/TD]
[TD="class: xl156, bgcolor: yellow"]Al[/TD]
[TD="class: xl158, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl161, bgcolor: yellow, align: right"]1,800[/TD]
[TD="class: xl158, bgcolor: transparent, align: right"]6[/TD]
[TD="class: xl159, bgcolor: yellow, align: right"]300[/TD]
[TD="class: xl160, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl161, bgcolor: yellow, align: right"]3[/TD]
[/TR]
</tbody>[/TABLE]

Daily Workbook


[TABLE="width: 170"]
<colgroup><col width="140" style="width: 84pt; mso-width-source: userset; mso-width-alt: 4096;"> <col width="71" style="width: 43pt; mso-width-source: userset; mso-width-alt: 2084;" span="2"> <tbody>[TR]
[TD="class: xl146, width: 140, bgcolor: aqua"]PRODUCT CODE[/TD]
[TD="class: xl147, width: 71, bgcolor: aqua"]Packs / Hour[/TD]
[TD="class: xl147, width: 71, bgcolor: aqua"]Packs / Case[/TD]
[/TR]
[TR]
[TD="class: xl148, bgcolor: white"]P CODE[/TD]
[TD="class: xl149, bgcolor: white"]PPH[/TD]
[TD="class: xl149, bgcolor: white"]PPC[/TD]
[/TR]
[TR]
[TD="class: xl152, bgcolor: yellow"]KD10042[/TD]
[TD="class: xl151, bgcolor: yellow"] [/TD]
[TD="class: xl150, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl153, bgcolor: yellow"]KD10045[/TD]
[TD="class: xl151, bgcolor: yellow"] [/TD]
[TD="class: xl150, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl152, bgcolor: yellow"]KD10065[/TD]
[TD="class: xl151, bgcolor: yellow"] [/TD]
[TD="class: xl150, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl152, bgcolor: yellow"]KD10066[/TD]
[TD="class: xl151, bgcolor: yellow"] [/TD]
[TD="class: xl150, bgcolor: transparent"] [/TD]
[/TR]
</tbody>[/TABLE]

Hope this explains my requirement, I just need to get started, I think if I had code to open the sheet read only and could fill in the first code efficiently, I presume it would be some sort of loop to continue the process.

Hope you can help Excel Family,

Ibbo
 

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
Back
Top