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


--
Simon Lloyd
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.excelforum.com/member.php...fo&userid=6708
View this thread: http://www.excelforum.com/showthread...hreadid=547555

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

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.


--
ICE9
------------------------------------------------------------------------
ICE9's Profile: http://www.excelforum.com/member.php...o&userid=13565
View this thread: http://www.excelforum.com/showthread...hreadid=547555

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

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


Regards,
Simon


--
Simon Lloyd
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.excelforum.com/member.php...fo&userid=6708
View this thread: http://www.excelforum.com/showthread...hreadid=547555

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

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

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

Thanks for the worked example Jake, it looks good but the problem that
am trying to work around is that i have 12 (not 8!) different sheet
that would need to call on that procedure but do the copying an
pasting from the sheet that originated it and then end back at tha
sheet, so by your example it seems that i would still have to name al
the sheets and (because my knowledge is limited!) i think it woul
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 ha
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 explainin
what i want as well as devising what i need haha!

Regards,
Simo

--
Simon Lloy
-----------------------------------------------------------------------
Simon Lloyd's Profile: http://www.excelforum.com/member.php...nfo&userid=670
View this thread: http://www.excelforum.com/showthread.php?threadid=54755

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

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