Does macro do action on other sheets without activating them?

[slowokan]

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.

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

 

[starl]

Sure. And that's actually one of the better programming practices.

You see these 3 lines:

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:

Sheets("Drafts").Range("Z10:AA46").Copy

for the paste

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

make sense?

 

[slowokan]

Thank you very much, starl...

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

 

[slowokan]

Thanks, starl...

I tried. It worked. Appreciated...

 

[starl]

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]

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

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,

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

And whatever other variations you think of.

 

[slowokan]

Thank you very much starl and Fazza...

 

[slowokan]

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]

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:

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]

Thanks, starl. That was very useful. Now, my program is almost finished and working good. Appreciated..