activesheet

[kevin carter]

Hi
i have a workbook that has 53 worksheets
52 of the worksheets are name week numbers ie week 1 , week 2 , week
3..................week 52
i have on worksheet called graph
each week data is entered onto the relevent week number worksheet
when i click a button the data is transfered to the graph worksheet
what i would like to do is after the data is pasted onto the graph
worksheet return to the sheet i was working on
ie if i on worksheet week 2 i click the button
i want to return to week 2


thanks

kevin

 

[Dave Peterson]

Most times, you don't have to change sheets to work on them. So maybe your code
could be tweaked to work on the objects instead of activating sheets.

But I don't have any specific suggestions without seeing your code.

Another way is to keep track of where you started, do the work, and then go
back:

Dim ActCell as range
set actcell = activecell
'do all your work here
application.goto actcell

 

[kevcar40]

Most times, you don't have to change sheets to work on them.  So maybe your code
could be tweaked to work on the objects instead of activating sheets.

But I don't have any specific suggestions without seeing your code.

Another way is to keep track of where you started, do the work, and then go
back:

Dim ActCell as range
set actcell = activecell
'do all your work here
application.goto actcell







--

Dave Peterson- Hide quoted text -

- Show quoted text -

Dave the code is below
thanks

Sub Move_data()
Application.ScreenUpdating = False

Range("cq45:dh81").Select ' on the week sheet here
Selection.Copy

Sheets("Graph").Select
Range("AC1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Range("AC3,AC12,AC23,AC32").Select
Selection.FormulaR1C1 = "Sat"
Range("AE3,AE12,AE23,AE32").Select
Selection.FormulaR1C1 = "Sun"
Range("AG3,AG12,AG23,AG32").Select
Selection.FormulaR1C1 = "Mon"
Range("AI3,AI12,AI23,AI32").Select
Selection.FormulaR1C1 = "Tue"
Range("AK3,AK12,AK23,AK32").Select
Selection.FormulaR1C1 = "Wed"
Range("AM3,AM12,AM23,AM32").Select
Selection.FormulaR1C1 = "Thu"
Range("AO3,AO12,AO23,AO32").Select
Selection.FormulaR1C1 = "Fri"
Range("AQ3,AQ12,AQ23,AQ32").Select
Selection.FormulaR1C1 = "Weekly Total"
Range("AS3,AS12,AS23,AS32").Select
Selection.FormulaR1C1 = "YTD"

Application.ScreenUpdating = True
End Sub

 

[kevcar40]

Most times, you don't have to change sheets to work on them.  So maybe your code
could be tweaked to work on the objects instead of activating sheets.

But I don't have any specific suggestions without seeing your code.

Another way is to keep track of where you started, do the work, and then go
back:

Dim ActCell as range
set actcell = activecell
'do all your work here
application.goto actcell







--

Dave Peterson- Hide quoted text -

- Show quoted text -

Dave
just inserted your piece of code this works fine thanks

kevin

 

[Dave Peterson]

Here's what I meant about not selecting the sheets and selecting the ranges:

Option Explicit
Sub Move_data()
Application.ScreenUpdating = False

ActiveSheet.Range("cq45:dh81").Copy

With Worksheets("Graph")
.Range("AC1").PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False

.Range("AC3,AC12,AC23,AC32").Value = "Sat"
.Range("AE3,AE12,AE23,AE32").Value = "Sun"
.Range("AG3,AG12,AG23,AG32").Value = "Mon"
.Range("AI3,AI12,AI23,AI32").Value = "Tue"
.Range("AK3,AK12,AK23,AK32").Value = "Wed"
.Range("AM3,AM12,AM23,AM32").Value = "Thu"
.Range("AO3,AO12,AO23,AO32").Value = "Fri"
.Range("AQ3,AQ12,AQ23,AQ32").Value = "Weekly Total"
.Range("AS3,AS12,AS23,AS32").Value = "YTD"
End With

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

End Sub