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

I

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
 
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
953
smozgur
smozgur
D
  • Locked
  • Question Question
VBA to copy only matching data from 2 csv workbooks into the "Master Workbook"
Replies
1
Views
392
RoryA
RoryA
M
  • Solved
Help with separating Text String from a Excel Col
Replies
3
Views
642
Peter_SSs
Peter_SSs
P
  • Question Question
Copy data from one table to multiple table.
Replies
0
Views
506
patapsychonaute
P
M
  • Question Question
VBA code that pulls the data from the workbook to another workbook
Replies
5
Views
479
MMM_84
M

Share this page

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