Return to previous worksheet after code pastes in another worksheet?
 

Hi Guys,

Im teaching myself vba [or trying to!] so my code attempts may reek of
amateur, but hell, we all started somewhere.


I have 35 very similar worksheets that grab financial data from a web
query. I only use one sheet at a time though. The active sheet then has
to format a load of data and paste it into a mastersheet.

What I need to do but can't grasp it is when I am in the active sheet and
do a cut of the relevant data, then tell my code to select the 'Data
Collection' worksheet and pastes it, I need to go back to the previous
sheet to clear some leftover data ready for the next time this particular
collection sheet is used.

I tried to set the activesheet name as a variable to refer back to it later
but it simply stays in the 'Data Collection' worksheet instead of going
back, or I generate a vba error.

Any help would be appreciated.

Ron

Return to previous worksheet after code pastes in another worksheet?
 

As usual, you should post your code. But, you need not select a worksheet or
a range to do something with it.

sheets("sheet2").range("a1")=1
sheets("sheet2").range("a1")=sheets("sheet1").rang e("a1")
sheets("sheet2").range("a1:a100").clearcontents

However, if you insist, try this
x=activesheet.name
application.goto sheets("sheet2").range("a1")
activecell=12
sheets(x).select

--
Don Guillett
SalesAid Software

"Ron" wrote in message
10.200...
Hi Guys,

Im teaching myself vba [or trying to!] so my code attempts may reek of
amateur, but hell, we all started somewhere.


I have 35 very similar worksheets that grab financial data from a web
query. I only use one sheet at a time though. The active sheet then has
to format a load of data and paste it into a mastersheet.

What I need to do but can't grasp it is when I am in the active sheet and
do a cut of the relevant data, then tell my code to select the 'Data
Collection' worksheet and pastes it, I need to go back to the previous
sheet to clear some leftover data ready for the next time this particular
collection sheet is used.

I tried to set the activesheet name as a variable to refer back to it
later
but it simply stays in the 'Data Collection' worksheet instead of going
back, or I generate a vba error.

Any help would be appreciated.

Ron

Return to previous worksheet after code pastes in another worksheet?
 

Ron

Do not select the Data Collection sheet.

ActiveSheet.Range("A1:J10").Cut _
Destination:=Worksheets("Data Collection").Range("A1")

Will leave you on the Activesheet.

To cut and paste to next available blank row on Data Collection sheet

ActiveSheet.Range("A1:J10").Cut _
Destination:=Worksheets("Data Collection").Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0)

Gord Dibben Excel MVP


On 04 Sep 2004 16:50:59 GMT, Ron wrote:

Hi Guys,

Im teaching myself vba [or trying to!] so my code attempts may reek of
amateur, but hell, we all started somewhere.


I have 35 very similar worksheets that grab financial data from a web
query. I only use one sheet at a time though. The active sheet then has
to format a load of data and paste it into a mastersheet.

What I need to do but can't grasp it is when I am in the active sheet and
do a cut of the relevant data, then tell my code to select the 'Data
Collection' worksheet and pastes it, I need to go back to the previous
sheet to clear some leftover data ready for the next time this particular
collection sheet is used.

I tried to set the activesheet name as a variable to refer back to it later
but it simply stays in the 'Data Collection' worksheet instead of going
back, or I generate a vba error.

Any help would be appreciated.

Ron

Return to previous worksheet after code pastes in another worksheet?
 

Don and Gord,

Thanks guys for answering so quick.

I was trying to copy from the source sheet and go to the destination sheet
and then back to the source sheet.

The examples you have both gave will help me no end in the future, thankyou
both.

Ron

Return to previous worksheet after code pastes in another worksheet?
 

As we said, you do not need to go back and forth. Just copy or cut to the
destination sheet.

--
Don Guillett
SalesAid Software

"Ron" wrote in message
10.200...
Don and Gord,

Thanks guys for answering so quick.

I was trying to copy from the source sheet and go to the destination sheet
and then back to the source sheet.

The examples you have both gave will help me no end in the future,
thankyou
both.

Ron