Run time error 1004 on existing code and add another range to code

pedro-egoli

Well-known Member
Joined
Apr 25, 2004
Messages
1,217
Office Version
  1. 2016
Platform
  1. Windows
Have just tried a macro to transfer and paste special /value from sheet “Accountbalance” to sheet “CENTRELINK ASSESSMENT” in same workbook .
It did not work and I got a Run time error 1004 Application-defined or object-defined error.
I tried to use the basics of code that works in same workbook but on another worksheet. See this thread
Transfer data from one sheet to another
Code used with offending line in Bold is
VBA Code:
Sub Centrelink()
Sheets("accountbalance").Range("d4:d7").Copy
[B]Sheets("Centrelink Assessment").Range("g4:g7").End(xlToRight).Offset(, 1).PasteSpecial xlPasteValues[/B]
Application.CutCopyMode = False
Application.CutCopyMode = False
Selection.NumberFormat = "$#,##0.00"
Selection.NumberFormat = "$#,##0.0"
Selection.NumberFormat = "$#,##0"
End Sub
The statement “Sheets("accountbalance").Range("d4:d7").Copy” is common to both and I don’t know if this creates a problem or not.
That is the first part of what I am trying to achieve as I also want to expand the
Source range in “Accountbalance” to “F14:F15” both of which are named range (AGL,CBA) and want to paste special value into “Centrelink assessment” sheet in cells “G9:G10” .
Once combined my aim is that the paste special will go to the next empty column which initially will be “H” and then “I” etc

Any help appreciated
 

pedro-egoli

Well-known Member
Joined
Apr 25, 2004
Messages
1,217
Office Version
  1. 2016
Platform
  1. Windows
This code has been working as required
VBA Code:
Sub Centrelink()

Sheets("accountbalance").Range("d4:d7").Copy
Sheets("Centrelink Assessment").Cells(4, Columns.Count).End(xlToLeft).Offset(, 1).PasteSpecial xlPasteValues
Sheets("accountbalance").Range("F14:F15").Copy
Sheets("Centrelink Assessment").Cells(9, Columns.Count).End(xlToLeft).Offset(, 1).PasteSpecial xlPasteValues
 Application.CutCopyMode = False
        Application.CutCopyMode = False
    Selection.NumberFormat = "$#,##0.00"
    Selection.NumberFormat = "$#,##0.0"
    Selection.NumberFormat = "$#,##0"
End Sub
Wanted to copy an additional 2 cells in "accountbalance" and paste special/ values into "centrelinkAssessment" row 21 and 22 and came yup with this code
Code:
Sub Centrelink()
Sheets("accountbalance").Range("d2:d3").Copy
Sheets("Centrelink Assessment").Cells(21, Columns.Count).End(xlToLeft).Offset(, 1).PasteSpecial xlPasteValues
Sheets("accountbalance").Range("d4:d7").Copy
Sheets("Centrelink Assessment").Cells(4, Columns.Count).End(xlToLeft).Offset(, 1).PasteSpecial xlPasteValues
Sheets("accountbalance").Range("F14:F15").Copy
Sheets("Centrelink Assessment").Cells(9, Columns.Count).End(xlToLeft).Offset(, 1).PasteSpecial xlPasteValues
 Application.CutCopyMode = False
        Application.CutCopyMode = False
    Selection.NumberFormat = "$#,##0.00"
    Selection.NumberFormat = "$#,##0.0"
    Selection.NumberFormat = "$#,##0"
End Sub
Everything works except that the data meant to go into cells in row 21 and 22 of "CentrelinkAssessment" do not show.
Cannot see why and any help appreciated.
 

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

pedro-egoli

Well-known Member
Joined
Apr 25, 2004
Messages
1,217
Office Version
  1. 2016
Platform
  1. Windows
Solved.
I had old formula in cells after current column and the figures were in fact after the last formula cell.
Deleted old formula across sheet and that fixed it.
 

Watch MrExcel Video

Forum statistics

Threads
1,119,060
Messages
5,575,869
Members
412,689
Latest member
nhsmedic
Top