VBA Help

[njlevi49]

I need help with VBA as this is my first time using it. I would like to copy and paste a1:ab14 to another worksheet but I need columns b, d, p, r removed also only rows with p in column A to paste with no blank rows showing, any help or ideas would be greatly appreciated.

	A	B	C	D	E	F	G	H	L	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB
1			Gross		1	2	3	4	5	6	7	8	9						10	11	12	13	14	15	16	17	18
2		<st1:date ls="trans" Month="04" Day="23" Year="13">04/23/13</st1:date>	Front Nine		3	7	5	2	6	4	8	9	1			41394	Back Nine		2	3	9	7	5	8	6	4	1
3		<st1:date ls="trans" Month="04" Day="23" Year="13">04/23/13</st1:date>	Barker, Steve	2	5	4	4	4	3	4	5	3	4	36	p	41394	Barker, Steve	2	5	4	4	3	5	3	5	4	4	37
4	p	<st1:date ls="trans" Month="04" Day="23" Year="13">04/23/13</st1:date>	Batcheider, Jason	2	5	4	4	4	3	4	5	3	4	36		41394	Batcheider, Jason	2	5	4	4	3	5	3	5	4	4	37
5		<st1:date ls="trans" Month="04" Day="23" Year="13">04/23/13</st1:date>	Budd, Jerry	3	5	4	4	4	3	4	5	3	4	36		41394	Budd, Jerry	3	5	4	4	3	5	3	5	4	4	37
6		<st1:date ls="trans" Month="04" Day="23" Year="13">04/23/13</st1:date>	Campau, Chad	2	5	4	4	4	3	4	5	3	4	36		41394	Campau, Chad	2	5	4	4	3	5	3	5	4	4	37
7		<st1:date ls="trans" Month="04" Day="23" Year="13">04/23/13</st1:date>	Coates, Brian	3	6	5	4	4	4	3	4	5	3	38	p	41394	Coates, Brian	3	5	4	4	3	5	3	5	4	4	37
8	p	<st1:date ls="trans" Month="04" Day="23" Year="13">04/23/13</st1:date>	Conway, Casey	2	5	4	4	4	3	4	5	3	4	36		41394	Conway, Casey	2	5	4	4	3	5	3	5	4	4	37
9		<st1:date ls="trans" Month="04" Day="23" Year="13">04/23/13</st1:date>	Curry, Claude	2	5	4	4	4	3	4	5	3	4	36		41394	Curry, Claude	2	5	4	4	3	5	3	5	4	4	37
10		<st1:date ls="trans" Month="04" Day="23" Year="13">04/23/13</st1:date>	Dattilo, Mike	2	5	4	4	4	3	4	5	3	4	36		41394	Dattilo, Mike	2	5	4	4	3	5	3	5	4	4	37
11	p	<st1:date ls="trans" Month="04" Day="23" Year="13">04/23/13</st1:date>	Daugherty, Pat	3	5	4	4	4	3	4	5	3	4	36		41394	Daugherty, Pat	3	5	4	4	3	5	3	5	4	4	37
12		<st1:date ls="trans" Month="04" Day="23" Year="13">04/23/13</st1:date>	Dennis, Brad	2	5	4	4	4	4	4	5	3	4	37		41394	Dennis, Brad	2	5	4	4	3	5	3	5	4	4	37
13		<st1:date ls="trans" Month="04" Day="23" Year="13">04/23/13</st1:date>	Dougherty Jack	2	5	4	4	4	3	4	5	3	4	36		41394	Dougherty Jack	2	5	4	4	3	5	3	5	4	4	37
14		<st1:date ls="trans" Month="04" Day="23" Year="13">04/23/13</st1:date>	Emeott,Mark	2	5	4	4	4	3	4	5	3	4	36		41394	Emeott,Mark	2	5	4	4	3	5	3	5	4	4	37

