Last Active Sheet

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Freddy - 21 Nov 2007 18:39 GMT

I am trying to put together a very simple piece of code that copies data from
one sheet and pastes into another

However every time a new worksheet is created the code cannot find the last
active sheet

Example:

Sheets.Add After:=Sheets(Sheets.Count)
Sheets("APS Structure").Select
Cells.Select
Range("AD7").Activate
Selection.Copy
Sheets("Sheet2").Select
Range("A1").Select
ActiveSheet.Paste
Range("A1").Select

How do I change the "sheet2" to the last active sheet I was viewing?

Your help is much appreciated

Reply to this Message

Jim Thomlinson - 21 Nov 2007 18:49 GMT

You are better off to avoid the selecting in the first place. Rarely in a
macro do you actually need to select anything...

dim wks as worksheet

set wks = activesheet
Sheets.Add After:=Sheets(Sheets.Count)
Sheets("APS Structure").Cells.Copy _
Destination:=wks.range("A1")

Signature

HTH...

Jim Thomlinson

> I am trying to put together a very simple piece of code that copies data from
> one sheet and pastes into another
[quoted text clipped - 17 lines]
>
> Your help is much appreciated

Reply to this Message

Jim Thomlinson - 21 Nov 2007 18:55 GMT

After re-reading your post are you trying to paste into the new sheet? If so
then just this will do...

Sheets.Add After:=Sheets(Sheets.Count)
Sheets("APS Structure").Cells.Copy _
Destination:=Activesheet.range("A1")

Signature

HTH...

Jim Thomlinson

> You are better off to avoid the selecting in the first place. Rarely in a
> macro do you actually need to select anything...
[quoted text clipped - 27 lines]
> >
> > Your help is much appreciated

Reply to this Message

FSt1 - 21 Nov 2007 18:50 GMT

hi
use a variable.
Dim sht as worksheet 'variable
Sheets.Add After:=Sheets(Sheets.Count)
Set sht = ActiveSheet 'remember this sheet
Sheets("APS Structure").Select
Range("AD7").Copy
sht.Select 'go back to previous sheet
Range("A1").Select
ActiveSheet.Paste

regards
FSt1

> I am trying to put together a very simple piece of code that copies data from
> one sheet and pastes into another
[quoted text clipped - 17 lines]
>
> Your help is much appreciated

Reply to this Message

JMB - 21 Nov 2007 23:20 GMT

One way (the code should all be on one line)

Sheets("APS Structure").Cells.Copy
Sheets.Add(After:=Sheets(Sheets.Count)).Range("A1")

or set up a variable, especially if you will need the sheet later on:
Dim wksNew As Worksheet
Set wksNew = Sheets.Add(After:=Sheets(Sheets.Count))
Sheets("APS Structure").Cells.Copy wksNew.Range("A1")

> I am trying to put together a very simple piece of code that copies data from
> one sheet and pastes into another
[quoted text clipped - 17 lines]
>
> Your help is much appreciated

Reply to this Message

Chip Pearson - 22 Nov 2007 19:20 GMT

Try

With ThisWorkbook.Worksheets
.Item("Sheet1").Range("A1").Copy Destination:=.Item(.Count).Range("A1")
End With

Signature

Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)

>I am trying to put together a very simple piece of code that copies data
>from
[quoted text clipped - 19 lines]
>
> Your help is much appreciated

Reply to this Message