Loop through data to copy to other sheets.
Manage your personal finances in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: Loop through data to copy to other sheets.

  1. #1
    MrExcel MVP Anne Troy's Avatar
    Join Date
    Feb 2002
    Location
    Westwood NJ
    Posts
    2,581
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    Hello, everybody.

    Input Workseet:

    Col A: Date
    Col B through M: Headings are employee names, data is how many hours of vacation per DATE.

    User will enter a date in column A, and then the corresponding number of vacation hours a person took that day. There are a dozen or so employees, so we're only entering a record on the dates that someone has taken vacation time. Dates are mm/dd/yyyy format. The hours are number/two decimals.

    What I would like:

    An update command button (hey, I can actually do that part!) that has an on-click that:

    Loops through each column B through M, and copies the information to the employee's individual sheet.

    The individual sheets:

    Columns are:
    A = Date of vacation
    B through M are months Jan through Dec.

    Data starts *paste* in cell A12, where the date of the first vacation day they take should appear. If it was a half-day in February, .5 (or .50) will appear in cell A14.

    If it's not clear, I'm happy to send the file! If you put your email here, I'll send it right away. If you email me at home, it'll be a few hours before I can send.

    No rush on my part.

    Really appreciate it!! I'm not a coder. I know small bits and pieces. When you start talking about Dim, I think of chinese food.

    _________________
    TheWordExpert

    [ This Message was edited by: Dreamboat on 2002-04-11 10:20 ]

  2. #2
    New Member
    Join Date
    Apr 2002
    Posts
    24
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hey Dreamboat:
    It does seems somewhat unclear about where you want to "paste" the data to, if nobody give you a good answer, you can send me your code to my email (see below), I will take a look at it this weekend.

    lantiger
    lantiger@hotmail.com

  3. #3
    MrExcel MVP Anne Troy's Avatar
    Join Date
    Feb 2002
    Location
    Westwood NJ
    Posts
    2,581
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Sorry. I repeat.

    I'm not a coder.

    I have no code.

    _________________
    TheWordExpert

    [ This Message was edited by: Dreamboat on 2002-04-11 10:34 ]

  4. #4
    New Member
    Join Date
    Apr 2002
    Posts
    24
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    sorry on my part for not refer it correctly. I meant file.

    lantiger

  5. #5
    MrExcel MVP Anne Troy's Avatar
    Join Date
    Feb 2002
    Location
    Westwood NJ
    Posts
    2,581
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Oh! Okay! Thanks!!

    Paste to =

    One Data input sheet for user to input vacation days earned, used, etc.

    Each Employee has a worksheet. I would like to paste the date and how many hours vacation they took that day from the data input sheet and copy each employee-specific date/hours to the employee's specific sheet.

    Sending file!!
    ~Anne Troy

  6. #6
    New Member
    Join Date
    Apr 2002
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Did you find the answer to your question because I am having a nightmare with something similar! I need a way to transfer data I entered on one page to another, but on the next row. If you did get an answer, could you reply to this one or see my message on page one. thanks!

  7. #7
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-14 09:28, Jeanie wrote:
    Did you find the answer to your question because I am having a nightmare with something similar! I need a way to transfer data I entered on one page to another, but on the next row. If you did get an answer, could you reply to this one or see my message on page one. thanks!
    Hi Jeanie,

    I sent a file to Dreamboat to do what you wish, but it is at work. I think others were working on the request for her as well, but I am unsure of their results.

    Please describe in more detail how your data is set up. If Data Validation (see other thread) is not enough for you, let us know what sheet your data set is on and in what order; what sheet(s) you want to populate and how; and what criteria is required for the search.

    For a simple transfer, here are two options using code. The first copies the data from Sheet1 to Sheet2 (the entire row), including formulae. The second only transfers the values.

    ---begin VBA---
    Sub test()
    Dim lastrow As Long, x As Long

    lastrow = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row

    For x = 1 To lastrow
    Sheets("Sheet1").Rows(x).Copy Sheets("Sheet2").Rows(x + 1)
    Next x

    End Sub
    ''''''''''''''''''''''''''''''''
    Sub test2()
    Dim lastrow As Long, x As Long


    lastrow = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
    For x = 1 To lastrow
    Sheets("Sheet1").Rows(x).Copy
    Sheets("Sheet2").Rows(x + 1).PasteSpecial (xlValues)
    Next x

    End Sub
    ---end VBA---

    Bye,
    Jay

  8. #8
    MrExcel MVP Anne Troy's Avatar
    Join Date
    Feb 2002
    Location
    Westwood NJ
    Posts
    2,581
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Jay:

    I apologize for not getting back to you sooner; it couldn't be helped!

    THANK YOU SOOOO MUCH.

    Your work is EXCELLENT. I sure hope you do this for money! It's a beautiful thing.
    ~Anne Troy

  9. #9
    Board Regular
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    357
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I have a Worksheet with names (Column A) and info/values to the right (Columns B to E).

    Each names appears more than once.

    Is there a macro that can copy each persons’ info to a auto-generated sheet named after the person?

  10. #10
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    On 2002-04-16 11:05, Cosmos75 wrote:
    I have a Worksheet with names (Column A) and info/values to the right (Columns B to E).

    Each names appears more than once.

    Is there a macro that can copy each persons’ info to a auto-generated sheet named after the person?
    Hi,

    Short answer:

    1. Get a list of unique names and add a sheet, then name it the person's name

    a) advanced filter and cycle through the list

    b) loop through the list of names and determine whether the sheet exists or not. If it does, go on, else add the sheet.

    2. Cycle through the data list and add each item to the appropriate sheet. You will match the name in the cell and transfer the data to the relevant sheet.

    These two can/should be separate routines as you will only have to add sheets initially and/or when new people are added.

    Here is a function (adapted from John W. Power Programming book) which can be called to determine if a sheet exists or not.

    --------------------
    Public Function SheetExists(sheetname) As Boolean
    Dim abc As Object
    On Error Resume Next
    Set abc = ActiveWorkbook.Sheets(sheetname)
    If Err = 0 Then SheetExists = True _
    Else SheetExists = False
    End Function
    --------------------

    This should get you started.

    HTH,
    Jay

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
  •  

 

 
DMCA.com