Loop through data to copy to other sheets.

Anne Troy

MrExcel MVP
Joined
Feb 18, 2002
Messages
2,632
Office Version
  1. 365
Platform
  1. Windows
Hello, everybody. :wink:

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
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
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
 
Upvote 0
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
 
Upvote 0
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!!
 
Upvote 0
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!
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,025
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top