<colgroup><col style="width: 19pt; mso-width-source: userset; mso-width-alt: 914;" width="25"> <col style="width: 12pt; mso-width-source: userset; mso-width-alt: 585;" width="16"> <col style="width: 46pt; mso-width-source: userset; mso-width-alt: 2230;" width="61"> <col style="width: 69pt; mso-width-source: userset; mso-width-alt: 3364;" width="92"> <col style="width: 26pt; mso-width-source: userset; mso-width-alt: 1243;" width="34"> <col style="width: 20pt; mso-width-source: userset; mso-width-alt: 950;" span="10" width="26"> <col style="width: 17pt; mso-width-source: userset; mso-width-alt: 804;" width="22"> <col style="width: 37pt; mso-width-source: userset; mso-width-alt: 1792;" width="49"> <col style="width: 69pt; mso-width-source: userset; mso-width-alt: 3364;" width="92"> <col style="width: 20pt; mso-width-source: userset; mso-width-alt: 950;" width="26"> <col style="width: 20pt; mso-width-source: userset; mso-width-alt: 950;" span="10" width="26"> <tbody>
</tbody>

 

[Firefly2012]

Hi

Try this:

Sub CopyData()
Dim rData As Range
Dim wsNew As Worksheet

ActiveSheet.AutoFilterMode = False
Set rData = Range("A1:Ab14")
rData.AutoFilter field:=1, Criteria1:="p"
.AutoFilter.Range.Copy
Set wsNew = Sheets.Add
With wsNew
    .Range("A1").PasteSpecial
    .Range("b:b,d:d,p:p,r:r").Delete
End With


End Sub

Place it in a standard module and run it with the sheet containing your data active (ie visible on your Excel screen).

 

[njlevi49]

Hi

Try this:

Sub CopyData()
Dim rData As Range
Dim wsNew As Worksheet

ActiveSheet.AutoFilterMode = False
Set rData = Range("A1:Ab14")
rData.AutoFilter field:=1, Criteria1:="p"
.AutoFilter.Range.Copy
Set wsNew = Sheets.Add
With wsNew
    .Range("A1").PasteSpecial
    .Range("b:b,d:d,p:p,r:r").Delete
End With


End Sub

Place it in a standard module and run it with the sheet containing your data active (ie visible on your Excel screen).

Sub CopyData()
Dim rData As Range
Dim wsNew As Worksheet
ActiveSheet.AutoFilterMode = False
Set rData = Range("a1:ab14")
rData.AutoFilter field:=1, Criteria1:="p"
.AutoFilter.Range.Copy (I get invalid or unqualified reference) with .AutoFilter highlighted
Set wsNew = Sheets.Add
With wsNew
.Range("A1").PasteSpecial
.Range("b:b,d:d,p,r:r").Delete
End With
End Sub

See above for the issue not sure how to fix it if I remove the period from .AutoFilter I get a run-time error 424

 

[njlevi49]

Sub CopyData()
Dim rData As Range
Dim wsNew As Worksheet
ActiveSheet.AutoFilterMode = False
Set rData = Range("a1:ab14")
rData.AutoFilter field:=1, Criteria1:="p"
.AutoFilter.Range.Copy (I get invalid or unqualified reference) with .AutoFilter highlighted
Set wsNew = Sheets.Add
With wsNew
.Range("A1").PasteSpecial
.Range("b:b,d:d,p,r:r").Delete
End With
End Sub

See above for the issue not sure how to fix it if I remove the period from .AutoFilter I get a run-time error 424

 

[Firefly2012]

Apologies - I thought I had corrected that omission. The code should be:

Sub CopyData()
Dim rData As Range
Dim wsNew As Worksheet

ActiveSheet.AutoFilterMode = False
Set rData = Range("A1:Ab14")
rData.AutoFilter field:=1, Criteria1:="p"
Activesheet.AutoFilter.Range.Copy
Set wsNew = Sheets.Add
With wsNew
    .Range("A1").PasteSpecial
    .Range("b:b,d:d,p:p,r:r").Delete
End With


End Sub

 

[njlevi49]

Thanks that worked the only other problem with this is I just need the values not the formulas I am not sure how or where I would place the Pastespecial Operation Values. do you also know a book that would provide mw with different VBA samples for all occasions? Again Thank you

 

[shg]

.Range("A1").PasteSpecial Paste:=xlPasteValues

 

[njlevi49]

Thanks it worked but only after I removed Paste:

 

[shg]

You're welcome.