• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Copy data from multiple workbooks into summary tool workbook

Thomas Kuriakose

Active Member
Respected Sirs,

We have 70 to 80 workbooks in identical format for tools database recording. The users have filled up the required details and sent for consolidation.

We are doing this manually and this is taking a lot of time with errors and rework.

Kindly help with a code to open different workbooks and copy the data which are filled (Column B to I) to a summary tool workbook.

Kindly find attached the workbooks for your perusal.

Thank you so much,

with regards,
thomas
 

Attachments

  • P1.xlsx
    946.9 KB · Views: 7
  • Tool Summary.xlsx
    9.9 KB · Views: 7
Here for testing.
1) You have to write list of You files to 'Sheet2', check sample
2) Press Sheet1!A1 -cells button
 

Attachments

  • Tool Summary.xlsm
    27.4 KB · Views: 11
Respected Sir,

Thank you very much for this code.

One request, this is copying all values. We need to copy only cells A:I which have input value, the empty cells can be omitted.

Also, is it possible to code to select the file location and not enter in Sheet 2 the details, so we get prompt for select file and we browse the folder and open the file to start copy.

Thank you so much,

with regards,
thomas
 

Hi Thomas !

Initial post should be crytal clear with a couple of database workbooks
and a summary workbook filled accordingly
with detail of database files path …
 
@Thomas Kuriakose
reply#4 Those file-names with path should write below of text files (ver1).
I'll change this to Your layout (the newest version).
I add one more option; Sheet2!A:A - copy.
You gotta mark those files that You want to copy.
If You really want to manually select every file every time
... I can make that too.
You wrote in reply#3, that copy only those cells...
Now, it copies all rows all values, except the last row.
If You can tell which rows not to copy, I can notice that too.
 

Attachments

  • Tool Summary.xlsm
    39.4 KB · Views: 6
Respected Sirs,

Apologies for not having explained my requirement clearly.

The problem with the workbooks is each work book is more than 6 MB and could not upload the same.

What is required from our end is as follows -

On clicking the button,

(1) it should open the directory to search for files with tools database
(2) Copy all non blank fields from column B to I entered in each database and paste to Tools summary workbook one after the other till all workbooks are copied.

Please find attached the desired result and the
 

Attachments

  • P1.xlsx
    320.2 KB · Views: 6
  • Tool Summary.xlsm
    25 KB · Views: 6
@Thomas Kuriakose ... 6MB per workbook. Okay.
... now, one copy per workbook ... soon it will copy packets ... Okay?
... now, it copies one workbook in time, after it's ready, it'll work with next workbook. So, that 'Sheet2' -list works better, if You have to do copy many times and You can select which files You want to copy. ... or You have to select every time manually each workbook! Are files names fixed or what ever?
... now. column A is same like workbook column A.
I can modify column A to show number of rows has copied from workbook. Every workbook starts from value 1 ...
I'll send next version, asap I'll get answers or I've made next 'have to do' modifications.
 
Respected Sir,

Yes Sir, it should copy one workbook data and then move to next workbook

Kindly provide both options.

(1) List of files in Sheet 2 and
(2) Select file manually

All file names are different

yes, kindly number the column A based on number of rows copied.

Thank you so much,

with regards,
thomas
 
next version:
1) packet copy
2) list/manual fileselection
3) A-col
... for testing
 

Attachments

  • Tool Summary.xlsm
    33.7 KB · Views: 6
Respected Sir,

I tried and it is copying but the whole data for P1 is getting copied. We need only the cells with data to be copied. Currently it is copying all the data for P1.

Thanks,

with regards,
thomas
 
@Thomas Kuriakose
Your previous P1-file has 105rows of data, am I right?
All of those rows has something in column , am I right?
Now, before copy, the code will check from row 2 to 106,
... if row's column has something, that row will copy. Is this right?
... or if You want, that all cells from to have to have something, then eight rows will copy.
Which do You want, 105 or 8 rows?
 
Respected Sir,

For all the files it should copy rows if there is data from Column B to I, if there is no data from Column B to I it should not copy that row.

Thank you so much.

with regards,
thomas
 
So, the answer is 8 rows;
all row's cells have to have data from to .
I'll do it as soon as I'll have time.
 
