Does macro do action on other sheets without activating them?

slowokan

New Member
Joined
Dec 6, 2011
Messages
19
Hi everybody.


I have a very simple problem. I want to assign a macro which copies some cells and pastes to next column. The button for this macro will be on Sheet1, but the macro will copy-paste the cells in Sheet3. I don't want the macro to select the Sheet3 and do the copy-paste thing. I want it work without activating the Sheet3. I want the file always stay on Sheet1.


The macro, I've created, is the following, but it selects Sheet3 and does the copy-paste.

Code:
Sub DENEME()
'
' DENEME Macro
'


'
    Sheets("Drafts").Select
    Range("Z10:AA46").Select
    Selection.Copy
    Range("X10:Y10").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
End Sub



Is it possible to create a macro without activating the other Sheet?


Thanks in advance, guys..;)
 
Last edited by a moderator:

Some videos you may like

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

starl

Administrator
Joined
Aug 16, 2002
Messages
5,969
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
  2. MacOS
Sure. And that's actually one of the better programming practices.

You see these 3 lines:
Code:
Sheets("Drafts").Select
    Range("Z10:AA46").Select
    Selection.Copy

well, if you are on sheet1 and want to stay on sheet1 but copy something on Drafts, then do this:
Code:
Sheets("Drafts").Range("Z10:AA46").Copy

for the paste
Code:
Sheets("Drafts").Range("X10:Y10").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

make sense?
 

slowokan

New Member
Joined
Dec 6, 2011
Messages
19
Thank you very much, starl...;)

I will try that, when I get to my cabin.
 

starl

Administrator
Joined
Aug 16, 2002
Messages
5,969
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

You're welcome :)
That's the one bad thing about the recorder - VERY literal.. it doesn't teach you how lines can be combined, code made more efficient (and pretty).
But then - that's what this site is for :)
 

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,368
for values only copying, you can use : range1.value = range2.value

If there are no dates, consider also to use .value2 = .value2

So, for an offset of 2 columns to the left, you could have

Code:
With Sheets("Drafts").Range("Z10:AA46")
  .Offset(, -2).Value = .Value
End With

Or offset (negative value for offset to the left, positive for offset to the right) as many columns as in the range being copied,

Code:
With Sheets("Drafts").Range("Z10:AA46")
  .Offset(, -.columns.count).Value = .Value
End With


And whatever other variations you think of. :)
 

slowokan

New Member
Joined
Dec 6, 2011
Messages
19

ADVERTISEMENT

Thank you very much starl and Fazza...;)
 

slowokan

New Member
Joined
Dec 6, 2011
Messages
19
How about this one? I wrote this code below, but it's giving error (Run-time error '438') at 2nd line. It says "Object doesn't support this property or method".


I guess there should be something after "Paste". But I don't know what to put there. I want there a regular "Paste", not "Paste Special".




Sub DENEME()
'
' DENEME Macro
'


'
Sheets("Results").Range("F9:F999").Copy
Sheets("Results").Range("F10:F1000").Paste
Sheets("Results").Range("G9:R999").Copy
Sheets("Results").Range("G10:R1000").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Results").Range("F9:G9") = "0"
Sheets("Results").Range("F9:G9").Font.ThemeColor = xlThemeColorDark1
Sheets("Results").Range("F9:G9").Font.TintAndShade = 0
Sheets("Results").Range("F9:G9").Font.Bold = True
Sheets("Results").Range("F9:G9").Interior.Pattern = xlSolid
Sheets("Results").Range("F9:G9").Interior.PatternColorIndex = xlAutomatic
Sheets("Results").Range("F9:G9").Interior.Color = 5287936
Sheets("Results").Range("F9:G9").Interior.TintAndShade = 0
Sheets("Results").Range("F9:G9").Interior.PatternTintAndShade = 0
End Sub
 

starl

Administrator
Joined
Aug 16, 2002
Messages
5,969
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
  2. MacOS
To bring up help in the VBE, place your cursor in the word, such as Copy, and press F1. That should jump you to the help on Copy, which should have some kind of example.

In the case you want to do a copy/paste then the syntax is:

range.copy destination:=range , the destination being the range where you want to paste. It's one line. Also, you don't have to specify the entire range you want to paste to, just the top left cell. So:

Code:
Sheets("Results").Range("F9:F999").Copy Destination:= Sheets("Results").Range("F10")

Note: this isn't the only solution, but it's the one using the code you have.
 

slowokan

New Member
Joined
Dec 6, 2011
Messages
19
Thanks, starl. That was very useful. Now, my program is almost finished and working good. Appreciated..;)
 

Watch MrExcel Video

Forum statistics

Threads
1,123,385
Messages
5,601,331
Members
414,446
Latest member
CRAVIN

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top