Importing

[Karen]

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.

 

[Karen]

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'

 

[Debra Dalgleish]

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]

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]

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]

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]

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]

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]

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