I have just installed Excel 2007 and wish to copy values from one worksheet
(data downloaded from online banking in csv format) to the first blank row in
another. I am manually copying a range of cells in the first worksheet and
then, in the second worksheet, running a macro which includes the code:

Application.ScreenUpdating = False
rownum = 4
Cells(rownum, 1).Select
While Selection.Value < ""
rownum = rownum + 1
Cells(rownum, 1).Select
Wend
ActiveSheet.Paste
.. . . . and then various formatting functions

When I try to run the macro from a UserForm or by selecting it in the list
of macros dialog box, it fails with the message

"Run Time Error 1004 - Paste method of Worksheet class failed"

The macro runs OK from a keyboard shortcut.

I get the same problem if I test it by using Record Macro to create a paste
action and then run the recorded macro.

(Running Vista Home Premium)

Can anyone help please?

I'm assuming, since you're turning off screen updating, that you're
not going for a cool visual effect. Therefore, I'd recommend
something like this instead. Let's say I'm copying cell A1 of Sheet2
to active sheet; the following will paste it into first available row
of column 1 on the active sheet:

With ActiveSheet
Sheet2.Cells(1, 1).Copy _
.Cells(Application.WorksheetFunction.CountA(.Range ("$A:$A")) + 1,
1)
End With

On Nov 28, 10:54 am, Steve wrote:
I have just installed Excel 2007 and wish to copy values from one worksheet
(data downloaded from online banking in csv format) to the first blank row in
another. I am manually copying a range of cells in the first worksheet and
then, in the second worksheet, running a macro which includes the code:

Application.ScreenUpdating = False
rownum = 4
Cells(rownum, 1).Select
While Selection.Value < ""
rownum = rownum + 1
Cells(rownum, 1).Select
Wend
ActiveSheet.Paste
. . . . and then various formatting functions

When I try to run the macro from a UserForm or by selecting it in the list
of macros dialog box, it fails with the message

"Run Time Error 1004 - Paste method of Worksheet class failed"

The macro runs OK from a keyboard shortcut.

I get the same problem if I test it by using Record Macro to create a paste
action and then run the recorded macro.

(Running Vista Home Premium)

Can anyone help please?

Thanks ilia but this is not copying. It is just clearing the contents of the
first cell of the last row of data.

"ilia" wrote:

I'm assuming, since you're turning off screen updating, that you're
not going for a cool visual effect. Therefore, I'd recommend
something like this instead. Let's say I'm copying cell A1 of Sheet2
to active sheet; the following will paste it into first available row
of column 1 on the active sheet:

With ActiveSheet
Sheet2.Cells(1, 1).Copy _
.Cells(Application.WorksheetFunction.CountA(.Range ("$A:$A")) + 1,
1)
End With

On Nov 28, 10:54 am, Steve wrote:
I have just installed Excel 2007 and wish to copy values from one worksheet
(data downloaded from online banking in csv format) to the first blank row in
another. I am manually copying a range of cells in the first worksheet and
then, in the second worksheet, running a macro which includes the code:

Application.ScreenUpdating = False
rownum = 4
Cells(rownum, 1).Select
While Selection.Value < ""
rownum = rownum + 1
Cells(rownum, 1).Select
Wend
ActiveSheet.Paste
. . . . and then various formatting functions

When I try to run the macro from a UserForm or by selecting it in the list
of macros dialog box, it fails with the message

"Run Time Error 1004 - Paste method of Worksheet class failed"

The macro runs OK from a keyboard shortcut.

I get the same problem if I test it by using Record Macro to create a paste
action and then run the recorded macro.

(Running Vista Home Premium)

Can anyone help please?

This is a generalized example. Post your whole macro, and I can
rewrite it to use this approach.


On Nov 28, 1:30 pm, Steve wrote:
Thanks ilia but this is not copying. It is just clearing the contents of the
first cell of the last row of data.



"ilia" wrote:
I'm assuming, since you're turning off screen updating, that you're
not going for a cool visual effect. Therefore, I'd recommend
something like this instead. Let's say I'm copying cell A1 of Sheet2
to active sheet; the following will paste it into first available row
of column 1 on the active sheet:

With ActiveSheet
Sheet2.Cells(1, 1).Copy _
.Cells(Application.WorksheetFunction.CountA(.Range ("$A:$A")) + 1,
1)
End With

On Nov 28, 10:54 am, Steve wrote:
I have just installed Excel 2007 and wish to copy values from one worksheet
(data downloaded from online banking in csv format) to the first blank row in
another. I am manually copying a range of cells in the first worksheet and
then, in the second worksheet, running a macro which includes the code:

