Code error ??
 

Hi
I have got this code which selects the contents of "MON" worksheet and
pastes it into the next available row in "LOG" worksheet.

The problem I have is that in the "MON" worksheet, Column A is populated by
a formula and when I select the macro to paste the data into the "LOG"
worksheet a '0' value is shown for column A .

How can I get the macro to select the cells and paste the data without
pasting any formula - just the results ???
Hope I haven't confused anybody - and thanks for ur help

The code:


Sub test()
'
Range("A8:N34").Select
Selection.Copy
Sheets("Log").Select
Range("A4").Select
ActiveSheet.Paste
Range("E11").Select
Application.Run "'Lost Prop.xls'!Clear"
Sheets("Mon").Select
Range("B14").Select
End Sub

Anthony

one way:

Public Sub test()
With Sheets("MON").Range("A8:N34")
Sheets("Log").Range("A" & Rows.Count).End(xlUp).Offset( _
1, 0).Resize(.Rows.Count, .Columns.Count).Value = .Value
.ClearContents
End With
End Sub

I assumed that "Lost Prop.xls'!Clear" clears the original copy range.

Note that you almost never need to select a range in order to work with
it. Using the range objects directly makes your code smaller, faster,
and, IMO, easier to maintain.

In article ,
"Anthony" wrote:

Hi
I have got this code which selects the contents of "MON" worksheet and
pastes it into the next available row in "LOG" worksheet.

The problem I have is that in the "MON" worksheet, Column A is populated by
a formula and when I select the macro to paste the data into the "LOG"
worksheet a '0' value is shown for column A .

How can I get the macro to select the cells and paste the data without
pasting any formula - just the results ???
Hope I haven't confused anybody - and thanks for ur help

The code:


Sub test()
'
Range("A8:N34").Select
Selection.Copy
Sheets("Log").Select
Range("A4").Select
ActiveSheet.Paste
Range("E11").Select
Application.Run "'Lost Prop.xls'!Clear"
Sheets("Mon").Select
Range("B14").Select
End Sub

Anthony

Thanks JE McGimpsey, ur reply worked.
regards
Anthony

"JE McGimpsey" wrote:

one way:

Public Sub test()
With Sheets("MON").Range("A8:N34")
Sheets("Log").Range("A" & Rows.Count).End(xlUp).Offset( _
1, 0).Resize(.Rows.Count, .Columns.Count).Value = .Value
.ClearContents
End With
End Sub

I assumed that "Lost Prop.xls'!Clear" clears the original copy range.

Note that you almost never need to select a range in order to work with
it. Using the range objects directly makes your code smaller, faster,
and, IMO, easier to maintain.

In article ,
"Anthony" wrote:

Hi
I have got this code which selects the contents of "MON" worksheet and
pastes it into the next available row in "LOG" worksheet.

The problem I have is that in the "MON" worksheet, Column A is populated by
a formula and when I select the macro to paste the data into the "LOG"
worksheet a '0' value is shown for column A .

How can I get the macro to select the cells and paste the data without
pasting any formula - just the results ???
Hope I haven't confused anybody - and thanks for ur help

The code:


Sub test()
'
Range("A8:N34").Select
Selection.Copy
Sheets("Log").Select
Range("A4").Select
ActiveSheet.Paste
Range("E11").Select
Application.Run "'Lost Prop.xls'!Clear"
Sheets("Mon").Select
Range("B14").Select
End Sub

Anthony