Excel Excel 2007 macro problem

[arthur.dobeson]

I keep getting a runtime error with the following macro, which is part of a list of routines when I convert an excel spreasheet to a csv file for uploading data into another program. The line highlighted below is the one shown when I click on the debug button. Range a4 is the last row of my data.

'inserts the word end below the last data

Range("a4").Select
Selection.End(x2Down).Select
ActiveCell.Offset(1, 20).Select
ActiveCell.FormulaR1C1 = "end"
Selection.Copy
Range(ActiveCell, ActiveCell.Offset(0, 50)).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

 

[AbsoluteKevster]

Morning and Welcome to the PCReview Forum!

Please could you provide the full details of the Runtime error.

 

[arthur.dobeson]

Please find attached the spreadsheet that contains the macro. Thanks

 

[Stoneboysteve]

I usually use ActiveCell.Value = "value" to make the assignment.

I could not test the macro, I tried the button and got an invalid reference to the csv file. Can you paste the code and show where the debug error, and identify the subroutine or macro you are calling?

 

[arthur.dobeson]

Thanks for responding. The complete macro is:

Sub Converttocsv()

'Saves Active Book
ActiveWorkbook.Save
'Saves Active Book as a csv
ActiveWorkbook.SaveAs Filename:="C:\temp\stkupload2012.csv", FileFormat:=xlCSV, _
CreateBackup:=False

'sorts by cc then exp then div
Range("A3:T547").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Sort Key1:=Range("F4"), Order1:=xlAscending, Key2:=Range("E4") _
, Order2:=xlAscending, Key3:=Range("B4"), Order3:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
xlSortNormal
Range("a1").Select


'inserts the word end below the last data
Range("a4").Select
Selection.End(x2Down).Select
ActiveCell.Offset(1, 20).Select
ActiveCell.FormulaR1C1 = "end"
Selection.Copy
Range(ActiveCell, ActiveCell.Offset(0, 50)).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False


'Converts formulas to values
Range("A:t").Select
Selection.NumberFormat = "General"
Call Value_conversion

'Converts blanks to zero
Range("h4").Select
Call blanktozero
Range("i4").Select
Call blanktozero
Range("j4").Select
Call blanktozero
Range("k4").Select
Call blanktozero
Range("l4").Select
Call blanktozero
Range("m4").Select
Call blanktozero
Range("n4").Select
Call blanktozero
Range("o4").Select
Call blanktozero
Range("p4").Select
Call blanktozero
Range("q4").Select
Call blanktozero
Range("r4").Select
Call blanktozero
Range("s4").Select
Call blanktozero

' Round to two decimal places
Range("h4").Select
Call rounding
Range("i4").Select
Call rounding
Range("j4").Select
Call rounding
Range("k4").Select
Call rounding
Range("l4").Select
Call rounding
Range("m4").Select
Call rounding
Range("n4").Select
Call rounding
Range("o4").Select
Call rounding
Range("p4").Select
Call rounding
Range("q4").Select
Call rounding
Range("r4").Select
Call rounding
Range("s4").Select
Call rounding

' eliminates duplicate cost codes by division
Range("f5").Select
Call sumsame
'deletes the headings
Call ClrHeadings
'clears the rows below the last line
Range("a4").Select
Selection.End(xlDown).Select
Range(ActiveCell.Offset(1, 0), ActiveCell.Offset(11, 50)).Select
Selection.ClearContents

Range("a1").Select
End Sub
Sub sumsame()
'
' Macro1 Macro
' Macro recorded 13/10/2005 by dmcgowan
'
Do Until IsEmpty(ActiveCell) = True
If ActiveCell = ActiveCell.Offset(-1, 0) Then
If ActiveCell.Offset(0, -1) = ActiveCell.Offset(-1, -1) Then
If ActiveCell.Offset(0, -4) = ActiveCell.Offset(-1, -4) Then
Range(ActiveCell.Offset(0, 2), ActiveCell.Offset(0, 15)).Select
Selection.Copy
Range(ActiveCell.Offset(-1, 0), ActiveCell.Offset(-1, 13)).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlAdd, SkipBlanks _
:=False, Transpose:=False
ActiveCell.Offset(1, -2).Select
Selection.EntireRow.Delete
Else: ActiveCell.Offset(1, 0).Select
End If
Else: ActiveCell.Offset(1, 0).Select
End If
Else: ActiveCell.Offset(1, 0).Select
End If
Loop
End Sub
Sub blanktozero()
'
' Macro1 Macro
' Macro recorded 13/10/2005 by dmcgowan
'
Do Until ActiveCell = "end"
If IsEmpty(ActiveCell) = True Then
ActiveCell.FormulaR1C1 = 0
ActiveCell.Offset(1, 0).Select
Else: ActiveCell.Offset(1, 0).Select
End If
Loop
Selection.End(xlUp).Select
ActiveCell.Offset(0, 1).Select
End Sub
Sub rounding()
Do Until ActiveCell = "end"
ActiveCell.Offset(0, 43).Activate
ActiveCell.FormulaR1C1 = "=ROUND(RC[-43],2)"
ActiveCell.Copy
ActiveCell.Offset(0, -43).Activate
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
ActiveCell.Offset(1, 0).Activate
Loop
End Sub
Sub ClrHeadings()
'
' ClrHeadings Macro
' Macro recorded 25/11/2003 by djmayg
'Deletes Headings
Range("1:3").Select
Selection.EntireRow.Delete

End Sub
Sub Value_conversion()
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
End Sub