understanding

[Darrell_Sarrasin via OfficeKB.com]

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?

 

[JLGWhiz]

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]

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!!

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?

 

[Gord Dibben]

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

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!!

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?