Copying filtered rows to a different sheet with VBA, but with a twist

[downrightcynical]

Hello all! I am a new user here hoping to resolve a slight issue I am having with VBA...

I am trying to use a simple button function to send filtered out rows to another sheet by way of VBA.
I have managed to accomplish copying over the entire row, but I need to add a slight twist. Instead of copying the entire row, I would like to only copy a specific column from the source row and paste that columns data into a predetermined column in the end sheet then repeat this process for the rest of the data in the source row(s). The reason why I want to do it this way is because the format of the two sheets is drastically different and I must specify column by column where the data is going to go, copying the whole row simply will not work for handling the data.

The code I am working with now appears like so:

   iRow = sht2.Range("B" & Application.Rows.Count).End(xlUp).Row + 1

   With frm
        
        ActiveSheet.UsedRange.Offset(1, 0).SpecialCells(xlCellTypeVisible).Copy Destination:=Sheets("screen").Cells(iRow, 1)
        
    End With

I am using the offset function to prevent the sheet from copying the source header.

Thank you!

 

[kevin9999]

I think we need more information. Which columns in the source sheet go to which columns in the destination sheet? Do they get copied to a particular row, or appended to the bottom of existing data? Could you provide your entire code (not just a snippet) and a sample of your data using the XL2BB Tool?

 

[Peter_SSs]

I assume that you are just looking for an example?
This would copy the filtered values in column C of the active sheet to column J, starting at row iRow, of sheet 'screen'

Intersect(ActiveSheet.UsedRange.Offset(1, 0), Columns("C")).SpecialCells(xlCellTypeVisible).Copy Destination:=Sheets("screen").Cells(iRow, "J")

 

[downrightcynical]

I think we need more information. Which columns in the source sheet go to which columns in the destination sheet? Do they get copied to a particular row, or appended to the bottom of existing data? Could you provide your entire code (not just a snippet) and a sample of your data using the XL2BB Tool?

Sure thing!

1.) From the source sheet (database) I am pulling all the columns to the destination sheet (screen), they are going in a random with no recognizable pattern. Column 1 and 2 will go both to 1 and 2 at the destination, column 3 from source will go into 7 at its destination, 4 -> 6, we skip 5, 6 -> 3, 7 -> 4, 8 -> 5, 9 -> 8, 9 -> 9, 10 -> 8, and there are a few more but it continues in random fashion.

2.) They will append at the bottom of the destination sheet.

3.) Full Code (its pretty messy, sorry im a novice)

Sub Screen_Send()

    Dim frm As Worksheet
    Dim sht2 As Worksheet
    Dim lastRow As Long
    Dim iRow As Long
    Dim lstrow As Integer
    
    Set frm = Sheets("database")
    Set sht2 = Sheets("screen")
    
    iRow = sht2.Range("B" & Application.Rows.Count).End(xlUp).Row + 1
    lastRow = ActiveSheet.UsedRange.Rows.Count
    
    With frm
        
        ActiveSheet.UsedRange.Offset(1, 0).SpecialCells(xlCellTypeVisible).Copy Destination:=Sheets("screen").Cells(iRow, 1)
        
    End With
    
End Sub

4.) Sample data:

