Copy / Paste Errors

  • Thread starter Thread starter Swimmer
  • Start date Start date
S

Swimmer

I have a workbook that copies data from another workbook into one
sheet. The data is taken into this sheet on the new workbook in the
raw format. When it is in the new workbook, I add an index, and copy
it to a new sheet, where it is formatted. The intention is that the
formatted sheet is updated, but you can see the updates on a sheet in
the original delivery format.
I`ve written three macros to do all this, and they seem to work fine
when called individually, but fail when called in sequence (they are
called in sequence on Workbook Open). I get a Paste Method of
Worksheet Class Failed (1004) error. Any idea why this is?

Code below:

Sub Copypasteinput()

Application.ScreenUpdating = False
Application.EnableEvents = False
Workbooks.Open Filename:= _
"\\............xls"
Application.EnableEvents = True
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Copy
Application.DisplayAlerts = False
ActiveWorkbook.Close
Sheets("Authorisecopy").Visible = True
Sheets("Authorisecopy").Select
Range("C1").Select
ActiveSheet.Paste
Range("C1").Select

End Sub

Sub AddIndex()
Range("A2").Select
Do Until ActiveCell.Offset(0, 2).Value = ""
ActiveCell.Value = ActiveCell.Offset(-1, 0) + 1
ActiveCell.Offset(1, 0).Select
Loop
End Sub

Sub CopytoDMDsort()

Set ws = Worksheets("DMDView")

' Select Data from AuthoriseCopy and copy to DMD View sheet
Sheets("Authorisecopy").Select
Range("A1").Select
Application.CutCopyMode = False
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Copy
Sheets("DMDView").Select
Sheets("Authorisecopy").Visible = False
Range("A1").Select
ActiveSheet.Paste Here is where the macro fails
Selection.Sort Key1:=Range("V2"), Order1:=xlDescending,
Key2:=Range("C2") _
, Order2:=xlAscending, Key3:=Range("D2"), Order3:=xlAscending,
Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom
' Find last row
iRow = ws.Cells(Rows.Count, 2).End(xlUp).Offset(1, 0).Row
ws.Cells(iRow, 1).Select
Do
If ActiveCell.Offset(0, 22).Value = "No" Then
Selection.EntireRow.Delete
If ActiveCell.Offset(0, 22).Value = "" Then
Selection.EntireRow.Delete
ActiveCell.Offset(-1, 0).Select
Loop Until ActiveCell.Value = "1"
' Insert Subtotals
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Subtotal GroupBy:=2, Function:=xlCount,
TotalList:=Array(6), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
Range("A1").Value = "Index"
Application.ScreenUpdating = True

End Sub
 
The 1st macro opens a workbook and closes it which changes the focus. You
need to activate the workbook before you continu.
 
Back
Top