My code copies the whole thing below last cell by routes now.
I want to filter not only by route but with shipped aswell - so with yes or no.
I also want to copy only selected cells from here to here. like images suggest by the following order (eg. cell C2 to cell A1 in another workbook).
my code looks like this:
Private Sub CommandButton1_Click()
Dim cell As Range
With Workbooks("FromHEre.xlsm").Worksheets("Sheet1")
For Each cell In .Range("D1:D" & .Cells(.Rows.Count, "D").End(xlUp).Row)
If cell.Value = "1" Then
.Rows(cell.Row).Copy Destination:=Workbooks("ToHere.xlsx").Worksheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Offset(1)
End If
Next cell
End With
End Sub
This is by no means a complete answer and it's un-tested, but should get you started.
Read the comments and adapt the code to fit your needs
Private Sub CommandButton1_Click()
Dim sourceWorkbook As Workbook
Dim sourceSheet As Worksheet
Dim sourceRange As Range
Dim sourceFilteredRange As Range
Dim targetWorkbook As Workbook
Dim targetSheet As Worksheet
Dim targetCell As Range
Dim cell As Range
Dim sourceLastRow As Long
Dim targetLastRow As Long
' Define source and target objects
Set sourceWorkbook = Workbooks("FromHere.xlsm")
Set sourceSheet = sourceWorkbook.Worksheets("Sheet1")
Set targetWorkbook = Workbooks("ToHere.xlsx")
Set targetSheet = targetWorkbook.Worksheets("Sheet1")
' Get last row of source sheet
sourceLastRow = sourceSheet.Cells(sourceSheet.Rows.Count, "A").End(xlUp).Row
' Get last row of target sheet
targetLastRow = targetSheet.Cells(targetSheet.Rows.Count, "A").End(xlUp).Row
' Set source range
Set sourceRange = sourceSheet.Range("A1:H" & sourceLastRow)
' Filter source range by route and shipped
With sourceRange
.AutoFilter Field:=4, Criteria1:="1"
.AutoFilter Field:=7, Criteria1:="yes"
End With
' Get filtered range
Set sourceFilteredRange = sourceRange.AutoFilter.Range
' Copy filtered range to target sheet
sourceFilteredRange.Copy targetSheet.Range("A" & targetLastRow)
End Sub
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments