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]

Try changing .Range("A2" & lastrow2) to .Range("A" & lastrow2)

 

[jalrs]

Try changing .Range("A2" & lastrow2) to .Range("A" & lastrow2)

it worked indeed, but i lost all the headers on row 1 so not quite there yet. i tried "A1" instead and it pasted on cell "A11"

anything else that might work?
thanks for your time!

 

[kevin9999]

it worked indeed, but i lost all the headers on row 1 so not quite there yet. i tried "A1" instead and it pasted on cell "A11"

anything else that might work?
thanks for your time!

You're copying to the last row in the sheet "pendentes" that your code finds data - which may be the header row. Try setting the row where you actually want the data to be pasted by .End(xlUp).Row + 1

 

[jalrs]

You're copying to the last row in the sheet "pendentes" that your code finds data - which may be the header row. Try setting the row where you actually want the data to be pasted by .End(xlUp).Row + 1

i mean i removed the "& lastrow2" from the .Range("A" & lastrow2) and simply rewrote it by .Range("a2") and it worked.

should i have any concerns regarding this? plus, could u help me on additional questions 1, 2?
Thanks again for your time!

 

[kevin9999]

i mean i removed the "& lastrow2" from the .Range("A" & lastrow2) and simply rewrote it by .Range("a2") and it worked.

should i have any concerns regarding this? plus, could u help me on additional questions 1, 2?
Thanks again for your time!

I'm away from my laptop right now, could you possibly post a sample of your data & I'll be happy to look your issues, although I suspect someone else will be able to help you before I do.

 

[jalrs]

I'm away from my laptop right now, could you possibly post a sample of your data & I'll be happy to look your issues, although I suspect someone else will be able to help you before I do.

i appreciate the help, however the data is sensitive so i cant really post a sample of it.
ill try to explain better what i want regarding 1 and 2

for 1) i want to also copy BH column from the worksheet "Stock Trânsito" on first workbook and paste it on column AW on sheet "pendentes" from the new workbook. my problem is i have set the copy range from A6 to AV [With activerange ("A6:AV" & lastrow)] and i dont know how to include the column BH starting on cell BH6 to copy it for cell AW2 on new workbook.

for 2) my first criteria of filtering is the logistics responsible, in this case its called "Apoio SP", the second criteria i want to apply is "in transit" which corresponds to the stock status. to sum up, i want to filter the data that concerns to "Apoio SP" and it is "in transit". field of stock status is 46 if it helps.

Thank you for your patience!!

 

[kevin9999]

You could still provide a sample of your layout with bogus information in it - II really just want to get a grip on the layout of your sheet. I've provided a sample below to show what I mean. Based on your description, I'm assuming that your headers on the "Stock Trânsito" sheet are on row 6. Therefore, your sheet would look something like this:

STTApoioSP.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE	BF	BG	BH
1	assume nothing up here
2
3
4
5
6	Header1	Header2	Header3	Header4	Header5	Header6	Header7	Header8	Header9	Header10	Header11	Header12	Header13	Header14	Header15	Header16	Header17	Header18	Header19	Header20	Header21	Header22	Header23	Header24	Header25	Header26	Header27	Header28	Header29	Header30	Header31	Header32	Header33	Header34	Header35	Header36	Header37	Header38	Header39	Header40	Header41	Header42	Header43	Header44	Header45	Header46	Header47	Header48	Header49	Header50	Header51	Header52	Header53	Header54	Header55	Header56	Header57	Header58	Header59	Header60
7	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x
8	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x
9	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x
10	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	Apoio SP	x	x	x	x	x	x	x	x	x	x	x	x	x	bh
11	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	Apoio SP	x	x	x	x	x	x	x	x	x	x	x	x	x	bh
12	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	Apoio SP	x	x	x	x	x	x	x	x	x	x	x	x	x	bh
13	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	Apoio SP	x	x	x	x	x	x	x	x	x	x	x	x	x	bh
14	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	bh
15	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	bh
16	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x
17
Stock Trânsito

If this is what your sheet looks like, then the following code will address your original post, as well as your additional question #2.

Option Explicit
Sub jalrs()
    Dim ws1 As Worksheet, ws2 As Worksheet
    Set ws1 = Worksheets("Stock Trânsito")
    Set ws2 = Worksheets("pendentes")
    
    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("A6:AV" & lr1)
        .AutoFilter 46, "Apoio SP"
        .Offset(1).Copy ws2.Cells(lr2, 1)
        With ws1.Range("BH7:BH" & lr1)
           .Copy ws2.Cells(2, 49)
        End With
        .AutoFilter
    End With
End Sub

Here's your "pendentes" sheet begore running the code:

STTApoioSP.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW
1	header1	header2	header3	header4	header5	header6	header7	header8	header9	header10	header11	header12	header13	header14	header15	header16	header17	header18	header19	header20	header21	header22	header23	header24	header25	header26	header27	header28	header29	header30	header31	header32	header33	header34	header35	header36	header37	header38	header39	header40	header41	header42	header43	header44	header45	header46	header47	header48	header49
2
3
4
5
6
pendentes

And after:

STTApoioSP.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW
1	header1	header2	header3	header4	header5	header6	header7	header8	header9	header10	header11	header12	header13	header14	header15	header16	header17	header18	header19	header20	header21	header22	header23	header24	header25	header26	header27	header28	header29	header30	header31	header32	header33	header34	header35	header36	header37	header38	header39	header40	header41	header42	header43	header44	header45	header46	header47	header48	header49
2	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	Apoio SP	x	x	bh
3	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	Apoio SP	x	x	bh
4	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	Apoio SP	x	x	bh
5	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	Apoio SP	x	x	bh
6
7
pendentes

As far as your additional question #1 is concerned - yes, no problem, just let me know which column you want the second criteria applied to.

 

[jalrs]

Hello Kevin!

Sorry, didnt really think about the bogus information layout, since im new dont really know how things work here yet. but i'll get there.

Yes thats like it except that "Stock Transito" (here shown as 1st Screenshot" is in a different workbook (Named "AnaliseST.xlsm") and "pendentes" is in STTApoioSP.xlsm like you provide on 2nd and 3rd screenshots.

As for second criteria, it is applied to column AU on 1st workbook and, so column #47. criteria here is "in transit"

Thank you!!!!

 

[jalrs]

Hello Kevin!

Sorry, didnt really think about the bogus information layout, since im new dont really know how things work here yet. but i'll get there.

Yes thats like it except that "Stock Transito" (here shown as 1st Screenshot" is in a different workbook (Named "AnaliseST.xlsm") and "pendentes" is in STTApoioSP.xlsm like you provide on 2nd and 3rd screenshots.

As for second criteria, it is applied to column AU on 1st workbook and, so column #47. criteria here is "in transit"

Thank you!!!!

might be stupid to ask but, does values keep the same format when copied and when pasted?

ill try the code when i get to work, ~3h. but just by that first glance, ye defo looks like it