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

[pedro-egoli]

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

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]

This code has been working as required

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

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.

 

[pedro-egoli]

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.