Consolidate data from different excel files using macro

[Pooja]

Hi,
I am using Chandoo's code (as below) and it is working great. But i have additional requirement on this code. I am using this code for a Dahsboard where I need to refresh and run the macro multiple times. Here each time i run the macro the new data is pasted one below another, creating duplications.

Requirement : Is it possible to easre the previous data and paste the new one each time I run the macro. Please help as I am stuck here.

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 strLinkSheet As String
Dim sheetname As String

strLinkSheet = "Link"

On Error GoTo ErrH
Sheets(strLinkSheet).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(strLinkSheet).Select
ActiveCell.Offset(1, 0).Select
Loop
'activates sheet of specific name
Worksheets("Dashboard Project view").Activate
Exit Sub

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

 

[kchiba]

Hi Pooja,

try this, I have added a line after the Loop statement

Loop
Range("B2").select
'activates sheet of specific name
Worksheets("Dashboard Project view").Activate
Exit Sub


kanti

 

[Pooja]

Hi Pooja,

try this, I have added a line after the Loop statement

Loop
Range("B2").select
'activates sheet of specific name
Worksheets("Dashboard Project view").Activate
Exit Sub


kanti

Hi Kanti,

thanks for your response.
I added this line to the code but still it is taking the data below pervious data.

 

[kchiba]

Hi Pooja,

Can you upload the file with the macro, so that i can identify the range that needs to be cleared, before the data is copied from the other file.

 

[Pooja]

Hi Pooja,

Can you upload the file with the macro, so that i can identify the range that needs to be cleared, before the data is copied from the other file.

Hi Kanti,

PFA file,
Sheet "Masterdata" has all raw data in which macro is coping. So we need to clear from "A2" to "O145489". I am trying to clear maximum row as in future the data will keep increasing. Let me know if you need more information.

 

[kchiba]

Hi Pooja,

thank you for the file, here is the file with a change to the macro, the macro clears the whole table before it starts with the loop.

Please check and let me know if it is OK

kanti

 

[Pooja]

Hey Kanti,

Your are awesome .. its working fine now
I am new to macro and VBA just trying to learn, can you explain me the code.

 

[kchiba]

Hi Pooja,

Glad it worked, if you are asking about the code I added,

Set rTable = Sheets("MasterData").Range("A1").CurrentRegion
Set rTable = rTable.Resize(rTable.Rows.Count - 1)

Set rTable = rTable.Offset(1)
rTable.Clear

You will note that I have a small sub GetRange2Clear() called in the VBA code. This code is the same as GOTO Special Current Region, so it will make the whole region that surrounds A1 on the sheet MasterData = to rTable.

Since we do not want to clear the Headers we Resize rTable by reducing it by one and then set the new Range being the old Range Offet by 1 row.

And now we can clear the table and keep the headers.

Caution, when using CurrentRegion the assumption is that there are no other Rows or Columns that you want to keep, as the CurrentRegion goes from the Cell (in this case A1) out to all other cells in a block until it reaches a blank column and blank row.

Hope that this helps, I am sure it is not too clear.

kanti

 

[Pooja]

Hi Kanti,

I understood most of what to explain . thanks again.