copying and pasting from source sheet to destination sheet without naming source sht?

Hi all, Below is a portion of quite a long section of code, it's like this because i am copying and pasting to another sheet (Statistics) and then going back to my original page (New Stats) copying...etc, I have 8 of these very long pieces of code because the exact same procedure is used by the other sheets but when it returns to a sheet to copy of course it has to return to the sheet the data is being copied from.....Is there anyway i code narrow it down to one piece of code that remembers which sheet it was on originally before opening "Statistics" or do i have to stick with having a seperate one for each sheet???

Regards,
Simon

Sub Stats()
Application.ScreenUpdating = False
Range("B1").Select
Selection.Copy
Sheets("Statistics").Visible = True
Sheets("Statistics").Select
Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Range("A1").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Sheets("New Stats").Select
ActiveCell.Offset(2, 0).Range("A1").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Statistics").Select
ActiveCell.Offset(0, 1).Range("A1").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Sheets("New Stats").Select
...............etc, And so on...!

You just need to save the active sheet name in a variable and use that in the commands.

OriginalSheet = ActiveSheet.Name

I frequently use this when I have a subroutine that should return to whichever sheet it was launched from.

Thanks for the reply Ice, could you show me where and how to incorporate that please?


Regards,
Simon

Hi Simon,

There's typically no need to select or activate sheets or ranges in VBA. It
slows things down, and it affects the user's current position in the
workbook (unless you set everything back at the end). Also, be careful with
ActiveCell unless it's really what you want. In many cases, you can
determine the target without knowing the active cell.

Here's an example of how you could rewrite your code (it may not work
exactly the same, but it should give you a starting point):

Sub Stats2()
Dim wsSource As Worksheet
Dim wsStats As Worksheet
Dim wsNewStats As Worksheet

Application.ScreenUpdating = False

Set wsSource = Worksheets("Sheet1") '/ or ActiveSheet
Set wsStats = Worksheets("Statistics")
Set wsNewStats = Worksheets("New Stats")

wsSource.Range("B1").Copy
wsStats.Range("A1").End(xlDown).Offset(1, _
0).PasteSpecial xlPasteValues
wsNewStats.Range("C3").Copy
wsStats.Range("A1").End(xlDown).Offset(0, _
1).PasteSpecial xlPasteValues

Set wsSource = Nothing
Set wsStats = Nothing
Set wsNewStats = Nothing

Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

--
Regards,

Jake Marx
www.longhead.com


[please keep replies in the newsgroup - email address unmonitored]


Simon Lloyd wrote:
> Hi all, Below is a portion of quite a long section of code, it's like
> this because i am copying and pasting to another sheet (Statistics)
> and then going back to my original page (New Stats) copying...etc, I
> have 8 of these very long pieces of code because the exact same
> procedure is used by the other sheets but when it returns to a sheet
> to copy of course it has to return to the sheet the data is being
> copied from.....Is there anyway i code narrow it down to one piece of
> code that remembers which sheet it was on originally before opening
> "Statistics" or do i have to stick with having a seperate one for each
> sheet???
>
> Regards,
> Simon
>
> Sub Stats()
> Application.ScreenUpdating = False
> Range("B1").Select
> Selection.Copy
> Sheets("Statistics").Visible = True
> Sheets("Statistics").Select
> Range("A1").Select
> Selection.End(xlDown).Select
> ActiveCell.Offset(1, 0).Range("A1").Select
> Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
> SkipBlanks:= _
> False, Transpose:=False
> Sheets("New Stats").Select
> ActiveCell.Offset(2, 0).Range("A1").Select
> Application.CutCopyMode = False
> Selection.Copy
> Sheets("Statistics").Select
> ActiveCell.Offset(0, 1).Range("A1").Select
> Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
> SkipBlanks:= _
> False, Transpose:=False
> Sheets("New Stats").Select
> ..............etc, And so on...!

Thanks for the worked example Jake, it looks good but the problem that i am trying to work around is that i have 12 (not 8!) different sheets that would need to call on that procedure but do the copying and pasting from the sheet that originated it and then end back at that sheet, so by your example it seems that i would still have to name all the sheets and (because my knowledge is limited!) i think it would become a very complex piece of coding and i dont think i am up to that, all i wanted to do is shrink the code in my workbook because i have had to duplicate code but change the sheet names that would call it.

Thats probably as clear as mud to you! guess i'm poor at explaining what i want as well as devising what i need haha!

Regards,
Simon

Hi Simon,

I'm not sure that you'd need to name all your sheets. As long as Statistics
and New Stats are named, you can use ActiveSheet for the others.

So in my example, try changing this line:

Set wsSource = Worksheets("Sheet1")

To this:

Set wsSource = ActiveSheet

That way, you'll always be working from the current sheet. Does that work?

--
Regards,

Jake Marx
www.longhead.com


[please keep replies in the newsgroup - email address unmonitored]


Simon Lloyd wrote:
> Thanks for the worked example Jake, it looks good but the problem
> that i am trying to work around is that i have 12 (not 8!) different
> sheets that would need to call on that procedure but do the copying
> and pasting from the sheet that originated it and then end back at
> that sheet, so by your example it seems that i would still have to
> name all the sheets and (because my knowledge is limited!) i think it
> would become a very complex piece of coding and i dont think i am up
> to that, all i wanted to do is shrink the code in my workbook because
> i have had to duplicate code but change the sheet names that would
> call it.
>
> Thats probably as clear as mud to you! guess i'm poor at explaining
> what i want as well as devising what i need haha!
>
> Regards,
> Simon