VBA code without selecting sheets

[Afro_Cookie]

Is it possible to run code on sheets without selecting them before hand? Below are two examples. The top version works, but the bottom does not.

'This works    
    Sheets("Today").Select
    ActiveSheets.Range("A1").CurrentRegion.Offset(1, 0).Select
    Selection.Cut
    Sheets("Yesterday").select
    ActiveSheets.Range("A2").Paste

'This does not work
    Sheets("Today").Range("A1").CurrentRegion.Offset(1, 0).Select
    Selection.Cut
    Sheets("Yesterday").Range("A2").Paste

 

[Fluff]

Try it like

    Sheets("Today").Range("A1").CurrentRegion.Offset(1, 0).Cut
    Sheets("Yesterday").Range("A2").PasteSpecial

 

[dmt32]

try

    Dim rng As Range
    Set rng = Sheets("Today").Range("A1").CurrentRegion.Offset(1, 0)
    rng.Cut Sheets("Yesterday").Range("A2")

Dave

 

[Afro_Cookie]

Try it like

    Sheets("Today").Range("A1").CurrentRegion.Offset(1, 0).Cut
    Sheets("Yesterday").Range("A2").PasteSpecial

Thanks for this Fluff, it did generate a run-time error

Run-time error '1004'
PasteSpecial method of Range class failed

I tried dmt32's and it worked well.

 

[Afro_Cookie]

try

    Dim rng As Range
    Set rng = Sheets("Today").Range("A1").CurrentRegion.Offset(1, 0)
    rng.Cut Sheets("Yesterday").Range("A2")

Dave

Thanks! It worked well

 

[Fluff]

Yup, my code should have been

    Sheets("Today").Range("A1").CurrentRegion.Offset(1, 0).Cut   Sheets("Yesterday").Range("A2")

 

[Afro_Cookie]

Yup, my code should have been

    Sheets("Today").Range("A1").CurrentRegion.Offset(1, 0).Cut   Sheets("Yesterday").Range("A2")

Works perfectly! Thanks Fluff.

 

[dmt32]

Thanks! It worked well

Glad suggestions helped - think @Fluff having a rare off moment

Dave

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[Afro_Cookie]

Glad we could help & thanks for the feedback.

I could use some help refining my code if you're willing.

I want to do something similar to what you helped with above, but I have formatting on the cells I want to copy. Is there a way to remove the 'select' function but still be able to 'PasteSpecial'?

'Current code
ActiveSheet.Range("L6", ActiveSheet.Range("L6").End(xlDown).End(xlToRight)).Select
    Selection.Copy
    Sheets("Today").Select
    Range("K2").Select
    Selection.PasteSpecial Paste:=xlPasteAllUsingSourceTheme, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

'desired/attempted streamline
ActiveSheet.Range("L6", ActiveSheet.Range("L6").End(xlDown).End(xlToRight)).Copy Sheets("Today").Range("K2")
    PasteSpecial Paste:=xlPasteAllUsingSourceTheme, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False