• 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.

Consolidate Data from multiple files

A

Abhijeet

Active Member
Hi
I have this code but this not work

Public strFileName As String
Public currentWB As Workbook
Public dataWB As Workbook
Public strCopyRange As String

Sub GetData()
Dim strWhereToCopy As String, strStartCellColName As String
Dim strListSheet As String
Dim strCopySheet As String

strListSheet = “List”

On Error GoTo ErrH
Sheets(strListSheet).Select
Range(“B2?).Select

‘this is the main loop, we will open the files one by one and copy their data into the masterdata sheet
Set currentWB = ActiveWorkbook
Do While ActiveCell.Value “”

strFileName = ActiveCell.Offset(0, 1) & ActiveCell.Value
strCopyRange = ActiveCell.Offset(0, 2) & “:” & ActiveCell.Offset(0, 3)
strWhereToCopy = ActiveCell.Offset(0, 4).Value
strCopySheet = ActiveCell.Offset(0, 6).Value
strStartCellColName = Mid(ActiveCell.Offset(0, 5), 2, 1)

Application.Workbooks.Open strFileName, UpdateLinks:=False, ReadOnly:=True
Set dataWB = ActiveWorkbook

Sheets(strCopySheet).Select
Range(strCopyRange).Select
Selection.Copy

currentWB.Activate
Sheets(strWhereToCopy).Select
lastRow = LastRowInOneColumn(strStartCellColName)
Cells(lastRow + 1, 1).Select

Selection.PasteSpecial xlPasteValues, xlPasteSpecialOperationNone
Application.CutCopyMode = False
dataWB.Close False
Sheets(strListSheet).Select
ActiveCell.Offset(1, 0).Select
Loop
Exit Sub

ErrH:
MsgBox “It seems some file was missing. The data copy operation is not complete.”
Exit Sub
End Sub

Public Function LastRowInOneColumn(col)
‘Find the last used row in a Column: column A in this example
Dim lastRow As Long
With ActiveSheet
lastRow = .Cells(.Rows.Count, col).End(xlUp).Row
End With
LastRowInOneColumn = lastRow
End Function
 
@Abhijeet
Hi!
Thanks for the information. So we'll have to report it to Ron De Bruin, that one of the most frequented pages and add-ins for Excel regarding mails doesn't work... o_O Luckily you noticed it and told us about. :rolleyes:
Regards!
 
SirJB7 said:
@Abhijeet
Hi!
Thanks for the information. So we'll have to report it to Ron De Bruin, that one of the most frequented pages and add-ins for Excel regarding mails doesn't work... o_O Luckily you noticed it and told us about. :rolleyes:
Regards!
Click to expand...
Abhijeet
Here is a macro which I use almost everyday to consolidate data from different files. This macro brings data from "Sheet1" only. If your requirement is different, please let us know.This is my first macro.

With Regards
Rudra
 

Attachments

  • Consolidate data - creates a different workook - without overflow error.xlsm
    48.9 KB · Views: 16
@murugan_gv
Hi thank you very much for this link..I was not aware about this addin.It has got everything you need while combining your data...
Thanks guru, you made my day.

Regards
Rudra
 
@rumshar
Hi!
I'm very sorry to inform you if we rely and and trust on @Abhijeet comment the macro that you use almost everyday it doesn't work. o_O But don't worry too much about that, as Ron de Bruin's one used by thousands of people everyday it doesn't work neither... What fortunate that we are to have discovered those incredible two facts, aren't we? :rolleyes:
Regards!
PS: Incredible or unreal? ;)
 
@rumshar & Abhijeet,

Please let me know the below points:
1) which version of excel you are using
2) which version of Add-in you are downloading
3) In which folder you are saving this Add-in?
4) After saving it, are you activating this add-in by restarting your excel workbook?

Thanks
PS: I use this everyday.
 
murugan_gv said:
@rumshar & Abhijeet,

Please let me know the below points:
1) which version of excel you are using
2) which version of Add-in you are downloading
3) In which folder you are saving this Add-in?
4) After saving it, are you activating this add-in by restarting your excel workbook?

Thanks
PS: I use this everyday.
Click to expand...
@murugan_gv
I have got no issue with this addin....I am very happy to get it through you...Infact I have already started using it(successfully) and have made it installed in all the systems of my subordinates.
@SirJB7 thanks for your words, indeed we are very fortunate to know these facts through him.
@Abhijeet I agree that my macro may not work for you but after using and seeing Ron De Bruin's addin, I am in love with it. If someone says it is not working, it is kinda 8th wonder(for me).

With Regards
Rudra
 
Last edited:
@rumshar
Hi!
You're in safe land again, lucky guy. You can be sure again that your widely and daily used Add-In works fine, Abhijeet has confirmed it. :)
I'm still concerned about Ron de Bruin's fate... very much concerned, poor Ron. :(
Regards!
 
You must log in or register to reply here.
Back
Top