Filtering from one workbook, pasting into another

[jalrs]

Hello guys,

I'm new to VBA and therefore i need some help. I would be glad if i found it here.
So, my problem is: I runned a code after doing my search here, but the code misses on something, after i did my changes and debug it. Im filtering from a sheet called "Stock Trânsito" in Workbook1 to paste on a sheet called "pendentes" on a workbook called "STTApoioSP". When i run the code, instead of pasting into cell "A2" it pastes into cell "A21" and brings a value that doesnt match the criteria filter.

My code:

Sub filterAPOIOSP()

ThisWorkbook.Worksheets("Stock Trânsito").Activate

Dim lastrow As Long, lastrow2 As Long

lastrow = Cells(Rows.Count, "A").End(xlUp).Row
lastrow2 = Workbooks("STTApoioSP.xlsm").Sheets("pendentes").Cells(Rows.Count, "A").End(xlUp).Row

With ActiveSheet.Range("A6:AV" & lastrow)
.AutoFilter Field:=46, Criteria1:="Apoio SP", Operator:=xlFilterValues
.SpecialCells(xlCellTypeVisible).Copy Workbooks("STTApoioSP.xlsm").Worksheets("pendentes").Range("A2" & lastrow2)

End With

ActiveSheet.AutoFilterMode = False

End Sub


Additional questions:
1- If i want to add criteria2, how should i add it into the code?
2- how can i add a specific column from my workbook to this new one? i mean, where do i write it on the code?

thanks in advance!

 

[kevin9999]

Good morning Kevin (timezones )

It did work for first department, when i assigned it to all department buttons, it stopped working. what happened was, for example department 2 has 254 entries, and it only returned 31 rows, happened the same for all the other departments

thanks!

I don't quite follow you - could you provide a sample of your "department 2" data and what filter did you apply to it in the code?

 

[jalrs]

I don't quite follow you - could you provide a sample of your "department 2" data and what filter did you apply to it in the code?

yes sure, ill comeback to you when i have it done!

 

[kevin9999]

yes sure, ill comeback to you when i have it done!

You said in post #22 that the code I put in post #21 worked. The only thing I added to that code was two lines that would only delete the bottom, unused rows in the destination sheet - so I'm completely confused as to what's gone wrong...

 

[jalrs]

You said in post #22 that the code I put in post #21 worked. The only thing I added to that code was two lines that would only delete the bottom, unused rows in the destination sheet - so I'm completely confused as to what's gone wrong...

armazém = department 2

ok so, on (1) you can see i have 254 records found for armazém (i had clicked there, mouse just doesnt show on printscreen)
on (2) you can see i'm on armazém sheet and has output i only have 31 records

code for armazém is the same for apoio sp(apoio sp=department 1) except in armazém i don't have the second filter because it is not intended here

code for armazém:

Option Explicit
Sub Armazém()

Dim wb1 As Workbook, wb2 As Workbook
Dim ws1 As Worksheet, ws2 As Worksheet

Set wb1 = ThisWorkbook
Set wb2 = Workbooks("Template.xlsm")

Set ws1 = wb1.Worksheets("Stock Trânsito")
Set ws2 = wb2.Worksheets("Armazém")

ws2.UsedRange.Offset(1).ClearContents

Dim lr1 As Long, lr2 As Long

lr1 = ws1.Cells(Rows.Count, 1).End(3).Row
lr2 = ws2.Cells(Rows.Count, 1).End(3).Row + 1

With ws1.Range("A5:AV" & lr1)

.AutoFilter 46, "Armazém"
.Offset(1).Copy ws2.Cells(lr2, 1)

With ws1.Range("BH6:BH" & lr1)

.Copy ws2.Cells(2, 49)

End With

.AutoFilter

End With

lr2 = Cells.Find("*", , xlFormulas, , 1, 2).Row + 1
ws2.Range("A" & lr2 & ":A1001").EntireRow.Delete

End Sub

 

[kevin9999]

Try changing

lr2 = Cells.Find("*", , xlFormulas, , 1, 2).Row + 1

to

lr2 = ws2.Cells.Find("*", , xlFormulas, , 1, 2).Row + 1

and let me know if that fixes it. If not, I'm going to need the actual data to work with because I can't do anything with screenshots. You can use the XL2BB Tool to provide it.

 

[jalrs]

Try changing

lr2 = Cells.Find("*", , xlFormulas, , 1, 2).Row + 1

to

lr2 = ws2.Cells.Find("*", , xlFormulas, , 1, 2).Row + 1

and let me know if that fixes it. If not, I'm going to need the actual data to work with because I can't do anything with screenshots. You can use the XL2BB Tool to provide it.

works great!!

thank you so much kevin!!!!!

hats off, like seriously!

 

[kevin9999]

works great!!

thank you so much kevin!!!!!

hats off, like seriously!

Finally ?

 

[jalrs]

Finally ?

additional question, might need it depending on my another open thread, depending on the solution.
what would i add here on the last bit of code you provided if i wanted to clear all the rows starting on row 15 even if rows 5-14 are empty lets say.

sorry to bother again kevin

 

[kevin9999]

If you mean delete the rows from row 15 to (say) row 1001 then

ws2.Range("A15:A1001").EntireRow.Delete

 

[jalrs]

If you mean delete the rows from row 15 to (say) row 1001 then

ws2.Range("A15:A1001").EntireRow.Delete

thank you kevin!!! superb work!!