Application.ScreenUpdating = False
rownum = 4
Cells(rownum, 1).Select
While Selection.Value < ""
rownum = rownum + 1
Cells(rownum, 1).Select
Wend
ActiveSheet.Paste
. . . . and then various formatting functions

When I try to run the macro from a UserForm or by selecting it in the list
of macros dialog box, it fails with the message

"Run Time Error 1004 - Paste method of Worksheet class failed"

The macro runs OK from a keyboard shortcut.

I get the same problem if I test it by using Record Macro to create a paste
action and then run the recorded macro.

(Running Vista Home Premium)

Can anyone help please?- Hide quoted text -

- Show quoted text -

The workbook and (single) sheet created by download from online banking
always have the same 8 characters at the beginning of the name but always
different characters after those. The data starts on row 4, columns A:G and
a variable number of rows. I have been copying row 4 to the end manually (but
assume the this could be achieved by suitable code) and then running the
following to add it to and update my file.

Sub UpdateNatWest()
'data in downloaded sheet has been copied manually before running this
macro
Sheets("Nat West").Activate
'find first blank row
rownum = 3
Cells(rownum, 5).Select
While Selection.Value < ""
rownum = rownum + 1
Cells(rownum, 5).Select
Wend
Cells(rownum, 1).Select
'paste in the data which was copied manually before running this macro
ActiveSheet.Paste
Application.CutCopyMode = False
'remove unwanted data
Columns("F:G").Select
Selection.Delete Shift:=xlToLeft
'correct the format of the imported data
Columns("D:E").Select
Selection.NumberFormat = "#,##0.00_ ;[Red]-#,##0.00 "
'format header rows
Rows("1:1").Select
Selection.Font.Size = 22
Rows("2:2").Select
Selection.Font.Size = 11
Rows("1:2").Select
With Selection.Font
.Color = -11489280
.Bold = True
.TintAndShade = 0
End With
'remove unwanted character
Cells.Replace What:="'", Replacement:=""
'correct the alignment of column C
Columns("C:C").Select
Selection.HorizontalAlignment = xlLeft
'goto (blank) cell below latest total for viewing
Cells(rownum, 5).Select
While Selection.Value < ""
rownum = rownum + 1
Cells(rownum, 5).Select
Wend
Cells(rownum, 5).Select
End Sub


"ilia" wrote:

This is a generalized example. Post your whole macro, and I can
rewrite it to use this approach.


On Nov 28, 1:30 pm, Steve wrote:
Thanks ilia but this is not copying. It is just clearing the contents of the
first cell of the last row of data.



"ilia" wrote:
I'm assuming, since you're turning off screen updating, that you're
not going for a cool visual effect. Therefore, I'd recommend
something like this instead. Let's say I'm copying cell A1 of Sheet2
to active sheet; the following will paste it into first available row
of column 1 on the active sheet:

With ActiveSheet
Sheet2.Cells(1, 1).Copy _
.Cells(Application.WorksheetFunction.CountA(.Range ("$A:$A")) + 1,
1)
End With

On Nov 28, 10:54 am, Steve wrote:
I have just installed Excel 2007 and wish to copy values from one worksheet
(data downloaded from online banking in csv format) to the first blank row in
another. I am manually copying a range of cells in the first worksheet and
then, in the second worksheet, running a macro which includes the code:

Application.ScreenUpdating = False
rownum = 4
Cells(rownum, 1).Select
While Selection.Value < ""
rownum = rownum + 1
Cells(rownum, 1).Select
Wend
ActiveSheet.Paste
. . . . and then various formatting functions

When I try to run the macro from a UserForm or by selecting it in the list
of macros dialog box, it fails with the message

"Run Time Error 1004 - Paste method of Worksheet class failed"

The macro runs OK from a keyboard shortcut.

I get the same problem if I test it by using Record Macro to create a paste
action and then run the recorded macro.

(Running Vista Home Premium)

Can anyone help please?- Hide quoted text -

- Show quoted text -

Try this:

Sub UpdateNatWest()
'data in downloaded sheet has been copied
'manually before running this macro
Dim wshNatWest As Excel.Worksheet
Dim rngFirstBlank As Excel.Range

Set wshNatWest = _
ThisWorkbook.Worksheets("Nat West")

With wshNatWest
'find first blank row
Set rngFirstBlank = _
.Cells(3, 5).End(xlDown).Offset(1, -4)

'paste in the data which was copied
'manually before running this macro
.Paste Destination:=rngFirstBlank
Application.CutCopyMode = False

'remove unwanted data
.Columns("F:G").Delete Shift:=xlToLeft

'correct the format of the imported data
.Columns("D:E").NumberFormat = _
"#,##0.00_ ;[Red]-#,##0.00 "

'format header rows
.Rows("1:1").Font.Size = 22
.Rows("2:2").Font.Size = 11

