| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| I need to sort an active sheet using the col of the active cell | Excel Programming | |||
| Active Cell Copy And Paste Sheet to Sheet | New Users to Excel | |||
| How do i copy a active sheet to a new sheet with code and everything | Excel Programming | |||
| Copy my active sheet to a new sheet and open with an input form | Excel Programming | |||
| Copy from active sheet and paste into new sheet using info from cell in active | Excel Programming | |||
Hybrid Mode
