VBA to distribute data by row from "Master" worksheet into multiple "sub" worksheets

[nklug3]

Hi all,

I work for an oil company and have a master list of all of the leases that we service along with other information specific to each lease. Along with the master lease (which is on one worksheet), I have also created a template for each separate lease using individual worksheets within Excel (each sheet is identical, but will only have information for one oil lease). My goal is to be able to distribute each individual lease's data into a separate worksheet ("lease sheet") template. We use these separate worksheets to store even more data than what is stored on the master. The master sheet looks like this:

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Lease[/TD]
[TD]Company[/TD]
[TD]County[/TD]
[TD]Legal Description[/TD]
[/TR]
[TR]
[TD]X[/TD]
[TD]Kyle Oil[/TD]
[TD]Kit Carson[/TD]
[TD]8979[/TD]
[/TR]
[TR]
[TD]Y[/TD]
[TD]Allen Oil[/TD]
[TD]Kiowa[/TD]
[TD]9899[/TD]
[/TR]
[TR]
[TD]Z[/TD]
[TD]Nick Oil[/TD]
[TD]Kit Carson[/TD]
[TD]1256[/TD]
[/TR]
</tbody>[/TABLE]







Each individual "lease sheet" will look something like this (I used the data from the first row of the "Master" to complete, but for now in my actual workbook all of the filler in Column 2 is blank in each sheet):

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Lease[/TD]
[TD]X[/TD]
[/TR]
[TR]
[TD]Company[/TD]
[TD]Kyle Oil[/TD]
[/TR]
[TR]
[TD]County[/TD]
[TD]Kit Carson[/TD]
[/TR]
[TR]
[TD]Legal Description[/TD]
[TD]8979[/TD]
[/TR]
</tbody>[/TABLE]







Basically it is just re-distributing data to a new worksheet with a different layout.

All of the individual "lease sheets" will be named (X, Y, Z, etc.) so my thoughts are a search function will need to be utilized that will grab the value from A2 (the value in this example being X) above and search the workbook for the sheet with that name, find the sheet, then copy and paste all of the corresponding data that belongs to that lease into the new format. This would then need to repeat for every lease.

I am wanting VBA in order to save myself some time so I do not have to type everything twice. I am a recent-ish hire and am trying to get our company to go paperless - currently everything we do is written by hand and we have over 3,000 leases that we service, which means over 3,000 sheets of paper laying around just asking to be lost. Once I have all of the "Master" data entered, I would like to be able to push one button (i.e. run the macro) and have it distribute all of the data to the "lease sheets" within the workbook.

I hope I didn't make this too confusing. TIA for your help and have a great day.

Nick

 

[MickG]

Try this:-

[COLOR="Navy"]Sub[/COLOR] MG13Sep51
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
Application.ScreenUpdating = False
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    Dn.Resize(, 4).Copy
    Sheets(Dn.Value).Range("B1").PasteSpecial Transpose:=True
[COLOR="Navy"]Next[/COLOR] Dn
Application.ScreenUpdating = True
MsgBox "Update complete!!"
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick