Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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...! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copied formulas refer to destination sheet not source sheet | Excel Worksheet Functions | |||
sort source data sheet while destination shows same data | Excel Worksheet Functions | |||
Always keeping source formatting when copying and pasting in Excel | Excel Worksheet Functions | |||
How to copy a sheet and rename it with the value of two cells from the source sheet? | Excel Programming | |||
Hyperlinks - identifying source in destination sheet | Excel Discussion (Misc queries) |