Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Autofilter Source Workbook A result in Destination Workbook BSheet1
I need to copy the result from Autofilter on Source Workbook A in
Destination Workbook B Sheet1 My VBA AutoFilter Code is in an external Menu Workbook .. Sub Import() Dim fso As Object Dim Source As Object ' Source Folder path Dim Dest As Object ' Destination Folder Path Dim WBA as Object ' Source Workbook Dim WBB as object ' Destination Workbook Dim LastRow As Long, Dim Rng As Range ' Set fso = CreateObject("Scripting.FileSystemObject") Set Source = fso.GetFolder("P:\Invoices") WBA = "A.xls" WBB = "B.xls" Workbooks.Open Filename:=WBA.Path On Error GoTo 0 LastRow = Range("B65335").End(xlUp).Row: Range("B2").Select ActiveSheet.Range("$A$2:$W$65535").AutoFilter Field:=2, Criteria1:="=Open", Operator:=xlOr, Criteria2:="=Re-Submitted" ' Set Rng = Range("B2").Resize(LastRow - 1) Workbooks(WBA.Name).Close False Workbooks.Open Filename:=WBB.Path 'Syntax problem here Rng.Copy ..... Workbooks(WBB.Name).Close False End Sub Help appreciated J.P. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Autofilter Source Workbook A result in Destination WorkbookB Sheet1
Untested, but it did compile:
Option Explicit Sub Import2() Dim WBA As Workbook Dim WBB As Workbook Dim LastRow As Long Dim Rng As Range Dim myPath As String Dim RngToCopy As Range Dim DestCell As Range myPath = "P:\invoices" If Right(myPath, 1) < "\" Then myPath = myPath & "\" End If Set WBA = Nothing On Error Resume Next Set WBA = Workbooks.Open(myPath & "A.xls") On Error GoTo 0 If WBA Is Nothing Then MsgBox "WBA wasn't found!" Exit Sub End If Set WBB = Nothing On Error Resume Next Set WBB = Workbooks.Open(myPath & "b.xls") On Error GoTo 0 If WBB Is Nothing Then MsgBox "WBb wasn't found!" Exit Sub End If 'change to the correct sheet name With WBA.Worksheets("sheet1") LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row .AutoFilterMode = False 'just filter by column B .Range("b1:b" & LastRow).AutoFilter _ field:=1, criteria:="Open", _ Operator:=xlOr, Criteria2:="Re-Submitted" If .AutoFilter.Range.Columns(1) _ .SpecialCells(xlCellTypeVisible).Cells.Count = 1 Then MsgBox "Only headers are visible" Exit Sub End If With .AutoFilter.Range Set RngToCopy = .Resize(.Rows.Count, 1).Offset(1, 0) _ .SpecialCells(xlCellTypeVisible).EntireRow End With End With 'change to the correct sheet name With WBB.Worksheets("sheet2") LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row Set DestCell = .Cells(LastRow + 1, "A") End With RngToCopy.Copy _ Destination:=DestCell 'close the sending workbook without saving WBA.Close savechanges:=False 'I would think you'd want to save your changes in WBB! WBB.Close savechanges:=True End Sub Test it before you trust it!!!! u473 wrote: I need to copy the result from Autofilter on Source Workbook A in Destination Workbook B Sheet1 My VBA AutoFilter Code is in an external Menu Workbook . Sub Import() Dim fso As Object Dim Source As Object ' Source Folder path Dim Dest As Object ' Destination Folder Path Dim WBA as Object ' Source Workbook Dim WBB as object ' Destination Workbook Dim LastRow As Long, Dim Rng As Range ' Set fso = CreateObject("Scripting.FileSystemObject") Set Source = fso.GetFolder("P:\Invoices") WBA = "A.xls" WBB = "B.xls" Workbooks.Open Filename:=WBA.Path On Error GoTo 0 LastRow = Range("B65335").End(xlUp).Row: Range("B2").Select ActiveSheet.Range("$A$2:$W$65535").AutoFilter Field:=2, Criteria1:="=Open", Operator:=xlOr, Criteria2:="=Re-Submitted" ' Set Rng = Range("B2").Resize(LastRow - 1) Workbooks(WBA.Name).Close False Workbooks.Open Filename:=WBB.Path 'Syntax problem here Rng.Copy ..... Workbooks(WBB.Name).Close False End Sub Help appreciated J.P. -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel-how to link source workbook to copy of destination workbook | Excel Worksheet Functions | |||
HELP: Use the autofilter result on one workbook to filter the next list on another workbook | Excel Programming | |||
HELP: Use the autofilter result on one workbook to filter the next list on another workbook | Excel Worksheet Functions | |||
How do I post values from source workbook to destination workbook | Excel Programming | |||
how to copy succeeding rows from source workbook to destination w. | Excel Programming |