Thomas,
even from your last attachment I can't understand your summary
starting with D SHACKLE item (source row #49)
but source rows #3, 4 & 6 (ANGLE GRINDER and CHAIN BLOCK items)
have all their cells filled except for picture but not appear in summary :
so, what are exactly the criterias to copy ?‼

In summary there is a picture column, don't want to paste any picture ?

All worksheets from any source workbook have filters on columns
like your attachment ?

Where are source workbooks ? All in same folder ? Always same folder ?
All workbooks in the folder must be treated ? …

When need is not clear, I do not write a single codeline
even it's easy like yours with an advanced filter
(as you can start your own code using Macro recorder !) …

From a crystal clear initial post with accurate attachment,
you should have a solution from post #2 !
 
@Thomas Kuriakose Here the newest version, less rows!
@Marc L He haven't wanted pictures. Q: Any folder, Reply #4.
I think that preset files should be useful, if have to do that many times.
Now he will get less rows; reply #12 & #13.
 

Attachments

  • Tool Summary.xlsm
    35.6 KB · Views: 2

My thought was based upon his last attachment :
summary do not exactly reflect source data !

It seems to need a tiny code (max 50 codelines)
using filters or an advanced filter …
 
Respected Sirs,

Apologies again if I have not explained my requirement clearly.

Here is the step by step requirement.

1. The source workbooks are sent by email and will be saved in one folder.
2. The summary workbook will also be in the folder where the source workbooks are saved.
3. The criteria for copying data are as follows -
(I) Open source workbook, check for tools data values in cells from column B to I in each source book.
(ii) Copy values from B to I and paste in summary workbook from column B to I
(III) Skip rows which do not have cell values filled from column D to I
(iv) The S.No (column A) should be filled with auto numbers starting from 1.
(v) After the last copied data from one source workbook in the summary workbook, the next row in summary workbook should be data from second source workbook and this should continue for 3rd 4th until 80th source workbook.
(vi) As a first step (Data to be copied only) the picture need not be copied from the source to the summary workbook. The pictures will be pasted manually for now.

Kindly check whether this clarifies the sequence and expected result for the code.

Thank you so much,

with regards,
thomas
 
This is how the initial post should be !

I already have a tiny code but criteria III columns D to I means :

1) all cells must be filled to copy row ?

2) at least one cell must be filled to copy row ?
 
Paste this demonstration to summary workbook :​
Code:
Sub Demo()
            Dim Rg As Range
With Sheet1
            Set Rg = .Cells(.Rows.Count, 2)
    With .UsedRange.Rows
        If .Count > 1 Then .Item("2:" & .Count).Clear
    End With
End With
            Application.ScreenUpdating = False
      F$ = Dir$(ThisWorkbook.Path & "\*.xlsx")
While F > ""
    With GetObject(ThisWorkbook.Path & "\" & F).Worksheets(1)
        .[P2].Formula = "=COUNTA(D2:I2)>0"
        .Cells(1).CurrentRegion.AdvancedFilter xlFilterInPlace, .[P1:P2]
        With .[_FilterDatabase].Rows
          If .Parent.Evaluate("SUBTOTAL(103," & .Columns(2).Address & ")") > 1 Then _
             .Item("2:" & .Count).Columns("B:I").Copy Rg.End(xlUp)(2)
        End With
            .Parent.Close False
    End With
      F = Dir
Wend
 Set Rg = Sheet1.Cells(1).CurrentRegion.Rows
With Rg.Item("2:" & Rg.Count).Columns(1)
    .HorizontalAlignment = xlCenter
    .Value = Evaluate("ROW(1:" & Rg.Count - 1 & ")")
End With
     Rg.Borders.ColorIndex = xlAutomatic
 Set Rg = Nothing
            Application.ScreenUpdating = True
End Sub
You may Like it !
 
@Thomas Kuriakose cause Reply#22
It was 'have to col-B filled' -> not good?
next 'have to all cols B-I filled' -> not good?
next 'have to cols B & C filled' -> not good?
now back to 'have to col-B filled' -> what next?
 

Thomas, in case it's not the expected result with my demonstration,
just explain which B to I columns must be filled and which could be blank
to allow a source row to be pasted in summary workbook …

 
Back
Top