asked on

extracting data from multiple excel sheets into one sheet

Hello All,

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"))

Open in new window

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"))
        'Pseudo code
        '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
    Loop
End Sub

Open in new window

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.
Copy-report---johnsmith.xlsm

SANTABABY

Please try the following code (replace existing code in your copy-report sheet).
I made a small change in your woeksheet : split the exiting merged column COPY-DUPCTR2 (4 columns) into COPY-DUPCTR2-BW (2 columns) COPY-DUPCTR2-COLOR (2 columns)

Const Rptstartrow = 6
Const Rptcopierrow = 3
Const Rptuseridcol = 1
Const Datastartrow = 2
Const startcopiercol = 5
Const copiercolwidth = 2

Private Sub CommandButton1_Click()
Dim cpyRpt As Worksheet
Dim ws As Worksheet
Dim crow As Long
Dim lrow As Long
Dim ccol As Long
Dim i As Long
Dim fnd As Range

Set cpyRpt = ActiveWorkbook.Worksheets("Copy-Report")

'Set cpy4Ad = Sheets("COPY-4thAdmin")

lrow = Rptstartrow - 1

For Each ws In ActiveWorkbook.Worksheets

Set fnd = cpyRpt.Rows(Rptcopierrow).Find(What:=ws.Name)

'Set fnd = cpyRpt.Rows(Rptcopierrow).Find(What:=ws.Name, After:=ActiveCell, LookIn:=xlFormulas _
, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)

If Not fnd Is Nothing Then
ccol = fnd.Column
i = Datastartrow
While Not IsEmpty(ws.Cells(i, 1))
If Not IsEmpty(ws.Cells(i, 6)) And ws.Cells(i, 6) > 0 Then
Set fnd = cpyRpt.Columns(Rptuseridcol).Find(What:=ws.Cells(i, 1))
'Set fnd = cpyRpt.Columns(Rptuseridcol).Find(What:=ws.Cells(i, 1), After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase _
:=False, SearchFormat:=False)
If Not fnd Is Nothing Then
crow = fnd.Row
Else
lrow = lrow + 1
crow = lrow
cpyRpt.Cells(crow, 1) = ws.Cells(i, 1)
End If
cpyRpt.Cells(crow, ccol) = ws.Cells(i, 6)
End If
i = i + 1
Wend
End If
Next ws

'Do While Not IsEmpty(cpy4Ad.Range("A"))
'Pseudo code
'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
'Loop
End Sub

Morgan

ASKER

Hello Santababy,

I made the changes and copied the code, but nothing happens after running it.

SANTABABY

Please see the attached workbook which contains your data and updated code (same as above).
Please let me know if you still have problems.
Copy-report---johnsmith.xlsm

Morgan

ASKER

SanatBaby:

Thank you. The only issue, or rather piece missing, is the name is not moving over to the Report. I get the copy code and the number of copies but the Name of the individual assigned to the copy code - i.e. John Smith - needs to move to the copy report as well.

Thanks,

neo

ASKER CERTIFIED SOLUTION

SANTABABY

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Morgan

ASKER

Thank you!

Now onto figuring out the next logic step in going through one sheet,once finished with that one moving onto the next.

Thanks!

SANTABABY

I'm not sure whether I understand your comment.
The current code processes all worksheets in your workbook and imports data from any worksheet when the name of corresponding worksheet appears in row 3 of your Copy-Report sheet.

If you have any other needs, please create a new question and post a link here so that I can find it easily.

Morgan

ASKER

Hello Santababy,

Because you has asked here is the link to another quetions revolving this one. Your code works great, now i just need to extend on it a bit. Your help will be appreciated.

https://www.experts-exchange.com/questions/27862715/inputting-data-from-multiple-worksheets-and-calculating-costs-of-the-results.html

Thanks,

neo