Here at my work we have multiple copy machines, with each employee having their own copy code that tracks copies so that the departments can than be properly charged. These copy machines will run a report each month listing all copy codes regardless if that copy code made any copies. Therefore, what I am wanting to do is combine all these copy reports into one workbook, with each having their own worksheet. So COPY-DUPCTR2 will have its own worksheet, COPY-FAC1 will be its own sheet, COPY-FAC2 will be its own sheet, etc. There will be a total of 13 different sheets within the workbook when all said and done.
Within this workbook I'll have one Report sheet that will have a macro button that will scan each of the above worksheets and extract only those copy codes that were used along with the number of copies and than assign the values from the copy monthly report sheets to the proper cells in the Report Sheet.
Right now i'm just working with one monthly report sheet just to figure out the proper function and logic in achieving the desired results.
The first column in the monthly report lists all the copy codes and therefore will not be blank. So with that in mind I thought it would be best to use the IsEmpty on a do while loop for this column:
Do While IsEmpty(cpy4Ad.Range("A2"))
Don't even know if this is right, I just want the Do While to run through all cells in column A starting at row 2.
The issue i'm having is how best to go about extracting the info from the monthly report and applying the value to the report sheet in the proper cell and than moving to the next row on the report sheet. I don't know VB well. Last time I written in VB was over 12 years ago. My primary knowledge is in C/C++ and Web development.
I've attached my code below.
Private Sub CommandButton1_Click()
Dim cpyRpt As Worksheet, cpy4Ad As Worksheet
Set cpyRpt = Sheets("Copy-Report")
Set cpy4Ad = Sheets("COPY-4thAdmin")
Do While IsEmpty(cpy4Ad.Range("A1"))
'if current row, column F in cpy4Ad != 0 OR != BLANK
'assign cells in currnt row from columns A and F in cpy4Ad to cpyRpt (currnt row (row will be different from cpy4Ad), columns A and W)
'move to next row in Report Sheet
Once i get this portion figured out I will than need to figure out how to move from one sheet to another once the do while loop reaches the end of each report, I figure this could be done either with a for loop or two do while loops...BUT that's later down the road. I just want to get this function running first before moving on.
I've also attached the workbook.
Any help would be great.