VBA MACRO TO COPY DATA FROM MULTIPLE FILES

[kyajaiinc]

On a daily basis I have to consolidate data from different workbooks into a master sheet. I am looking for a macro that can automate this task. I found some code on this site which is ideal, however when I run it it is not copying any data. I am new to VBA and I cannot find out what is going wrong. I have included the code for ease of reference.


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


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

strStartCellColName = Mid(ActiveCell.Offset(0, 5), 2, 1)


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

Set dataWB = ActiveWorkbook


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

'http://www.rondebruin.nl/last.htm

Dim lastRow As Long

With ActiveSheet

lastRow = .Cells(.Rows.Count, col).End(xlUp).Row

End With

LastRowInOneColumn = lastRow

End Function

 

[naveed raza]

If u provide sample workbook and your criteria i mean to say from where u want to copy and which columns

 

[SirJB7]

Hi, kyajaiinc!

As a new user you might want (I'd say should and must) read this:
http://chandoo.org/forum/forums/new-users-please-start-here.14/

And regarding your issue, if you haven't performed yet the search herein, try going to the topmost right zone of this page (Search...), type the keywords used in the title when creating the topic or other proper words and press Search button. You'd retrieve many links from this website, as this is a widely posted question, maybe you find useful information and even the solution. If not please advise so as people who read it could get back to you as soon as possible.

Regards!

 

[tazz]

Hi kyajaiinc,
If you go to this web page:www.rondebruin.nl/windows_articles.htm you will find exactly what you need.
Have you saved the workbook as a macro excel? I made this mistakes a couple of times and this is the first thing I check.
Also, a good way to find an answer to your question is to upload an example with what you have and what your data you want to look like.
Have a nice day!