Excel/Macro to filter and copy data in new workbook
Hope you are doing well!!
Below is my code to open an workbook from the path specified in Range B1. It is working fine.
Need: I would like to have a code to filter the range from A:M in the opened workbook with criteria "JPMVEC" copy and paste the visible cells in new workbook thereby saving it in the spath1 mentioned in the master sheet.
'Consider this macro ofor
Dim sh As Worksheet, v As Variant, i As Long
Dim r As Range, bk As Workbook, sh1 As Worksheet
Dim sPath As String, vv As Variant
Dim sPath1 As String
Dim rr As Range, r1 As Range
Dim r2 As Range
'mystring = E3
Dim myselection As Range
sPath = Sheets("Sheet1").Range("B1").Value
sPath1 = Sheets("Sheet1").Range("B2").Value
vv = Application.GetOpenFilename( _
filefilter:="Excel Files (*.xl*),*.xl", _
Title:="Select one or more files to process", _
If Not IsArray(vv) Then
MsgBox "You chose to cancel"
For i = LBound(vv) To UBound(vv)
Set bk = Nothing
On Error Resume Next
Application.DisplayAlerts = False
Set bk = Workbooks.Open(Filename:=vv(i))
Thanks for your help!!
You can use 2 more pieces of code to make this happen:
1. select the workbook you want to open: workbooks.("xxxxx").activate
2. From that workbook, filter and copy the data by using the below code:
ActiveSheet.Range("$A$1:$A$8").AutoFilter Field:=1, Criteria1:="JPMVEC"
3. Select the original workbook where you started and paste the data there.
Hope this helps,