Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: copying to a new worksheet

  1. #1
    New Member
    Join Date
    Apr 2002
    Location
    Sheffield, England
    Posts
    49
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I create a new worksheet at the beginning of every week and would like to be able to copy the final-total cell from last weeks worksheet into the new worksheet

  2. #2
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Sounds like a cake walk. You can copy or paste or use vba. Depends on the cell location of the total and the cell location of the deposit.

  3. #3
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Sample code (in the event you'd like vba):

    Code:
    Sub Copy()
    Sheet1.[a1].Copy Sheet2.[a5]
    End Sub
    _________________
    Cheers, NateO

    [ This Message was edited by: NateO on 2002-04-26 08:49 ]

  4. #4
    MrExcel MVP Joe Was's Avatar
    Join Date
    Feb 2002
    Location
    Central Florida, USA
    Posts
    7,539
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    Try this:

    Sub DataList()
    'To use build a data list on Sheet1.
    'Put a lable in A1 on Sheet2.
    'Then select data on Sheet1 & run this code.

    'Copy the current selection on Sheet1 &
    'amend paste selection to bottom of data
    'list on Sheet2.

    'Copy the current selection on Sheet1.
    Sheets("Sheet1").Select
    Selection.Copy

    'Go to sheet2, find the next empty cell & paste.
    Sheets("Sheet2").Select
    ActiveSheet.Range("A65536").End(xlUp).Offset(1, 0).Select
    ActiveSheet.Paste

    'Go back to the original sheet.
    Sheets("Sheet1").Select
    Range("A1").Select
    End Sub

    Hope this helps. JSW

  5. #5
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    One could streamline JWS's a little, runs faster without selecting and extends the life of your keyboard:

    Code:
    Sub DataList2()
    Selection.Copy Sheet2.[a65536].End(xlUp).Offset(1, 0)
    End Sub
    Or, if you can create methodology to consistently identify your target, you substitute [a1] with that methodology:

    Code:
    Sub DataList3()
    Sheet1.[a1].Copy Sheet2.[a65536].End(xlUp).Offset(1, 0)
    End Sub
    No harm intended here...
    _________________
    Cheers, NateO


    [ This Message was edited by: NateO on 2002-04-26 09:16 ]

  6. #6
    MrExcel MVP Joe Was's Avatar
    Join Date
    Feb 2002
    Location
    Central Florida, USA
    Posts
    7,539
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    NateO,

    No harm taken.

    I know the selects are not needed, I just like to keep the code self-explaining for both new users and complex code.

    Plus, you and I both know just about any code can be made specific or universal. Does the user want to select the cell to perform the action on or does the user want the same cell or range used each time?

    Thanks, we all should like all the help we can get. JSW

    [ This Message was edited by: Joe Was on 2002-04-26 09:56 ]

  7. #7
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Completely agree with your post JWS. With the [a1] deal, I meant you substitute it with:
    [a65536].End(xlUp).Offset(1, 0)
    or other ways of copying a range, even if it's 'moving' target.

    It's tricky responding to fairly vague posts...Have a good one.


    _________________
    Cheers, NateO

    [ This Message was edited by: NateO on 2002-04-26 10:07 ]

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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