+ Reply to Thread
Results 1 to 3 of 3

Code error ??

  1. #1
    Anthony
    Guest

    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

  2. #2
    JE McGimpsey
    Guest

    Re: Code error ??

    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 <[email protected]>,
    "Anthony" <[email protected]> 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


  3. #3
    Anthony
    Guest

    Re: Code error ??

    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 <[email protected]>,
    > "Anthony" <[email protected]> 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

    >


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1