VBA WS Name from Cell, Loop Copy/Paste

Small Paul

Board Regular
Joined
Jun 28, 2018
Messages
118
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

Board Regular
Joined
Jun 28, 2018
Messages
118
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.
 

Forum statistics

Threads
1,085,542
Messages
5,384,330
Members
401,887
Latest member
Somesh

Some videos you may like

This Week's Hot Topics

Top