understanding
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 |
understanding
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 |
understanding
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 |
understanding
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? |
All times are GMT +1. The time now is 10:09 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com