Previous Topic Next Topic

Problem in Macro for formating error 1004.

Closed
Vicky - Jan 7, 2009 at 10:29 AM
phdhopes Posts 21 Registration date Wednesday September 17, 2008 Status Member Last seen May 11, 2009 - Jan 7, 2009 at 12:19 PM
Hello,
All I am new in forum, I am not a programmer or any macro expert but facing a problem with our ERP software which our company is using its generates a report of purchase and sales in a excel which does not solve my problem I have to format it with the help of autofilters and paste special but it utilized too much of my time and I have to submit the reports as early as possible than I got the idea to create a macro for formating the reports but its giving me a error message of 1004 ActiveSheet.PasteSpecial I don't know what is means.

Please some body provide me some help on it here I am giving the code if possible please send me the codes.

Sub purchase()
'
' purchase Macro
' Macro recorded 6/25/2002 by Santosh
'
' Keyboard Shortcut: Ctrl+m
'
ActiveCell.Rows("1:6").EntireRow.Select
ActiveCell.Offset(5, 0).Range("A1").Activate
Selection.Delete Shift:=xlUp
ActiveCell.Offset(0, 1).Columns("A:A").EntireColumn.Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
ActiveCell.Offset(0, -1).Range("A1").Select
ActiveCell.FormulaR1C1 = "S No."
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "Date"
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "Voucher No."
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "Bill Date"
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "Bill No."
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "GRN Date"
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "GRN No."
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "Item Code"
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "Item Name"
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "Party Name"
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "Tin No."
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "Tax Code"
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 11
ActiveCell.Columns("A:G").EntireColumn.Select
Selection.Delete Shift:=xlToLeft
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
ActiveCell.Offset(1, -11).Range("A1").Select
ActiveCell.FormulaR1C1 = "=RC[11]"
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "=RC[11]"
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "=R[1]C[9]"
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "=R[1]C[9]"
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "=R[2]C[7]"
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "=R[1]C[7]"
ActiveCell.Offset(0, -1).Range("A1").Select
ActiveCell.FormulaR1C1 = "=R[1]C[7]"
ActiveCell.Offset(0, 2).Range("A1").Select
ActiveCell.FormulaR1C1 = "=R[4]C[6]"
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "=R[4]C[6]"
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "=R[1]C[5]"
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "=R[2]C[4]"
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "=R[3]C[3]"
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
ActiveCell.Cells.Select
Selection.AutoFilter
ActiveCell.Offset(4, 4).Range("A1").Select
Selection.AutoFilter Field:=2, Criteria1:="="
ActiveCell.Offset(-2, -3).Range("A1:K803").Select
ActiveCell.Offset(-2, 0).Range("A1").Select
Selection.AutoFilter Field:=2
ActiveCell.Offset(1, 0).Range("A1:K1").Select
Selection.Copy
ActiveCell.Offset(-1, 0).Range("A1").Select
Selection.AutoFilter Field:=2, Criteria1:="="
ActiveCell.Offset(2, 0).Range("A1:K803").Select
ActiveSheet.PasteSpecial
Application.CutCopyMode = False
ActiveCell.Cells.Select
Selection.Copy
ActiveCell.Offset(3, 1).Range("A1").Select
Selection.AutoFilter Field:=2
ActiveCell.Cells.Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveCell.Offset(0, 1).Columns("A:K").EntireColumn.Select
Selection.ColumnWidth = 13.71
Selection.ColumnWidth = 17
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 7
ActiveCell.Offset(16, 9).Range("A1").Select
ActiveCell.Offset(0, -2).Columns("A:A").EntireColumn.ColumnWidth = 26.71
ActiveCell.Offset(0, -1).Columns("A:A").EntireColumn.ColumnWidth = 23.57
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Selection.AutoFilter Field:=1, Criteria1:="Bill :"
ActiveCell.Offset(-14, 0).Rows("1:835").EntireRow.Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
Selection.AutoFilter Field:=1, Criteria1:="D.N. :"
Selection.Delete Shift:=xlUp
Selection.AutoFilter Field:=1, Criteria1:="Items :"
Selection.Delete Shift:=xlUp
Selection.AutoFilter Field:=1, Criteria1:="Order :"
Selection.Delete Shift:=xlUp
Selection.AutoFilter Field:=1
ActiveCell.Offset(0, 14).Range("A1").Select
Selection.AutoFilter Field:=15, Criteria1:="<>Voucher Totals", Operator:= _
xlAnd
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Selection.AutoFilter Field:=1, Criteria1:="="
ActiveCell.Offset(2, 0).Rows("1:403").EntireRow.Select
Selection.Delete Shift:=xlUp
Selection.AutoFilter Field:=1
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 11
Selection.AutoFilter Field:=15
ActiveCell.Offset(-3, 14).Range("A1").Select
Selection.AutoFilter Field:=15, Criteria1:="<>Voucher Totals", Operator:= _
xlAnd
ActiveCell.Range("A1:A233").Select
Selection.ClearContents
Selection.AutoFilter Field:=15
ActiveCell.Range("A1:P1").Select
Selection.Delete Shift:=xlUp
ActiveCell.Offset(8, 0).Range("A1").Select
Selection.AutoFilter Field:=15, Criteria1:="="
ActiveCell.Offset(-7, 0).Rows("1:302").EntireRow.Select
ActiveCell.Offset(-7, -4).Range("A1").Activate
Selection.Delete Shift:=xlUp
Selection.AutoFilter Field:=15
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 16
ActiveWindow.ScrollColumn = 17
ActiveWindow.ScrollColumn = 16
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
ActiveCell.Cells.Select
Selection.Font.Bold = False
ActiveCell.Rows("1:1").EntireRow.Select
Selection.Font.Bold = True
ActiveCell.Offset(1, 0).Range("A1:A311").Select
Selection.ClearContents
ActiveCell.Select
ActiveCell.FormulaR1C1 = "1"
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "2"
ActiveCell.Offset(-1, 0).Range("A1:A2").Select
Selection.AutoFill Destination:=ActiveCell.Range("A1:A108"), Type:= _
xlFillDefault
ActiveCell.Range("A1:A108").Select
ActiveWindow.ScrollRow = 100
ActiveWindow.ScrollRow = 95
ActiveWindow.ScrollRow = 89
ActiveWindow.ScrollRow = 81
ActiveWindow.ScrollRow = 71
ActiveWindow.ScrollRow = 60
ActiveWindow.ScrollRow = 51
ActiveWindow.ScrollRow = 39
ActiveWindow.ScrollRow = 31
ActiveWindow.ScrollRow = 22
ActiveWindow.ScrollRow = 15
ActiveWindow.ScrollRow = 9
ActiveWindow.ScrollRow = 4
ActiveWindow.ScrollRow = 1
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 7
ActiveCell.Offset(0, 8).Columns("A:A").EntireColumn.EntireColumn.AutoFit
ActiveCell.Offset(0, 9).Columns("A:A").EntireColumn.EntireColumn.AutoFit
ActiveWindow.SmallScroll ToRight:=3
ActiveCell.Offset(0, 10).Columns("A:A").EntireColumn.EntireColumn.AutoFit
ActiveCell.Offset(0, 11).Columns("A:A").EntireColumn.EntireColumn.AutoFit
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 16
ActiveCell.Offset(0, 23).Columns("A:A").EntireColumn.Select
Selection.Insert Shift:=xlToRight
ActiveCell.Select
ActiveCell.FormulaR1C1 = "Taxable Amount"
ActiveCell.Offset(0, -5).Columns("A:M").EntireColumn.Select
Selection.Style = "Comma"
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
ActiveCell.Offset(0, -18).Range("A1").Select
End Sub

with best wishes
Thanks
Vicky
Related:

1 response

Answer 1 / 1
phdhopes Posts 21 Registration date Wednesday September 17, 2008 Status Member Last seen May 11, 2009 2
Jan 7, 2009 at 12:19 PM
Hi,

I think that you application has been configurated to read specif language.


When using paste special you must select - whether text or numbers.
0

Similar discussions

Corel Draw: Error Reading file problem
clinton_fields2 -
clinton_fields2 -

2 responses
ps3 error problem 80023017 how can i fix it
siendy -
Bobstew -

2 responses
Instagram caption issue
giddensg -
Rw -

7 responses
error code 230000
Rajvir -
dwebb -

1 response
Root directory is full or errors during pasting
5468960 -
aryann9152 -

5 responses