I have recently ran macro record to get a quick code built to help me set up
for a copy feature I am trying to build. it works fine in the record but
when I place it in a button I get an error on the range line. the code is:
Private Sub CommandButton1_Click()
If Range("a1").Value = "test" Then
Sheets("information").Select
Range("A3:A21").Select
Selection.Copy
Sheets("information sheet").Select
Range("C1").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=
_
xlNone, SkipBlanks:=False, Transpose:=True
End If
End Sub

I get the error on the line Range("A3:A21").Select

Can someone help me understand why I am getting the error?

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/201005/1

The selects and selections are unnecessary. The problem was that VBA likes
to know which sheet it is supposed to be working on. I am not sure if the
names are correct but the syntax will work. Check the sheet names to be
sure there is no typo.


Sheets("information").Range("A3:A21").Copy
Sheets("information sheet").Range("C1") _
.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=True





"Darrell_Sarrasin via OfficeKB.com" <u33691@uwe wrote in message
news:a827455cf280e@uwe...
I have recently ran macro record to get a quick code built to help me set
up
for a copy feature I am trying to build. it works fine in the record but
when I place it in a button I get an error on the range line. the code
is:
Private Sub CommandButton1_Click()
If Range("a1").Value = "test" Then
Sheets("information").Select
Range("A3:A21").Select
Selection.Copy
Sheets("information sheet").Select
Range("C1").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats,
Operation:=
_
xlNone, SkipBlanks:=False, Transpose:=True
End If
End Sub

I get the error on the line Range("A3:A21").Select

Can someone help me understand why I am getting the error?

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/201005/1

Awesome thanks!!!

is there a way to get this to auto run based on the selection off a valdation
list? I can get it to run behind the button, but want it to auto run.

Thanks again for your help!!

JLGWhiz wrote:
The selects and selections are unnecessary. The problem was that VBA likes
to know which sheet it is supposed to be working on. I am not sure if the
names are correct but the syntax will work. Check the sheet names to be
sure there is no typo.

Sheets("information").Range("A3:A21").Copy
Sheets("information sheet").Range("C1") _
.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=True

I have recently ran macro record to get a quick code built to help me set
up
[quoted text clipped - 18 lines]

Can someone help me understand why I am getting the error?

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/201005/1

Make it event code.

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Me.Range("A1")).Value = "test" Then
Me.Range("A3:A21").Copy
Sheets("information sheet").Range("C1") _
.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=True
End If
Application.CutCopyMode = False
End Sub


Gord Dibben MS Excel MVP


On Tue, 18 May 2010 12:14:26 GMT, "Darrell_Sarrasin via OfficeKB.com"
<u33691@uwe wrote:

Awesome thanks!!!

is there a way to get this to auto run based on the selection off a valdation
list? I can get it to run behind the button, but want it to auto run.

Thanks again for your help!!

JLGWhiz wrote:
The selects and selections are unnecessary. The problem was that VBA likes
to know which sheet it is supposed to be working on. I am not sure if the
names are correct but the syntax will work. Check the sheet names to be
sure there is no typo.

Sheets("information").Range("A3:A21").Copy
Sheets("information sheet").Range("C1") _
.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=True

I have recently ran macro record to get a quick code built to help me set
up
[quoted text clipped - 18 lines]

Can someone help me understand why I am getting the error?