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
 

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
51,062
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
Sheets("Centrelink Assessment").Cells(4, Columns.Count).End(xlToLeft).Offset(, 1).PasteSpecial xlPasteValues
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,628
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Apr 25, 2004
Messages
1,217
Office Version
  1. 2016
Platform
  1. Windows
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
VBA Code:
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
Rich (BB code):
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
51,062
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Apr 25, 2004
Messages
1,217
Office Version
  1. 2016
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
51,062
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

pedro-egoli

Well-known Member
Joined
Apr 25, 2004
Messages
1,217
Office Version
  1. 2016
Platform
  1. Windows
Thanks again Fluff,
My attempt at code is as follows
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(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
FGHI
4316$1,660$1,718$34,100
51720$4,634$4,634$53,453
6344$43$43
7147$1,859$1,859
8
9AGL$33,000
10CBA$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

Well-known Member
Joined
Apr 25, 2004
Messages
1,217
Office Version
  1. 2016
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
51,062
Office Version
  1. 365
Platform
  1. Windows
Glad you sorted it & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,119,098
Messages
5,576,123
Members
412,697
Latest member
ahem27
Top