Loop through data to copy to other sheets.

[Anne Troy]

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 ]

[lantiger]

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

[Anne Troy]

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 ]

[lantiger]

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

lantiger

[Anne Troy]

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!!

[Jeanie]

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!

[Jay Petrulis]

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

[Anne Troy]

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.

[Cosmos75]

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?

[Jay Petrulis]

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