VBA WS Name from Cell, Loop Copy/Paste

[Small Paul]

Hi
I have a worksheet, “Required Data”, which has data in columns A:M. In column A I have a list of numbers which are derived from criteria in the worksheeet. Starting at cell A2 (A1 is a header) this presently runs 1, 2, 2, 2, 2, 3, 4, 4, 5 etc to 76. Each of these values have a corresponding worksheet (1:76) in the workbook.

I need to have a macro which will:

1. Find the value in the ws.(“Required Data”).Range(“A2”)
2. Copy selected data from that row
3. Locate the corresponding worksheet “1”
4. Paste the data

I have looked extensively for a method of doing this without success. I have also looked at reversing the search i.e. starting with the worksheet name and finding the value in the “Required Data” worksheet.

Is there a method to do this?

One additional problem is that in each sheet (1:76) is a set of headers (B7:B9) which are added by vba. A key ‘driver’ is the data held in column D, of the “Required Data” sheet, which needs to be pasted into cell C8. There are instances where this changes for each worksheet. For example, value/worksheet 2 has 3 different ‘definitions’ in the ‘Required Data’ sheet. Each time this happens, B7:K9 (headers) needs to be copied and pasted 3 rows below the last entry!

“Required Data” Cell	“Required Data” Cell Value	“Required Data” Criteria Cell	“Required Data” Required Data Value	Macro Requirement
A2	1	D2	1924328633	Copy “Required Data” fields and paste in worksheet 1, range(“B10:K10”)
A3	2	D3	1919505427	Copy “Required Data” fields and paste in worksheet 2, range(“B10:K10”)
A4	2	D4	1919505427	Copy “Required Data” fields and paste in worksheet 2, range(“B11:K11”)
A5	2	D5	1919505690	Copy worksheet 2, range “B7:K9”, paste in range “B14:K16”. Copy “Required Data” range and paste in worksheet 2, range “B17:K17”
A6	2	D6	1924328633	Copy worksheet 2, range “B7:K9”, paste in range “B20:K22”. Copy “Required Data” range and paste in worksheet 2, range “B23:K23”
A7	3	D7	1924328633	Copy “Required Data” fields and paste in worksheet 3, range(“B10:K10”)
A8	4	D8	1728687622	Copy “Required Data” fields and paste in worksheet 4, range(“B10:K10”)
A9	4	D9	1728687622	Copy worksheet 4, range “B7:K9”, paste in range “B13:K13”. Copy “Required Data” range and paste in worksheet 2, range “B14:K14”
A10	5	D10	1919505690	Copy “Required Data” fields and paste in worksheet 5, range(“B10:K10”)

<tbody>
</tbody>

The process then needs to repeat for each value from 1:76 (in this instance).

Any guidance (on any of the above) would be extremely welcome.
Small Paul.

 

[Small Paul]

Hi

To put the above in context, below is a Dropbox link to the file in question:

https://www.dropbox.com/s/cy0ds8gmzhchpfw/Commission Statements.xlsm?dl=0

Each row on the "Required Data" tab has a number on the left. Each number has a corresponding worksheet. I need to (in the next instance) copy data from the Required Data sheet and paste it in the corresponding numbered sheet.

If anyone can help it would really be appreciated.

Many thanks
Small Paul.