Running macros in Excel 2007
 

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?

Running macros in Excel 2007
 

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?

Running macros in Excel 2007
 

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?

Running macros in Excel 2007
 

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 -

Running macros in Excel 2007
 

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 -

Running macros in Excel 2007
 

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 -

Running macros in Excel 2007
 

You may want to add this at the very top:

If (Application.CutCopyMode = False) Then
Call MsgBox("No selection!")
Exit Sub
End If


On Nov 29, 2:56 pm, ilia wrote:
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

Running macros in Excel 2007
 

Thank you. That works fine but only when I assign a shortcut key to it.
I want to run the macro from a UserForm but, having manually copied the
required data from another sheet and then running this macro, I find that
CutCopyMode becomes False before the macro runs. This happens as soon as I
click the Macro button on the Developer tab or when I try to run it from a
UserForm. (Why does it do that?)
I wonder if the macro could start by finding the source sheet and then
copying the data to be transferred. My (amateurish) code to do this would be
as follows but I dont know how to activate a sheet where only part of the
sheet name will be constant. The first 8 characters only are always the same.
Worksheets("????").Activate
'the data starts on row 4 for an unknown number of rows
rownum = 4
colnum = 5
Cells(rownum, colnum).Select
While ActiveCell.Value < ""
rownum = rownum + 1
Cells(rownum, colnum).Select
Wend
lastrownum = Str(rownum - 1)
endofrange = "E" + Mid(lastrownum, 2)
Range("A4", endofrange).Select
Selection.Copy


"ilia" wrote:

You may want to add this at the very top:

If (Application.CutCopyMode = False) Then
Call MsgBox("No selection!")
Exit Sub
End If


On Nov 29, 2:56 pm, ilia wrote:
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

Running macros in Excel 2007
 

If you only have one workbook that begins with those 8 constant
characters, you can use the following code (assuming abcdefgh are the
first 8 characters):

Dim findSource as Excel.Worksheet
Const first8 as String = "abcdefgh"

For Each findSource in ThisWorkbook
if (LCase(Left$(findSource.Name)) = first8 then Exit For
Next findSource

' Then do something like, later on in the code where you normally
paste:

If findSource is Nothing Then
Call MsgBox("Data source not found!")
Exit Sub
End If

With findSource
.Range(.Range("A4"), _
.Cells(.UsedRange.Rows.Count, _
.UsedRange.Columns.Count)).Copy _
Destination:=rngFirstBlank
End With

Also, you might be able to establish a pattern to what the rest of the
worksheet name is, but if that's not possible, and you have multiple
worksheets with those first 8 letters, then you either have to add the
import steps to your code so that the workbook knows what the correct
sheet is.

If all else fails, you can add a control on the user form that allows
user to select a range, first. Then, modify the code as follows, and
run it from a command button click event, on the form.

Sub UpdateNatWest()

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 selected data
Selection.Copy Destination:=rngFirstBlank

'don't need this anymore
'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 30, 10:06 am, Steve wrote:
Thank you. That works fine but only when I assign a shortcut key to it.
I want to run the macro from a UserForm but, having manually copied the
required data from another sheet and then running this macro, I find that
CutCopyMode becomes False before the macro runs. This happens as soon as I
click the Macro button on the Developer tab or when I try to run it from a
UserForm. (Why does it do that?)
I wonder if the macro could start by finding the source sheet and then
copying the data to be transferred. My (amateurish) code to do this would be
as follows but I don't know how to activate a sheet where only part of the
sheet name will be constant. The first 8 characters only are always the same.
Worksheets("????").Activate
'the data starts on row 4 for an unknown number of rows
rownum = 4
colnum = 5
Cells(rownum, colnum).Select
While ActiveCell.Value < ""
rownum = rownum + 1
Cells(rownum, colnum).Select
Wend
lastrownum = Str(rownum - 1)
endofrange = "E" + Mid(lastrownum, 2)
Range("A4", endofrange).Select
Selection.Copy



"ilia" wrote:
You may want to add this at the very top:

If (Application.CutCopyMode = False) Then
Call MsgBox("No selection!")
Exit Sub
End If

On Nov 29, 2:56 pm, ilia wrote:
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- Hide quoted text -

- Show quoted text -

Running macros in Excel 2007
 

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?