Good morning
I have a tall order and your help would be much appreciated.

I always have problems with Ranges properties etc but have got by from
finding solutions from the many excellent replies here particularly
when it comes to VBA and Macro's. However I now seem to have a problem
in finding a suitable solution butif you don't ask ...

I have a workbook with several sheets with named employees.
At the end of each month I get sent a workbook with details of time
spent by each employee on different tasks. Their names appear on the
top cell with all their expense details listed below. There could be
several employees details  on the one sheet but there are several
sheets with employees from different departments.
What I would like to do is run a macro that will take the name from
the tab in my workbook and examine the top cell in the employees
workbook till it finds a match. It then copies the whole column back
to my workbook under that employees named tab not overwriting any
previous columns the idea being that I will at the year end have a
worksheet with each of the 12 months data. Theres a bit more - after
copying I would like to insert todays date formatted to mmm/yy so the
name gets shifted to the second cell in my workbook.

This is asking a lot but I hope there is someone brave out there to
give it a go.


Thanks a lot.

Come on guys - I realise this is difficult but can't you give it a go?
Slight error below - I said 'Their names appear on the
> top cell with all their expense details listed below' It should have read 'Their names appear on the
> top cell with all their time details listed below'.

Thanks for looking tho'




Karen_Thompson90@hotmail.com (Karen) wrote in message news:<7fb3611c.0309161523.64d62f45@posting.google.com>...
> Good morning
> I have a tall order and your help would be much appreciated.
> 
> I always have problems with Ranges properties etc but have got by from
> finding solutions from the many excellent replies here particularly
> when it comes to VBA and Macro's. However I now seem to have a problem
> in finding a suitable solution butif you don't ask ...
> 
> I have a workbook with several sheets with named employees.
> At the end of each month I get sent a workbook with details of time
> spent by each employee on different tasks. Their names appear on the
> top cell with all their expense details listed below. There could be
> several employees details  on the one sheet but there are several
> sheets with employees from different departments.
> What I would like to do is run a macro that will take the name from
> the tab in my workbook and examine the top cell in the employees
> workbook till it finds a match. It then copies the whole column back
> to my workbook under that employees named tab not overwriting any
> previous columns the idea being that I will at the year end have a
> worksheet with each of the 12 months data. Theres a bit more - after
> copying I would like to insert todays date formatted to mmm/yy so the
> name gets shifted to the second cell in my workbook.
> 
> This is asking a lot but I hope there is someone brave out there to
> give it a go.
> 
> 
> Thanks a lot.

You describe the monthly workbook as containing expense details, but say 
you want to copy "the whole column". What does the a sheet in the 
monthly workbook look like? Is it really one column, or multiple columns?

How are the target sheets set up? Do you really want to copy the new 
data into a new column (or columns)?

Have you tried recording any of the steps in this process, to create 
some code that you can tweak?

And finally, what is your ultimate goal in collecting this data? Do you 
want to summarize expenses by employee? By department? By expense type?

Depending on your goal, it may be better to collect all the data on one 
sheet, and include the date, department, and employee name in each row. 
Then, you can create a pivot table to summarize the data, in a variety 
of ways.


Karen wrote:
> Good morning
> I have a tall order and your help would be much appreciated.
> 
> I always have problems with Ranges properties etc but have got by from
> finding solutions from the many excellent replies here particularly
> when it comes to VBA and Macro's. However I now seem to have a problem
> in finding a suitable solution butif you don't ask ...
> 
> I have a workbook with several sheets with named employees.
> At the end of each month I get sent a workbook with details of time
> spent by each employee on different tasks. Their names appear on the
> top cell with all their expense details listed below. There could be
> several employees details  on the one sheet but there are several
> sheets with employees from different departments.
> What I would like to do is run a macro that will take the name from
> the tab in my workbook and examine the top cell in the employees
> workbook till it finds a match. It then copies the whole column back
> to my workbook under that employees named tab not overwriting any
> previous columns the idea being that I will at the year end have a
> worksheet with each of the 12 months data. Theres a bit more - after
> copying I would like to insert todays date formatted to mmm/yy so the
> name gets shifted to the second cell in my workbook.
> 
> This is asking a lot but I hope there is someone brave out there to
> give it a go.
> 
> 
> Thanks a lot.


