Insert shift down issue macro issue

**CC64** · 10-15-2012, 04:17 AM

Hi,

Could someone please give me some guidance with an issue I am having.

I'm trying to copy a range of data from one sheet, paste it to another, then insert and shift down one row.

The first example below works but if I'm working on another sheet it will always returns me to sheet two.

The second example I think is heading in the right direction(minus .Activate and .Select) but I don't how to incorporate the insert and shift down without encountering the issues of the first example.

Any help would be appreciated.

Thanks,

Simon

Sub example1()
Application.ScreenUpdating = False
Sheets("one").Activate
Sheets("one").Range("B5:Y5").Select
Selection.Copy
Sheets("two").Activate
Sheets("two").Range("B5:Y5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        Application.CutCopyMode = False
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Application.ScreenUpdating = False
    StartTimer
    
End Sub

Sub Example2()

            Dim srceRng As Range
            Dim destRng As Range
            Set srceRng = Workbooks("ontimemacro.xlsm").Sheets("one").Range("B5:Y5")
            Set destRng = Workbooks("ontimemacro.xlsm").Sheets("two").Range("B5:Y5")

srceRng.Copy
Workbooks("ontimemacro.xlsm").Sheets("two").Paste destRng

            
 StartTimer

End Sub

**TMS** · 10-15-2012, 04:53 AM

Try:

Sub example1()
Application.ScreenUpdating = False
Sheets("one").Range("B5:Y5").Copy
With Sheets("two")
   .Range("B5:Y5").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
   .Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
End With
Application.ScreenUpdating = False
StartTimer
End Sub

Regards, TMS

**CC64** · 10-15-2012, 05:08 AM

Hi TMS,

Thanks for your reply.

I ran your code but received a run time 'error 438 object doesn't support property or method' on the following line.

Thanks

Simon

.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

**TMS** · 10-15-2012, 05:24 AM

Oops:

Sub example1()
Application.ScreenUpdating = False
Sheets("one").Range("B5:Y5").Copy
With Sheets("two")
   With .Range("B5:Y5")
      .PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,  _
           SkipBlanks:=False, Transpose:=False
      .Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
   End With
End With
Application.ScreenUpdating = True ' <<<< also changed this from False
StartTimer
End Sub

Regards, TMS

**CC64** · 10-15-2012, 09:23 PM

Thanks again for your help TMS.

For some reason a compile error was triggered in the StartTimer macro.

I changed the name of the procedure which seemed to fixed the problem???

It's working so that's all that matters.

Cheers,

Simon

**TMS** · 10-15-2012, 10:57 PM

You're welcome.

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save