VB Code to copy and paste range to certain sheet based on cell Value

Hello:

Please refer to attached file.

I have payroll data as shown in sheet "Payroll" in Range ("A2:H20") where Column H is SToreName.

I need VB Code to perform following.

Go thru loop and need to copy and paste
range A:H at cell A21, then the result generated in range A27:M41 needs to be pasted at Sheet named in cell Column H at first available cell in column A.

I have manually performed this as follows.
Copied A2:H2 and pasted at cell A21, this generated data in Range A27:M41.
Since H2 is "LJ" Then i copied this range in sheet "LJ" at A1.

Copied A3:H3 and pasted at cell A21, this generated data in Range A27:M41.
Since H3 is "LJ" Then i copied this range in sheet "LJ"at A16 (This was 1st available blank cell in column A).

Copied A4:H4 and pasted at cell A21, this generated data in Range A27:M41.
Since H4 is "WE" Then i copied this range in sheet "WE"at A1 (This was 1st available blank cell in column A).

Copied A5:H5 and pasted at cell A21, this generated data in Range A27:M41.
Since H5 is "LJ" Then i copied this range in sheet "LJ"at A31 (This was 1st available blank cell in column A).


Let me know if you have any questions.
Thanks.

Riz

Hello JLGWHiz:

Thanks a lot, It works great.
Would it be possible to go one step further and copy each store data (whole sheet by selecting Ctrl+A )in text file and store at C:\Temp\ with FileName :StoreName.iif

In this case i will have 2 files saved at C:\Temp\LJ.iif and C:\Temp\WE.iif

Once again thanks a lot for great help.

Riz