VBA Code to filter and copy visible data from multiple workbooks into another open workbook - urgent

Kthom019

New Member
Joined
May 16, 2017
Messages
44
I have 3 open workbooks, I want to filter based on the message box criteria, then copy the filtered values for each workbook over to another workbook based on the next empty row.

The code before I inserted the filter section worked fine, now it filters but copy only the first workbook.
Please assist me to fix and understand what I did wrong in the below code?

Code:
VBA Code:
Sub updateCurRevenue()

Dim sNames As Variant
sNames = Array("Jas.xlsm", "Tees.xlsm", "Books.xlsm")

Dim wb As Workbook
Set wb = ActiveWorkbook

With wb.Worksheets("Data")
Dim dFirst As Range
Set dFirst = .Cells(.Rows.Count, "A").End(xlUp).Offset(1)
End With

Dim dCell As Range
Set dCell = dFirst

Dim sName As Variant
Dim sLastRow As Long
Dim sRng As Range
Dim dRows As Long
Dim i As String
i = InputBox("Last actual 'RED' Date")

For Each sName In sNames
With Workbooks(sName).Worksheets("Names")
sLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
Selection.AutoFilter
.Range("A2:N" & sLastRow).AutoFilter field:=12, Criteria1:=">" & i
Set sRng = .SpecialCells(xlCellTypeVisible).Copy
End With

With sRng
dCell.Resize(.Rows.Count, .Columns.Count).Value = sRng.Value
dRows = dRows + .Rows.Count
Set dCell = dCell.Offset(.Rows.Count)
End With
Next sName
With dFirst.Resize(dRows, sRng.Columns.Count)
.Interior.Color = xlNone
With .Font
.Name = "Arial"
.Size = 10
End With
End With

End Sub

I may have posted a similar question in another group but no one was able to answer.
 
Last edited by a moderator:

Some videos you may like

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

Watch MrExcel Video

Forum statistics

Threads
1,128,154
Messages
5,629,005
Members
416,358
Latest member
grsaltzman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top