Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > ACTIVESHEET.PASTE not working.
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 69
Default ACTIVESHEET.PASTE not working.
On the code below, the "ActiveSheet.Paste" is not working in either section
of the IF . . . ELSE statement. I've tried recreating that process in
another MACRO and pasting it to this code with no luck.

Any suggestion will be welcomed.

Thank you in advance

Marco

With ActiveSheet.QueryTables.Add(Connection:= _
"FINDER;C:\Documents and Settings\marco.rodas\Application
Data\Microsoft\Queries\IC Trace Table.dqy" _
, Destination:=Range("A1"))
.Name = "Trace Table Query"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With


Yearanalysis = InputBox("What year?", "Year")

CurrentPeriod = InputBox("What period (SINGLE DIGIT 1-9)?", "Current
Period")
If CurrentPeriod = "" Then
Range("A1").Select
Selection.AutoFilter
Selection.AutoFilter Field:=8, Criteria1:=Yearanalysis
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Sheets("Sheet2").Select

ActiveSheet.Paste

Cells.Select
Cells.EntireColumn.AutoFit
Range("A1").Select
Sheets("Sheet1").Select
Application.CutCopyMode = False

Sheets("Sheet2").Select
Range("M1").Select
Selection.Sort Key1:=Range("M1"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Selection.Subtotal GroupBy:=13, Function:=xlCount, TotalList:=Array(13), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
ActiveSheet.Outline.ShowLevels RowLevels:=2
Columns("K:K").ColumnWidth = 14.14
Columns("L:L").ColumnWidth = 17

Else


Range("A1").Select
Selection.AutoFilter
Selection.AutoFilter Field:=8, Criteria1:=Yearanalysis
Selection.AutoFilter Field:=9, Criteria1:=CurrentPeriod
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Sheets("Sheet3").Select
Range("A1").Select

ActiveSheet.Paste

Cells.EntireColumn.AutoFit
Range("A1").Select
Sheets("Sheet1").Select
Application.CutCopyMode = False

Sheets("Sheet3").Select
Range("M1").Select
Selection.Sort Key1:=Range("M1"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Selection.Subtotal GroupBy:=13, Function:=xlCount, TotalList:=Array(13), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
ActiveSheet.Outline.ShowLevels RowLevels:=2
Columns("K:K").ColumnWidth = 14.14
Columns("L:L").ColumnWidth = 17
End If



Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
ben ben is offline
external usenet poster
  
Posts: 232
Default ACTIVESHEET.PASTE not working.
sheets("Sheet2").activate
not select

--
When you lose your mind, you free your life.


"Marco" wrote:

On the code below, the "ActiveSheet.Paste" is not working in either section
of the IF . . . ELSE statement. I've tried recreating that process in
another MACRO and pasting it to this code with no luck.

Any suggestion will be welcomed.

Thank you in advance

Marco

With ActiveSheet.QueryTables.Add(Connection:= _
"FINDER;C:\Documents and Settings\marco.rodas\Application
Data\Microsoft\Queries\IC Trace Table.dqy" _
, Destination:=Range("A1"))
.Name = "Trace Table Query"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With


Yearanalysis = InputBox("What year?", "Year")

CurrentPeriod = InputBox("What period (SINGLE DIGIT 1-9)?", "Current
Period")
If CurrentPeriod = "" Then
Range("A1").Select
Selection.AutoFilter
Selection.AutoFilter Field:=8, Criteria1:=Yearanalysis
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Sheets("Sheet2").Select

ActiveSheet.Paste

Cells.Select
Cells.EntireColumn.AutoFit
Range("A1").Select
Sheets("Sheet1").Select
Application.CutCopyMode = False

Sheets("Sheet2").Select
Range("M1").Select
Selection.Sort Key1:=Range("M1"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Selection.Subtotal GroupBy:=13, Function:=xlCount, TotalList:=Array(13), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
ActiveSheet.Outline.ShowLevels RowLevels:=2
Columns("K:K").ColumnWidth = 14.14
Columns("L:L").ColumnWidth = 17

Else


Range("A1").Select
Selection.AutoFilter
Selection.AutoFilter Field:=8, Criteria1:=Yearanalysis
Selection.AutoFilter Field:=9, Criteria1:=CurrentPeriod
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Sheets("Sheet3").Select
Range("A1").Select

ActiveSheet.Paste

Cells.EntireColumn.AutoFit
Range("A1").Select
Sheets("Sheet1").Select
Application.CutCopyMode = False

Sheets("Sheet3").Select
Range("M1").Select
Selection.Sort Key1:=Range("M1"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Selection.Subtotal GroupBy:=13, Function:=xlCount, TotalList:=Array(13), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
ActiveSheet.Outline.ShowLevels RowLevels:=2
Columns("K:K").ColumnWidth = 14.14
Columns("L:L").ColumnWidth = 17
End If



Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 69
Default ACTIVESHEET.PASTE not working.
Made the change and still did not work. It keeps hanging up on 'paste'
command.

Thanks.

"ben" wrote:

sheets("Sheet2").activate
not select

--
When you lose your mind, you free your life.


"Marco" wrote:

On the code below, the "ActiveSheet.Paste" is not working in either section
of the IF . . . ELSE statement. I've tried recreating that process in
another MACRO and pasting it to this code with no luck.

Any suggestion will be welcomed.

Thank you in advance

Marco

With ActiveSheet.QueryTables.Add(Connection:= _
"FINDER;C:\Documents and Settings\marco.rodas\Application
Data\Microsoft\Queries\IC Trace Table.dqy" _
, Destination:=Range("A1"))
.Name = "Trace Table Query"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With


Yearanalysis = InputBox("What year?", "Year")

CurrentPeriod = InputBox("What period (SINGLE DIGIT 1-9)?", "Current
Period")
If CurrentPeriod = "" Then
Range("A1").Select
Selection.AutoFilter
Selection.AutoFilter Field:=8, Criteria1:=Yearanalysis
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Sheets("Sheet2").Select

ActiveSheet.Paste

Cells.Select
Cells.EntireColumn.AutoFit
Range("A1").Select
Sheets("Sheet1").Select
Application.CutCopyMode = False

Sheets("Sheet2").Select
Range("M1").Select
Selection.Sort Key1:=Range("M1"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Selection.Subtotal GroupBy:=13, Function:=xlCount, TotalList:=Array(13), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
ActiveSheet.Outline.ShowLevels RowLevels:=2
Columns("K:K").ColumnWidth = 14.14
Columns("L:L").ColumnWidth = 17

Else


Range("A1").Select
Selection.AutoFilter
Selection.AutoFilter Field:=8, Criteria1:=Yearanalysis
Selection.AutoFilter Field:=9, Criteria1:=CurrentPeriod
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Sheets("Sheet3").Select
Range("A1").Select

ActiveSheet.Paste

Cells.EntireColumn.AutoFit
Range("A1").Select
Sheets("Sheet1").Select
Application.CutCopyMode = False

Sheets("Sheet3").Select
Range("M1").Select
Selection.Sort Key1:=Range("M1"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Selection.Subtotal GroupBy:=13, Function:=xlCount, TotalList:=Array(13), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
ActiveSheet.Outline.ShowLevels RowLevels:=2
Columns("K:K").ColumnWidth = 14.14
Columns("L:L").ColumnWidth = 17
End If



Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 129
Default ACTIVESHEET.PASTE not working.
Hi,

check Application.CutCopyMode like:

Selection.Copy
MsgBox Application.CutCopyMode
Sheets("Sheet2").Select
MsgBox Application.CutCopyMode
ActiveSheet.Paste

if the 2nd msgbox shows '0', then selecting 'Sheet2' turns off the
copy mode. for instance,

'Module1
Sub Test()
Sheets("Sheet1").Activate
Range("A1").Copy
'On Error Resume Next
'Application.EnableEvents = False
Sheets("Sheet2").Activate
'Application.EnableEvents = True
'On Error GoTo 0
MsgBox Application.CutCopyMode
End Sub

'Sheet2 module
Private Sub Worksheet_Activate()
Application.MoveAfterReturnDirection = xlDown
End Sub

if so, the comment lines in the above is one of solutions.
or:

Sub Test2()
Dim rngCopy As Range
Sheets("Sheet1").Activate
Range("A1").Select
Set rngCopy = Selection
Sheets("Sheet2").Activate
rngCopy.Copy
ActiveCell.PasteSpecial
End Sub

--
HTH,

okaizawa


Marco wrote:
Made the change and still did not work. It keeps hanging up on 'paste'
command.

Thanks.

"ben" wrote:


sheets("Sheet2").activate
not select

--
When you lose your mind, you free your life.


"Marco" wrote:


On the code below, the "ActiveSheet.Paste" is not working in either section
of the IF . . . ELSE statement. I've tried recreating that process in
another MACRO and pasting it to this code with no luck.

Any suggestion will be welcomed.

Thank you in advance

Marco

With ActiveSheet.QueryTables.Add(Connection:= _
"FINDER;C:\Documents and Settings\marco.rodas\Application
Data\Microsoft\Queries\IC Trace Table.dqy" _
, Destination:=Range("A1"))
.Name = "Trace Table Query"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With


Yearanalysis = InputBox("What year?", "Year")

CurrentPeriod = InputBox("What period (SINGLE DIGIT 1-9)?", "Current
Period")
If CurrentPeriod = "" Then
Range("A1").Select
Selection.AutoFilter
Selection.AutoFilter Field:=8, Criteria1:=Yearanalysis
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Sheets("Sheet2").Select

ActiveSheet.Paste

Cells.Select
Cells.EntireColumn.AutoFit
Range("A1").Select
Sheets("Sheet1").Select
Application.CutCopyMode = False

Sheets("Sheet2").Select
Range("M1").Select
Selection.Sort Key1:=Range("M1"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Selection.Subtotal GroupBy:=13, Function:=xlCount, TotalList:=Array(13), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
ActiveSheet.Outline.ShowLevels RowLevels:=2
Columns("K:K").ColumnWidth = 14.14
Columns("L:L").ColumnWidth = 17

Else


Range("A1").Select
Selection.AutoFilter
Selection.AutoFilter Field:=8, Criteria1:=Yearanalysis
Selection.AutoFilter Field:=9, Criteria1:=CurrentPeriod
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Sheets("Sheet3").Select
Range("A1").Select

ActiveSheet.Paste

Cells.EntireColumn.AutoFit
Range("A1").Select
Sheets("Sheet1").Select
Application.CutCopyMode = False

Sheets("Sheet3").Select
Range("M1").Select
Selection.Sort Key1:=Range("M1"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Selection.Subtotal GroupBy:=13, Function:=xlCount, TotalList:=Array(13), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
ActiveSheet.Outline.ShowLevels RowLevels:=2
Columns("K:K").ColumnWidth = 14.14
Columns("L:L").ColumnWidth = 17
End If



Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Working with Macros-Activesheet.Paste lunar1085 Excel Discussion (Misc queries) 0 February 3rd 06 04:13 PM
Macro working in background ie not on activesheet Dynamiteskippy Excel Programming 1 May 28th 05 03:53 AM
ActiveSheet.Next.Select not working! Ron McCormick[_2_] Excel Programming 2 December 15th 03 12:42 PM


All times are GMT +1. The time now is 05:59 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement