Copy and Paste in Macro not working

[johnr1951]

I have a new spreadsheet (Book1) in which I populate cells via a query from a SQL data base. Data is placed at cell A1. It works.

I have a macro (ctl-F) that massages the data, add a few columns, does some totaling. Works.

I have another macro (ctl-H) that inserts four blank lines at the top of the spreadsheet (Book1), opens another spreadsheet (header.xls), gets the first three rows (header information for my newly created spreadsheet) and copies the data to the clipboard, goes back to the original spreadsheet (Book1), goes to cell A1 and pastes. This does not work. However, if I step through the macro, it does work.

Why does the macro work if I step through it, but not if I execute the macro with my ctl-key sequence?

If I can get the header macro to work, I want to call it from my ctl-F macro to eliminate the end user from having to execute two macros.

 

[Bill_Biggs]

Can you post the code?

Goto http://www.puremis.net/excel/downloads.shtml and download the tool that will turn your code into a postable code for this board.

 

[johnr1951]

Yes, here it is..

------------------
This code works
------------------
Sub fixwosql()
'
' fixwosql Macro
' Macro recorded 6/23/2008 by John R
'
' Keyboard Shortcut: Ctrl+Shift+F
'
Dim WOSheet As String
Dim selrow As Integer

WOSheet = ActiveWorkbook.Name
ActiveCell.SpecialCells(xlCellTypeLastCell).Select
selrow = ActiveCell.Row

Columns("N:N").Select
ActiveWindow.LargeScroll ToRight:=2
Columns("AA:AA").Select
Selection.Insert Shift:=xlToRight
Range("AA1").Select
ActiveCell.FormulaR1C1 = "total"
Range("AA2").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-20]:RC[-1])"
Range("AA2").Select
xa = Cells(2, 27).Address
xb = Cells(selrow, 27).Address
Selection.AutoFill Destination:=Range(xa + ":" + xb), Type:=xlFillDefault
Range("AA2:AA32").Select
ActiveWindow.LargeScroll ToRight:=1
Columns("AJ:AJ").Select
Selection.Insert Shift:=xlToRight
Range("AJ1").Select
ActiveCell.FormulaR1C1 = "total"
Range("AJ2").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-8]:RC[-1])"
Range("AJ2").Select
xa = Cells(2, 36).Address
xb = Cells(selrow, 36).Address
Selection.AutoFill Destination:=Range(xa + ":" + xb), Type:=xlFillDefault
Range("AJ2:AJ31").Select
Columns("G:G").Select
Selection.Insert Shift:=xlToRight
Range("G1").Select
ActiveCell.FormulaR1C1 = "average"
Range("G2").Select
Columns("G:G").ColumnWidth = 16.57
ActiveCell.FormulaR1C1 = "=IF(AND(RC[-2]<>0,RC[-1]<>0),RC[-2]/RC[-1],""N/A"")"
Range("G2").Select
xa = Cells(2, 7).Address
xb = Cells(selrow, 7).Address
Selection.AutoFill Destination:=Range(xa + ":" + xb), Type:=xlFillDefault
Range("G2:G31").Select
Columns("G:G").Select
Selection.NumberFormat = "0.00"
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Columns("B:B").Select
Selection.NumberFormat = "mm/dd/yy;@"

-----------------
Here is the code that should copy my heading information over ...
I tried a bunch of things to get this to work...
-----------------

Rows("1:1").Select
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Workbooks.Open Filename:="C:\Imports\rpthdg.xls"
xa = Cells(1, 1).Address
xb = Cells(3, 39).Address
Range(xa + ":" + xb).Select
'Rows("1:3").Select
'Range("AG1").Activate
Application.CutCopyMode = xlCopy
Selection.Copy
Windows(WOSheet).Activate
Range("A1").Select
'Cells(1, 1).Select
ActiveSheet.Paste
Rows("4:5").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
End Sub

 

[Bill_Biggs]

Well I tested it and it worked fine on my machine. Both when I hopped it through and when I ran it. I set up simulated data to let it carry out the same op. when it locks up, what kind of error does it say it is?

 

[johnr1951]

It dosn't lock up. Whn I press ctl-h. The header file opens up, and it looks like it is working, but the header worksheet is the last sheet displayed, and when I click on Book1, the header was not pasted.

If I step through the macro, that final screen I see is my Book1 spreadsheet with the heading information pasted where it should be, and two lines from the original Book1 deleted underneath the headings.

I have tried this on two systems. One, with XP Pro SP2 and Excel 2000, the other a WIN server 2003 with Excel 2003. Same results.

 

[Bill_Biggs]

I am sorry, I tried it again and it seems to be working here. I wish I could help more. IF you will hit "Reply" and type in the word "Bump" this will move your question bafk to the first page and maybe someone sharper can determine.