-- 
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Hi Debra
Thank you so much for replying.
I am sorry if I haven't made myself clear. My goal was to keep a check
on expenses by individual.
Each month I am sent a workbook (Expenses.xls) listing departments on
separate sheets and individuals within those sheets on each column. 
It is a new workbook each time.
Column A contains a fixed list of expenses and columns b onwards has
the employees name at the top with his expenses below.
EG
               B       C        D
              Matt    Brian    Gavin
Hotel         277      300     485
Taxi           26       85
Train/Bus      40       16      25
Etc
The list is more detailed then this but I think you see my point.
I have created a Master workbook that has a tab for each employee and
column A contains the same list of those expenses and column b onwards
each months expenses. Each month I copy and paste each column to my
master workbook relating to that employee. I total the rows into
column 13 and compare the amount with a proportion of what he/she
spent in the previous year. I also found a neat macro that crunches
the rows that return a blank - no expenses under this heading. This
was started off when I was new to excel but since reading the
newsgroups I have become more interested and thought I would try a
macro that might speed things up. So far I only figured out that the
way would be to name the columns asa ranges in order for a macro to
retrive their data. This would tie in with the worksheet tabs in my
mMaster Workbook. I found a macro that does this based on the top cell
(courtesy again of some code posted here previously)
I tried recording the copying/pasting process through the macro
recorder - not very successfull yesterday, so I spent most of today
(saturday!) scouring Google(and David McRitchies Worksheet page where
I copied an example of a loop-through) to see if I could figure that
part of it out. Seems most of the replies posted in Google  relate to
importing "Text" files.
I was just gong to call it a day when I thought I would see if there
was a reply!! Yippee.
Thanks for responding and for reading this. I hope I have made myself
clear.

K












Debra Dalgleish <dsd@contextures.com> wrote in message news:<3F6CA04F.2020704@contextures.com>...
> You describe the monthly workbook as containing expense details, but say 
> you want to copy "the whole column". What does the a sheet in the 
> monthly workbook look like? Is it really one column, or multiple columns?
> 
> How are the target sheets set up? Do you really want to copy the new 
> data into a new column (or columns)?
> 
> Have you tried recording any of the steps in this process, to create 
> some code that you can tweak?
> 
> And finally, what is your ultimate goal in collecting this data? Do you 
> want to summarize expenses by employee? By department? By expense type?
> 
> Depending on your goal, it may be better to collect all the data on one 
> sheet, and include the date, department, and employee name in each row. 
> Then, you can create a pivot table to summarize the data, in a variety 
> of ways.
> 
> 
> Karen wrote:
> > Good morning
> > I have a tall order and your help would be much appreciated.
> > 
> > I always have problems with Ranges properties etc but have got by from
> > finding solutions from the many excellent replies here particularly
> > when it comes to VBA and Macro's. However I now seem to have a problem
> > in finding a suitable solution butif you don't ask ...
> > 
> > I have a workbook with several sheets with named employees.
> > At the end of each month I get sent a workbook with details of time
> > spent by each employee on different tasks. Their names appear on the
> > top cell with all their expense details listed below. There could be
> > several employees details  on the one sheet but there are several
> > sheets with employees from different departments.
> > What I would like to do is run a macro that will take the name from
> > the tab in my workbook and examine the top cell in the employees
> > workbook till it finds a match. It then copies the whole column back
> > to my workbook under that employees named tab not overwriting any
> > previous columns the idea being that I will at the year end have a
> > worksheet with each of the 12 months data. Theres a bit more - after
> > copying I would like to insert todays date formatted to mmm/yy so the
> > name gets shifted to the second cell in my workbook.
> > 
> > This is asking a lot but I hope there is someone brave out there to
> > give it a go.
> > 
> > 
> > Thanks a lot.

The following code may get you started. It will prompt you for a month 
number, and copy data from the monthly expense workbook, to the master 
workbook. It is set to copy 50 rows of data, as you said there was a 
fixed list of expenses:

