How to Cycle through 2 different workbooks and adding row 2newWB

[Closed]
Report
Posts
4
Registration date
Thursday October 16, 2014
Status
Member
Last seen
November 14, 2014
-
Posts
4
Registration date
Thursday October 16, 2014
Status
Member
Last seen
November 14, 2014
-
Hi all

I am totally new to this forum posting. So please overlook any mistakes. First let me apologize as my first post was not per forum guideline and was thankfully made aware of that.
I am a hobbyist program writer, which is to say I try to write program for my use and what looks fun to me. This program/macro I am writing is also for my personal use but related to my work.
May be someone can help me.
For my day to day work I get large data from server. I extract it into many different workbooks related to set criteria.

I am pretty familiar with cycling through rows and column and compare and extract data in a single work book and sheet however no idea how to cycle through sheets or finding end of sheets, when to stop or let along cycling through 3 different workbooks and multiple sheets.
My problem is as follows:

I have 2 workbooks customer and Item (all the names are created as an example)

customer WB has 1 sheet named custname which has a list of customer's name in column A (I. E. John, Tom, James, Steve) Column B and C has middle and Last name and Column D has Customer ID (I. E. jba01245789, bsg1245789, etc), column E is the Item limit column. It is either empty or 99999 which indicate it has no limit or has a limit value.

Item WB has multiple worksheets named as (numbered from 1 to let's say 9999 + item name) (I.E. 1 potato, 2 Wheel of Car, 3 TOP coat, etc), with multiple rows of data.

1. I would like to create a new workbook with sheets named after each customer ID.
Which is something like (just an example)

For i = 2 To finalrow
Sheets.Add After:=Sheets(Sheets.Count)
Set ws = ActiveSheet ` declared as worksheet before
ws.Name = i name ` which has the value of cust ID
Next i
2. Cycle through each item sheet and add (Cut and paste from Item sheet to one of the customer sheet) entire row to the customer ID sheet.

Or vice versa

Cycle through each customer ID sheets and adding (Cut and paste) rows from one of the each item sheets to the customers sheet.


3. Each customer has a set number of total items they can have (I.E. Tom can have only 50 item, John can have 10, Etc.)

4. Do this until all the item sheets rows are empty or the limit for the item of the customer is reached.


Thank you all in advance for the help.

2 replies

Posts
4
Registration date
Thursday October 16, 2014
Status
Member
Last seen
November 14, 2014

Solved by me.
Posts
4
Registration date
Thursday October 16, 2014
Status
Member
Last seen
November 14, 2014

Solved myself.