Copy range from the worksheet preceding the active worksheet

**davidrias** · 03-31-2013, 07:11 PM

I need to copy a range of cells from a worksheet before the active worksheet. I can't use the name of the worksheet or its index number because these two things are always changing. So I need to reference the worksheet I want to copy from using only the active worksheet. My code looks like this:

Sub Macro6()
    Sheets("1").Select
    Range("C11:E11").Copy
    Sheets("2").Select
    Range("C11:E11").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
End Sub

So sheet "2" is my active and sheet "1" is the one I need the cells from. But I need to reference sheet 1 using the active sheet ("2")

**Norie** · 03-31-2013, 07:39 PM

The only way I can think of getting the previous sheet woud be to use the index of the current sheet and subtract one.


Sheets(ActiveSheet.Index).Range("C11:E11").Copy

ActiveSheet.Rnge("C11:E11").PasteSpecial xlPasteValues

**davidrias** · 03-31-2013, 07:49 PM

I am not getting how I would do that by looking at your code. This copies from the active worksheet:

Sheets(ActiveSheet.Index).Range("C11:E11").Copy

So how do you subtract 1 from the index?

Thank you so much for your time.

**davidrias** · 03-31-2013, 07:41 PM

I apologize, I figured it out. I was able to set both worksheets as variables like this:

'Create New Tab, Clear Previous Repeat Data (if any)
ActiveWorkbook.ActiveSheet.Unprotect

Dim wks As Worksheet
Set wks = ActiveSheet

ActiveWorkbook.ActiveSheet.Copy after:=ActiveWorkbook.ActiveSheet

Dim wks2 As Worksheet
Set wks2 = ActiveSheet

wks.Select
    Range("C11:E20").Copy
wks2.Select
    Range("C11:E20").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

 Set wks = Nothing
 Set wks2 = Nothing

**Norie** · 03-31-2013, 07:44 PM

Oops, forgot about Previous and Next.


Set wsPrev = ActiveSheet.Previous

Set wsNext = ActiveSheet.Next

**davidrias** · 03-31-2013, 07:50 PM

Oh perfect! Thanks, it seemed like a simple and reasonable thing to be able to do.

**Norie** · 03-31-2013, 08:01 PM

Sorry it should have been this.

Sheets(ActiveSheet.Index-1).Range("C11:E11").Copy

**davidrias** · 03-31-2013, 10:57 PM

Thank you I will tuck all of this away for future reference.

Copy range from the worksheet preceding the active worksheet

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Copy range from the worksheet preceding the active worksheet

Re: Copy range from the worksheet preceding the active worksheet

Re: Copy range from the worksheet preceding the active worksheet

Re: Copy range from the worksheet preceding the active worksheet

Re: Copy range from the worksheet preceding the active worksheet

Re: Copy range from the worksheet preceding the active worksheet

Re: Copy range from the worksheet preceding the active worksheet

Re: Copy range from the worksheet preceding the active worksheet

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions