Last Active Sheet
 

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

Last Active Sheet
 

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")
--
HTH...

Jim Thomlinson


"Freddy" wrote:

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

Last Active Sheet
 

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
"Freddy" wrote:

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

Last Active Sheet
 

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")

--
HTH...

Jim Thomlinson


"Jim Thomlinson" wrote:

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")
--
HTH...

Jim Thomlinson


"Freddy" wrote:

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

Last Active Sheet
 

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")



"Freddy" wrote:

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

Last Active Sheet
 

Try

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


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



"Freddy" wrote in message
...
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