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

 

[Fluff]

How about

Sheets("Centrelink Assessment").Cells(4, Columns.Count).End(xlToLeft).Offset(, 1).PasteSpecial xlPasteValues

 

[jasonb75]

Two observations.
You haven't qualified the sheet name with a workbook identity so the code could be looking for the sheet in the wrong workbook.
If it is looking at the correct workbook and the range G4:G7 is empty then that line could be falling off of the end of the sheet.

 

[pedro-egoli]

Thanks Flugg, that worked perfectly.
As a novice could you tell me why this statement works in one worksheet and not this one.
As I mentioned in my earlier post I had assumed that just changing the range cells and sheet name for destination that it would work .
Code that works is

Sub Copy()

Sheets("accountbalance").Range("d2:d3").Copy
Sheets("Daily balances").Range("BN5:BN6").End(xlToRight).Offset(, 1).PasteSpecial xlPasteValues
    Application.CutCopyMode = False
        Application.CutCopyMode = False
    Selection.NumberFormat = "$#,##0.00"
    Selection.NumberFormat = "$#,##0.0"
    Selection.NumberFormat = "$#,##0"
End Sub

jasonb75, looks like your post and Fluffs' may have crossed so thanks for your input.
Now to the second part of my query which was

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 assistance here again greatly appreciated.

 

[Fluff]

You can copy/paste F14:F15 in the same way you have for D4:D7.

As a novice could you tell me why this statement works in one worksheet and not this one.

See Jason's post.

 

[pedro-egoli]

Thanks again and I can see what Jason was getting at,
Off to bed now and will work on the copy/paste of F13/15 tomorrow.
In the meantime do I enter the code before
Application.CutCopyMode = False
or after this
And looking at it is there need for that line to be repeated?
Thanks

 

[Fluff]

Enter the code before that line & it doesn't need to be repeated.

 

[pedro-egoli]

Thanks again Fluff,
My attempt at code is as follows

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(6, 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

The original part still works however, my attempt gets figures across but in the wrong column/cells as shown below

Account balances for centrelink Final Version-1.xlsm
	F	G	H	I
4	316	$1,660	$1,718	$34,100
5	1720	$4,634	$4,634	$53,453
6	344	$43	$43
7	147	$1,859	$1,859
8
9	AGL	$33,000
10	CBA	$52,807
CENTRELINK ASSESSMENT

Figures in I4:I5 should be in cells H9:H10
Not fully understanding what the steps were in your original amendment I am at a loss as to how to rectify.

What do I need to change?

 

[pedro-egoli]

Patience has paid off (I think!!)
Worked out that the number 6 was creating the problem and that the 4 which worked on original solution was in fact 4 rows down.
Changed the second number from 6 to 9 and hey presto it worked.
Thanks heaps

 

[Fluff]

Glad you sorted it & thanks for the feedback.