couple of questions from a stumped person

  • Thread starter Thread starter mads
  • Start date Start date
M

mads

Hi,
couple of questions, mainly to do with macro's i think. I'm new t
macro code & VBA (actually until last week i didnt know that macro'
used VBA)

firstly some background:
i have a workbook which has 12 worksheets in it, one for each month
each worksheet has information for each day that month in rows. i hav
another worksheet (HWAWD) in the same workbook which collaborates thi
information in a list for the end of each week. this sheet i
protected.

what i would like help with:
i want another (template) sheet in the same workbook which retrieve
specific data from HWAWD sheet, depending upon the date which i
entered (into maybe a userform box is it?), i.e. if the date 24/04/0
was entered, then the macro would retrieve the specific informatio
relating to that date in the HWAWD sheet and enter it into th
appropriate cells in this template sheet. there are a few other field
which would need to be entered in this sheet manually by the user.

HOLD ON, not quite finished yet.

THEN, i would like the sheet (only) e-mailed to a specified recipien
(which i have sorted out ) but at the same time i would like the shee
copied and added to a different but existing workbook for referenc
with the week ending date as the sheet name and then saves it an
closes automatically. and the template sheet cleared of the data, read
for next week. - i can only seem to find code to copy the sheet and ad
it to a new workbook.

i know this is a lot to ask for, especially considering its my firs
post but i have not got a clue.

i dont neccessarily need someone to give me the whole code but mayb
just point me in the right direction.
if screenshots are needed to further understand, then please let m
know and i will post them on here.
thanking you in advance for reading this,
regards,

mad
 
So we have 3 steps if I have understood you:

1- get data from a data sheet to a front sheet
There are many ways of doing this and can be done in mos
situations with the VLOOKUP worksheet function. I would explore thi
before heading for macro's and the control toolbox.

2- copy the worksheet to a new book and e-mail with VBA-

Sheets("Sheet1").Copy
ActiveWorkbook.SendMail "(e-mail address removed)", "Spreadsheet"
False

3- Open existing workbook, copy sheet over, save new book and clos
with VBA-

Workbooks.Open "C:\Book A"
Workbooks("Book1").Sheets("Sheet1").Cop
Before:=Workbooks("BookA").Sheets(2)
Workbooks("BookA").Close True


Hope this helps

Dunca
 
Workbooks.Open "C:\Book A"
Workbooks("Book1").Sheets("Sheet1").Cop
Before:=Workbooks("BookA").Sheets(2)
Workbooks("BookA").Close True
Click to expand...

i take it that this would just add a new sheet each time to th
existing workbook?

and thankyou for giving me a headstart on this. it has helped me.
anyone else wishing to add anything else, please do. open to al
suggestions
 
Mads,

Some suggestions.

First, post with a subject line that helps readers identify your post. Some
of us peruse and respond to a lot of posts, and may skip an unrecognizable
one. SOmething like: "Extract data to another sheet."

In general, an application like yours works better with all the data in one
sheet, using an additional column for the date. You will find as you go
along that there are many Excel tools available to you that don't work when
the data is across multiple worksheets. Initially consolidating them is a
simple copy/paste operation adding the date column and filling down. An
Autofilter can quickly reduce this list to a given date (for pasting
elsewhere as you have asked). Advanced filter can actually put the data
elsewhere also. The list can be sorted on various columns as needed. Etc.
etc. etc. Strongly recommended.
 
Workbooks.Open "C:\Book A"
Workbooks("*Book1*").Sheets("Sheet1").Copy Before:=Workbooks(
*BookA*").Sheets(2)
Workbooks("BookA").Close True

So this will take book1 sheet1 and copy it to bookA after the secon
sheet.

Dunca
 
cheers for the reply.

i posted the question and then realised about the topic heading so
tried to go back and change it but it doesnt allow a change to th
forum. all it did was change the topic once you are inside so apologis
about that.

if i was to put the data into one sheet then the sheet would end u
being VERY long (365 days worth of data, 11 columns for each date etc
hence me splitting it up slightly. also it is easier to find th
worksheets e-mailed all in one place should i need to go back to them
 
Mads,

365 isn't a lot of rows. You have the same amount of data either way. An
autofilter can reduce it as needed. There should probably be a single date
column. Still recommended.
 
Back
Top