Copy and Paste in Macro not working

johnr1951

New Member
Joined
Jun 24, 2008
Messages
3
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.
 

Some videos you may like

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

johnr1951

New Member
Joined
Jun 24, 2008
Messages
3
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-known Member
Joined
Feb 6, 2007
Messages
1,216
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

New Member
Joined
Jun 24, 2008
Messages
3
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

Well-known Member
Joined
Feb 6, 2007
Messages
1,216
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,128,057
Messages
5,628,359
Members
416,314
Latest member
Dan99321

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top