Macro to combine data from multiple files in a one file

[vijay.vizzu]

Hi all,


I am new to VBA and recently joined Chandoo's VBA Classes. Now i have created a macro, which can copy data from a master file and paste it to another file. I have 7 master files and by using advanced filter in VBA code, i am trying to copy filtered data to a single file. So below is the code which i have created one of my master file. it works fine, but how can i put code in another master file because initially the code has to check the blank cell in the single file and filtered data to paste. I don't know how to check the blank cells before pasting the filtered data.

1st Module ( to filter the data)

[pre]

Sub AdvanceFilter()
'This code will display pendency in current sheet
Sheets("filters").Select
Range("b6").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Clear

Worksheets("04X-SOUTH").Range("fltRange").AdvancedFilter _
Action:=xlFilterCopy, _
criteriarange:=Worksheets("Filters").Range("fltCriteria"), _
copytorange:=Range("b6"), _
unique:=False
Range("b7").Select

End Sub
2nd module (to paste the data in another file)

Sub SendToQP()
' This Code will send Pendency to QP.xlsx
Dim x As Integer

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Application.Workbooks.Open ("C:Documents and SettingsvijaykumarMy DocumentsQuote PendencyQP.xlsx")
Worksheets("all").Select
Range("b3").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Clear

ActiveWindow.ActivatePrevious
Sheets("filters").Select
Range("b7:C7").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy

ActiveWindow.ActivateNext
Range("b3").Select
ActiveSheet.Paste

ActiveWindow.ActivatePrevious
Range("g7").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
ActiveWindow.ActivateNext
Range("d3").Select
ActiveSheet.Paste
ActiveWindow.ActivatePrevious

Range("i7:j7").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
ActiveWindow.ActivateNext
Range("e3").Select
ActiveSheet.Paste
ActiveWindow.ActivatePrevious

Range("m7:n7").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
ActiveWindow.ActivateNext
Range("g3").Select
ActiveSheet.Paste
ActiveWindow.ActivatePrevious

Range("q7").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
ActiveWindow.ActivateNext
Range("i3").Select
ActiveSheet.Paste

ActiveWorkbook.Save
ActiveWorkbook.Close

Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub

[/pre]
Any help highly appreciated


Regards

Vijay

 

[kaushik03]

Hi Vijay,


If I understand your question properly, then you are trying to find out out the last blank row in a sheet where you are trying to paste the data.


If I am correct, then let's take the below example:


In sheet1, say A1 to A8 are already filled with data. Now your filtered data should be pasted from A9 onwards in the sheet1 of this workbook. In order to do this, you need to find out the last blank row as follows:


Dim LstRow As Long


LstRow = ThisWorkbook.Worksheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row + 1


Note: You can change the sheet and Column reference according to your need.


Now when you will paste the data, you will need to do something like this:


Range("A"&(LstRow)).Select


Activesheet.Paste


Hope this helps.


In case it doesn't solve your problem please consider uploading a sample file.


Regards,

Kaushik