With .Rows("1:2").Font
.Color = -11489280
.Bold = True
.TintAndShade = 0
End With

'remove unwanted character
.Cells.Replace What:="'", Replacement:=""

'correct the alignment of column C
.Columns("C:C").HorizontalAlignment = xlLeft

'goto (blank) cell below latest total for viewing
.Activate
With .UsedRange
.Cells(.Rows.Count, 5).Offset(1, 0).Select
End With
End With
End Sub



On Nov 29, 8:50 am, Steve wrote:
The workbook and (single) sheet created by download from online banking
always have the same 8 characters at the beginning of the name but always
different characters after those. The data starts on row 4, columns A:G and
a variable number of rows. I have been copying row 4 to the end manually (but
assume the this could be achieved by suitable code) and then running the
following to add it to and update my file.

Sub UpdateNatWest()
'data in downloaded sheet has been copied manually before running this
macro
Sheets("Nat West").Activate
'find first blank row
rownum = 3
Cells(rownum, 5).Select
While Selection.Value < ""
rownum = rownum + 1
Cells(rownum, 5).Select
Wend
Cells(rownum, 1).Select
'paste in the data which was copied manually before running this macro
ActiveSheet.Paste
Application.CutCopyMode = False
'remove unwanted data
Columns("F:G").Select
Selection.Delete Shift:=xlToLeft
'correct the format of the imported data
Columns("D:E").Select
Selection.NumberFormat = "#,##0.00_ ;[Red]-#,##0.00 "
'format header rows
Rows("1:1").Select
Selection.Font.Size = 22
Rows("2:2").Select
Selection.Font.Size = 11
Rows("1:2").Select
With Selection.Font
.Color = -11489280
.Bold = True
.TintAndShade = 0
End With
'remove unwanted character
Cells.Replace What:="'", Replacement:=""
'correct the alignment of column C
Columns("C:C").Select
Selection.HorizontalAlignment = xlLeft
'goto (blank) cell below latest total for viewing
Cells(rownum, 5).Select
While Selection.Value < ""
rownum = rownum + 1
Cells(rownum, 5).Select
Wend
Cells(rownum, 5).Select
End Sub



"ilia" wrote:
This is a generalized example. Post your whole macro, and I can
rewrite it to use this approach.

On Nov 28, 1:30 pm, Steve wrote:
Thanks ilia but this is not copying. It is just clearing the contents of the
first cell of the last row of data.

"ilia" wrote:
I'm assuming, since you're turning off screen updating, that you're
not going for a cool visual effect. Therefore, I'd recommend
something like this instead. Let's say I'm copying cell A1 of Sheet2
to active sheet; the following will paste it into first available row
of column 1 on the active sheet:

With ActiveSheet
Sheet2.Cells(1, 1).Copy _
.Cells(Application.WorksheetFunction.CountA(.Range ("$A:$A")) + 1,
1)
End With

On Nov 28, 10:54 am, Steve wrote:
I have just installed Excel 2007 and wish to copy values from one worksheet
(data downloaded from online banking in csv format) to the first blank row in
another. I am manually copying a range of cells in the first worksheet and
then, in the second worksheet, running a macro which includes the code:

Application.ScreenUpdating = False
rownum = 4
Cells(rownum, 1).Select
While Selection.Value < ""
rownum = rownum + 1
Cells(rownum, 1).Select
Wend
ActiveSheet.Paste
. . . . and then various formatting functions

When I try to run the macro from a UserForm or by selecting it in the list
of macros dialog box, it fails with the message

"Run Time Error 1004 - Paste method of Worksheet class failed"

The macro runs OK from a keyboard shortcut.

I get the same problem if I test it by using Record Macro to create a paste
action and then run the recorded macro.

(Running Vista Home Premium)

Can anyone help please?- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

Thanks for your help. Its easy when you know how!

"Steve" wrote:

I have just installed Excel 2007 and wish to copy values from one worksheet
(data downloaded from online banking in csv format) to the first blank row in
another. I am manually copying a range of cells in the first worksheet and
then, in the second worksheet, running a macro which includes the code:

Application.ScreenUpdating = False
rownum = 4
Cells(rownum, 1).Select
While Selection.Value < ""
rownum = rownum + 1
Cells(rownum, 1).Select
Wend
ActiveSheet.Paste
. . . . and then various formatting functions

When I try to run the macro from a UserForm or by selecting it in the list
of macros dialog box, it fails with the message

"Run Time Error 1004 - Paste method of Worksheet class failed"

The macro runs OK from a keyboard shortcut.

I get the same problem if I test it by using Record Macro to create a paste
action and then run the recorded macro.

(Running Vista Home Premium)

Can anyone help please?