Sub GetMonthAmts()
Dim i As Integer
Dim wbMaster As Workbook
Dim wbMth As Workbook
Dim ws As Worksheet
Dim ws2 As Worksheet
Dim strEmp As String
Dim col As Integer
Dim LastRow As Integer
LastRow = 50

i = InputBox("Please enter the Month number")
Set wbMaster = Workbooks("EmpMaster.xls")
Set wbMth = Workbooks("EmpMonthExp.xls")

For Each ws In wbMaster.Worksheets
   strEmp = ws.Name
   For Each ws2 In wbMth.Worksheets
     If IsError(Application.Match(strEmp, _
       ws2.Range("B1:Z1"), 0)) Then
       'do nothing
     Else
       col = Application.Match(strEmp, _
         ws2.Range("B1:Z1"), 0)
       ws2.Range(ws2.Cells(2, col + 1), _
         ws2.Cells(LastRow, col + 1)).Copy _
         Destination:=ws.Cells(2, i + 1)
       Exit For
     End If
   Next
Next

End Sub



Karen wrote:
> Hi Debra
> Thank you so much for replying.
> I am sorry if I haven't made myself clear. My goal was to keep a check
> on expenses by individual.
> Each month I am sent a workbook (Expenses.xls) listing departments on
> separate sheets and individuals within those sheets on each column. 
> It is a new workbook each time.
> Column A contains a fixed list of expenses and columns b onwards has
> the employees name at the top with his expenses below.
> EG
>                B       C        D
>               Matt    Brian    Gavin
> Hotel         277      300     485
> Taxi           26       85
> Train/Bus      40       16      25
> Etc
> The list is more detailed then this but I think you see my point.
> I have created a Master workbook that has a tab for each employee and
> column A contains the same list of those expenses and column b onwards
> each months expenses. Each month I copy and paste each column to my
> master workbook relating to that employee. I total the rows into
> column 13 and compare the amount with a proportion of what he/she
> spent in the previous year. I also found a neat macro that crunches
> the rows that return a blank - no expenses under this heading. This
> was started off when I was new to excel but since reading the
> newsgroups I have become more interested and thought I would try a
> macro that might speed things up. So far I only figured out that the
> way would be to name the columns asa ranges in order for a macro to
> retrive their data. This would tie in with the worksheet tabs in my
> mMaster Workbook. I found a macro that does this based on the top cell
> (courtesy again of some code posted here previously)
> I tried recording the copying/pasting process through the macro
> recorder - not very successfull yesterday, so I spent most of today
> (saturday!) scouring Google(and David McRitchies Worksheet page where
> I copied an example of a loop-through) to see if I could figure that
> part of it out. Seems most of the replies posted in Google  relate to
> importing "Text" files.
> I was just gong to call it a day when I thought I would see if there
> was a reply!! Yippee.
> Thanks for responding and for reading this. I hope I have made myself
> clear.
> 
> K
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> Debra Dalgleish <dsd@contextures.com> wrote in message news:<3F6CA04F.2020704@contextures.com>...
> 
>>You describe the monthly workbook as containing expense details, but say 
>>you want to copy "the whole column". What does the a sheet in the 
>>monthly workbook look like? Is it really one column, or multiple columns?
>>
>>How are the target sheets set up? Do you really want to copy the new 
>>data into a new column (or columns)?
>>
>>Have you tried recording any of the steps in this process, to create 
>>some code that you can tweak?
>>
>>And finally, what is your ultimate goal in collecting this data? Do you 
>>want to summarize expenses by employee? By department? By expense type?
>>
>>Depending on your goal, it may be better to collect all the data on one 
>>sheet, and include the date, department, and employee name in each row. 
>>Then, you can create a pivot table to summarize the data, in a variety 
>>of ways.
>>
>>
>>Karen wrote:
>>
>>>Good morning
>>>I have a tall order and your help would be much appreciated.
>>>
>>>I always have problems with Ranges properties etc but have got by from
>>>finding solutions from the many excellent replies here particularly
>>>when it comes to VBA and Macro's. However I now seem to have a problem
>>>in finding a suitable solution butif you don't ask ...
>>>
>>>I have a workbook with several sheets with named employees.
>>>At the end of each month I get sent a workbook with details of time
>>>spent by each employee on different tasks. Their names appear on the
>>>top cell with all their expense details listed below. There could be
>>>several employees details  on the one sheet but there are several
>>>sheets with employees from different departments.
>>>What I would like to do is run a macro that will take the name from
>>>the tab in my workbook and examine the top cell in the employees
>>>workbook till it finds a match. It then copies the whole column back
>>>to my workbook under that employees named tab not overwriting any
>>>previous columns the idea being that I will at the year end have a
>>>worksheet with each of the 12 months data. Theres a bit more - after
>>>copying I would like to insert todays date formatted to mmm/yy so the
>>>name gets shifted to the second cell in my workbook.
>>>
>>>This is asking a lot but I hope there is someone brave out there to
>>>give it a go.
>>>
>>>
>>>Thanks a lot.
>>


