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
|
Similar Threads