Soyboy Portfolio.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
1	ticker	id	name	write	sector	industry group	industry	sub industry	rev tag	nation	ownership	screen	more 1	more 2	flavor	price	mktcap	value	value %	confidence	value type	tracker			inst. %s	ins. %s		date added
19	ABT	19	ABBOTT LABORATORIES (XNYS:ABT)	Makes critical healthcare products for the treatment of Diabities amongst other common ailments.	Health Care	Health Care Equipment & Services	Health Care Equipment & Supplies	Health Care Equipment		multinational		stable	value			117.25	206.8 B	$88.48	132.52%	4	conservative	watch			75%	0.73%		10/17/2021 16:11
59	ABBV	59	ABBVIE INC. (XNYS:ABBV)	Big pharma company that makes very popular drugs such as immunology drugs including: humira, rinvoq, skyrizi and including mental drugs like lexapro, amongst a plethora of other drugs.	Health Care	Pharmaceuticals, Biotechnology & Life Sciences	Pharmaceuticals	Pharmaceuticals	big pharma		buffett		orphan		value	146.76	259.6 B	$100.53	145.99%	4	par	watch			69%	0.12%		10/21/2021 15:26
70	RTX	70	RAYTHEON TECHNOLOGIES CORPORATION (XNYS:RTX)	Holds several valuable contracts with the US government for the production of defense systems, mainly revolving around aerospace. Has revenue segments that breakdown as: Collins Aerospace Systems, Pratt & Whitney, Intelligence & Space, and finally, Missiles & Defense.	Industrials	Capital Goods	Aerospace & Defense	Aerospace & Defense								92.24	137.7 B	$54.07	170.59%	4	par				80%	0.11%		10/22/2021 9:55
71	ZYME	71	ZYMEWORKS INC. (XNYS:ZYME)	Develops treatments for several types of cancers through protein cell therapy.	Health Care	Pharmaceuticals, Biotechnology & Life Sciences	Biotechnology	Biotechnology					cancer			6.93	00.4 B	$16.38	42.31%	2	aggressive				77%	0.83%		10/22/2021 10:20
77	IT	77	GARTNER, INC. (XNYS:IT)	Research and Analytics firm that delivers data to customers in large quantities for enterprise use so that enterprises can better understand their business and potential growth opprotunities.	Information Technology	Software & Services	IT Services	IT Consulting & Other Services			high		future tech		growth	273.57	22.5 B	$140.16	195.18%	3	conservative				94%	2.97%		10/22/2021 11:51
79	ALB	79	ALBEMARLE CORPORATION (XNYS:ALB)	Manufatures chemicles like lithium and bromine, is also the largest producer of lithium used in EV batteries.	Materials	Materials	Chemicals	Specialty Chemicals	lithium				ev		growth	182.00	21.3 B	$224.38	81.11%	4	aggressive	watch			85%	0.33%		10/22/2021 11:59
80	KEYS	80	KEYSIGHT TECHNOLOGIES, INC. (XNYS:KEYS)	Acting as a key innovator, Keysight's goal is to help enterprises develop and test new technologies that can help them innovate for the future all across several sectors, such as aerospace, defense, electronics, ect. They also own Eggplant which offers AI solutions for testing purposes.	Information Technology	Technology Hardware & Equipment	Electronic Equipment, Instruments & Components	Electronic Equipment & Instruments				margins, roic	future tech	ai		153.28	28.0 B	$163.37	93.82%	4	par				91%	0.47%		10/22/2021 12:08
82	NVO	82	Novo Nordisk A/S (XNYS:NVO)	Danish biotech company that produces treatments for several diseases. Has 14% revenue generated from diabete treatments.	Health Care	Pharmaceuticals, Biotechnology & Life Sciences	Pharmaceuticals	Pharmaceuticals			simons	margins	diabetes			97.68	176.3 B	$79.45	122.95%	2	aggressive				8%	0.01%		10/22/2021 20:00
95	FRPT	95	FRESHPET, INC. (XNAS:FRPT)	Creates alternative food products for domesticated animals. Sells food in retail stores by way of public refrigeration units.	Consumer Staples		Food Products	Packaged Foods & Meats	pets		high					83.35	03.6 B	$12.01	694.00%	3	conservative				98%	3.51%		10/23/2021 17:59
97	MEDP	97	MEDPACE HOLDINGS, INC. (XNAS:MEDP)	Organizes clinical contract research providing phase I-IV clinical development services to biotechs.	Health Care		Life Sciences Tools & Services	Life Sciences Tools & Services			high		labs			140.20	04.9 B	$114.89	122.03%	4	conservative				79%	20.31%		10/23/2021 18:06
99	PENN	99	PENN NATIONAL GAMING, INC. (XNAS:PENN)	Operates small to medium size casinos around the United States with most of its vesting interest being targeted in the Northeast of the United States. Future Tech interests lie in onling gambling, which last quarter comprimised 8% of their revenue.	Consumer Discretionary		Hotels, Restaurants & Leisure	Casinos & Gaming			druckenmiller	short int	future tech			47.24	08.0 B	$36.33	130.03%	3	conservative				86%	1.69%		10/23/2021 18:11
106	SABR	106	SABRE CORPORATION (XNAS:SABR)	Creates technology to assist travel firms in developing their businesses' programs by way of AI developments.	Information Technology		IT Services	Data Processing & Outsourced Services	ai		robbins	short int.	future tech	travel	growth	11.11	03.6 B	$9.13	121.69%	1	aggressive				113%	1.05%		10/23/2021 21:20
database

Cell Formulas
Range	Formula
S19,S59,S70:S71,S77,S79:S80,S82,S95,S97,S99,S106	S19	=P19/R19
Q19,Q59,Q70:Q71,Q77,Q79:Q80,Q82,Q95,Q97,Q99,Q106	Q19	=C19.[Market cap]
P19,P59,P70:P71,P77,P79:P80,P82,P95,P97,P99,P106	P19	=C19.Price

Cells with Data Validation
Cell	Allow	Criteria
A2:A106	Custom	=COUNTIF($A$2:$A$1093,A2)=1

 

[downrightcynical]

I assume that you are just looking for an example?
This would copy the filtered values in column C of the active sheet to column J, starting at row iRow, of sheet 'screen'

Intersect(ActiveSheet.UsedRange.Offset(1, 0), Columns("C")).SpecialCells(xlCellTypeVisible).Copy Destination:=Sheets("screen").Cells(iRow, "J")

This does the trick Peter! Thank you!!

 

[Peter_SSs]

This does the trick Peter! Thank you!!

You're welcome. Thanks for the follow-up.