What is the equivalent code in VB for Micorsoft Excel Macro code Range("C2") for paste special operation

Question

If I want to copy a range of cells from one sheet to another sheet in macro operation I use the following codes: 1.sheets ("something").select 2. range("k1:k45").select 3. selection.copy 4. sheets("somethingelse").select. 5. Range("C1").select etc. If I copy these codes and paste in a command button and run, the 5th step code Range("C1") is taken as error. All other macro codes are being accepted as such in command buttons. Why is it so? What is the alternate code?. This happens during paste special operation. The program is running while in macro operation for the same codes. But the same codes when copied and pasted in command button, the program does not run through the click of the command button and an error is displayed due to step 5 above.

Answer 1

As Richard said, it is good to use the Macro Recorder to help you figure out stuff like that. However, one thing I noticed is that you are writing extra code that you don't need to which will slow the macro down and is just extra code to type.

It may be due to the fact that you are using Select instead of Activate and possibly due to the fact of what sheet the command button is on. Below is an example of two macro codes. The first one is commented with apostrophes. The second one is not. The first one came from the macro recorder. The second macro I wrote. The two macro codes will give the same result, but the one I wrote is shorter and especially notice that I don't use select. I reduced 6 lines of code down to just 2 lines of code. I utilized paste special to copy just the values in on both. To help with some possible confusion, VBA programmers will use a space followed by an underscore to continue one line of code onto a second line. This makes it easier to read so that the coding doesn't extend out past the right side of the screen. So if you look at the line that I wrote that starts out as Sheets("Sheet2"), you'll notice that those code line continuations wrap that one line of code onto three lines for easier readability.

'Sheets("Dock_Recordings").Select
'Range("C1:H13").Select
'Selection.Copy
'Sheets("Sheet2").Select
'Range("C7").Select
'Selection.PasteSpecial Paste:=xlPasteValues, _
' Operation:=xlNone, SkipBlanks _
' :=False, Transpose:=False

Sheets("Dock_Recordings").Range("C1:H13").Copy
Sheets("Sheet2").Range("C7").PasteSpecial _
Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False


You don't have to select stuff in order to copy and paste. I think if you simplify your code down so that you don't have to use Select statements your coding should work. And if you compare the two codes. The typing for mine is about half as long as the Macro Recorder's.

PS -

The first line of the Macro Recorder's should end in:

.Select

The first line of mine should end in:

.Range("C1:H13").Copy


For some reason it is cutting the end of those lines of code off.

The space and underscore for line continuation can be entered right into your programming code and work. It isn't just used for publishing purposes. When I originally encountered line continuations I thought they couldn't be entered directly into the code, so I thought I would make sure you knew they could.

Answer 2

Answers or CISN 103.9 Edmonton & Country 105 Calgary CISN: 7am Song = Start A Band 7:15 Word = Combination BBFF = Crystal Chandelier 9am Song = Kiss A Girl Work Word = Pencil 10:15 Word = Dab 12pm Artist = Sugarland - worked for me 2pm Song = Don't Take The Girl 2:15 Word = Outside 4:15 Word = Player 5pm Song = Chattahoochee Most Wanted = Then - worked for me 8:15 Word = Outdoors Wine Country Trivia = 15 Country 105: 7am Song = Online Work Word = Machine 1st Song @ Lunch = Love Story Top 3 @ 3 = Kiss A Girl 5pm Song = No Shoes, No Shirt, No Problem Trivia for Both: CA Free Samples = Sign me up! Games = 20 How Stuff Works = Balance your checkbook Sleuth = Banjo This Day In Canadian History = A All The Rest = B Happy Hump Day everyone

Answer 3

Record the macro (using the MACRO RECORDER in Excel), then view the VBA code. It will all be written out for you.

Answer 4

Try Range( "C1:C1" ). I think a single-cell range has to be specified this way.