-- 
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Debra
Your a gem - thanks a lot - wow!!!!
I have tried it and it works great!!!
There are now endless possibilities - the list also contained Time
data - all totalling some 400 rows - I have changed the 50 to 400, yes
400!!!
There is one thing I am off to find out - the input box asks for a
number - I tried inputting a month instead out of curiosity and got
'Debugged'. In the remote possibility that this becomes a hit at work
I will spend today (kids, mother-in-law,dogs etc permitting to 'error
check' this.

You have a great day - thank you - again.

Karen

Karen,

You're welcome, and thanks for letting me know that it worked for you.

To use text instead of month number, you could do something similar to 
the following (watch for word wrap in the code):

Dim strMth As String
strMth = InputBox("Please enter first 3 letters of month name")
i = Application.Match(strMth, Array("Jan", "Feb", "Mar", "Apr", "May", 
"Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"), 0)

instead of:
i = InputBox("Please enter the Month number")

Good luck!

Debra

Karen wrote:
> Debra
> Your a gem - thanks a lot - wow!!!!
> I have tried it and it works great!!!
> There are now endless possibilities - the list also contained Time
> data - all totalling some 400 rows - I have changed the 50 to 400, yes
> 400!!!
> There is one thing I am off to find out - the input box asks for a
> number - I tried inputting a month instead out of curiosity and got
> 'Debugged'. In the remote possibility that this becomes a hit at work
> I will spend today (kids, mother-in-law,dogs etc permitting to 'error
> check' this.
> 
> You have a great day - thank you - again.
> 
> Karen


-- 
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Debra
Thanks again. I attempted to paste the formula but kept getting a
compile error confirming the 'word-wrap' you warned about was correct.
I searched the previous postings on 'word-wrapping' as I didn't want
to show my naivity but alas no joy.
In any case I am going with your original script (marvelling each time
I press that button) - I will research restricting entries to
numerical 1-12,
There is lots to do - I never thought I could get this excited with
Excel!

Thanks again

Karen
PS I have used your internet site a lot and the examples are great.
Icing on the cake to have received help directly. Keep up the good
work.

Karen,

Word wrap means that a line break occurs where none was intended. For 
example, the following:

i = Application.Match(strMth, Array("Jan", "Feb", "Mar", "Apr", "May", 
"Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"), 0)

should be on the same line in the code, but it get broken into two lines 
in the posting.

After you paste it into the module, go to the end of the first line 
(after "May",), and press the delete key, and it should fix the problem.

Thanks for letting me know that you've found some useful information on 
my site. That's what makes it all worthwhile.

Debra

Karen wrote:
> Debra
> Thanks again. I attempted to paste the formula but kept getting a
> compile error confirming the 'word-wrap' you warned about was correct.
> I searched the previous postings on 'word-wrapping' as I didn't want
> to show my naivity but alas no joy.
> In any case I am going with your original script (marvelling each time
> I press that button) - I will research restricting entries to
> numerical 1-12,
> There is lots to do - I never thought I could get this excited with
> Excel!
> 
> Thanks again
> 
> Karen
> PS I have used your internet site a lot and the examples are great.
> Icing on the cake to have received help directly. Keep up the good
> work.


-- 
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html