[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 546
  • Last Modified:

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
0
Morgan
Asked:
Morgan
  • 4
  • 4
1 Solution
 
SANTABABYCommented:
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
0
 
MorganAuthor Commented:
Hello Santababy,

I made the changes and copied the code, but nothing happens after running it.
0
 
SANTABABYCommented:
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
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
MorganAuthor Commented:
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
0
 
SANTABABYCommented:
I did not include it as I thought you were going to make changes after the base logic is in place. Anyway, I updated the workbook which copies the names too.
Please note that the name is copied to the report worksheet only once i.e. when the code encounters an userid for the first time in a worksheet. I assume that the names will be same for an userid in all worksheets.
Copy-report---johnsmith.xlsm
0
 
MorganAuthor Commented:
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!
0
 
SANTABABYCommented:
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.
0
 
MorganAuthor Commented:
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.

http://www.experts-exchange.com/Programming/Languages/Visual_Basic/Q_27862715.html

Thanks,

neo
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now