Last Active Sheet

[Guest]

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

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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

 

[Chip Pearson]

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)