PDA

View Full Version : Automating some functions



rcbricker
06-01-2004, 11:11 AM
I have a spreadsheet that gets changed on a regular basis. It is saved with each change. I need to have certain Info from the spreadsheet to update an ongoing list on a spreadsheet (in a different workbook) used to track all info.

Example:

F1= Comapny name (needs to be updates into cell C# of tracking spreadsheet)
C2 = Date of changes (needs to be updated into Cell D# of tracking spreadsheet) {BTW anyone know how to get the cell with the date of changes to autofill only on the day of the initial change?}

The File name which is automated with the following Thread:


Sub SvMe()
Dim path
Dim fname
path = ActiveSheet.Range("A1")
fname = ActiveWorkbook.Name
ActiveWorkbook.SaveAs path & "\" & fname
End Sub

needs to auto enter into cell A# of tracking spreadsheet.

Now there is a total in the K Column. The cell changes as to the number of Rows needed to calculate all the invoices. The Total needs to Update into cell B# of tracking spreadsheet.

This is a big thing i am asking for but will save me an immense amount of time

Zack Barresse
06-01-2004, 01:31 PM
hi rcbricker! nice to see you on the board!

could you maybe upload an attachment as so we could get a much better idea of what your talking about. (at least for me. as, if you stick around, you'll see i need a good visual ;) )

rcbricker
06-01-2004, 03:13 PM
absolutly, will upload a sample of both spreadsheets tomorrow from work.

Thanks for the attention to my problem.

Going to try and upload the attachment.

rcbricker
06-02-2004, 08:02 AM
Explanation of the sheets. Didn't put this in last post cause wasn't sure it would take attachment.


All three sheets are copies of three different spreadsheets.

Sheets 2 & 3 are the source documents and Sheet 1 is the destination document. I Use 2 & 3 on a regualr basis. One spreadsheet per comapny per billing cycle. I need the areas on 2 & 3 to be entered in to the cells on 1 that they indicate. The Column will stay the same but the info would need to be entered into the next available cell.

If possible could someone tell me how to auto date sheets 2 & 3 in the Green Boxes ( only want them to date when the first cell under plaintiff).

any questions please ask.

Zack Barresse
06-02-2004, 10:45 AM
okay, not sure how your inputing information on this one, but for sheet one, right click your sheet name and insert this code in the right frame:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column = 1 And Not IsEmpty(Target) Then
Target.Offset(0, 3).Value = Format$(Date, "mm/dd/yyyy")
End If

End Sub

that will give you a static current date of when the information was put into column A (on the corresponding row in column D)

Questions on sheet 2:

the cells C2, F2 & L9, what information do you want in there? you have the same setup on sheet 3 also, in the cells A2, C2 & D11. is this information coming from sheet 1?? sheet 2?? to be filled in after everything else is?

not sure if this will work for sheet 2, but:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Not IsEmpty(Range("C7")) Then
Range("C2").Value = Format$(Date, "mm/dd/yyyy")
Range("F2").Value = Range("C7").Value
Range("L9").Formula = "=SUM($L$7:$L8)"
End If

End Sub

not sure if i've got this right or not..

rcbricker
06-02-2004, 11:14 AM
Ok the first code works as you said. The only difference is that Sheet 2 & 3 both start out with nothing in the cell below "Plaintiff". The date needs to be entered only when the 1st cell in that column (after the cell that contains "plaintiff") is populated. In the example spreadsheet it is C12. When data is entered into that cell ONLY then the date will be filled into cell C2 only.

Hope that is a better explanation of what i am looking for.

The second string you posted I wasn't sure what it did. :dunno . I am a n00b!:)

To answer your questions for those two sheets:

In the example given of sheet 2:

Cells C2, C4 & L14 will have data in them. That info needs to be auto filled into the first available cells in the the appropriate columns on sheet 1. THey would be auto populated as follows :

C2 populates C# on sheet 1.
C4 populates A# on sheet 1.
L14 populates B# on sheet 1.

One thing to remember is that all three of these sheets are cut & pastes of three seperate workbooks. It maybe easier to test on your end if you cut & paste them into their own books.

One other thing to remember is that Sheets 2 & 3 (when properly used as seperate workbooks) will be regenerated with each new billing cycle per company. So sheet 2 (named billing spreadsheet) is generated per company per cycle so I will end up with 12 seperate spreadsheets (that need C2, C4 & L14) to auto populate into the next available row in sheet 1. THe same goes for Sheet 3 (it is simply a different format for a different form of billing).

Last thing to note.

C2 will always be the same per spreadsheet
C4 will always be the same per spreadsheet
L14 will vary depending on the number of invoices bill for that company during that billing cycle. Jan may have 5 invoices and Feb may have 13 invoices billed. so the row that has total into (in the example it is row 14) will drop to last cell Populated in L + 2). So if there were 12 invoices billed the total line would be on line on row 25. as such it would be L25 for that billing period that would need to populate in the B# cell that was available.

Hope this explains everything.

Zack Barresse
06-02-2004, 11:46 AM
okay, the first code, i'd add a line to it, for error-checking's sake:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Row < 5 Then Exit Sub
If Target.Column = 1 And Not IsEmpty(Target) Then
Target.Offset(0, 3).Value = Format$(Date, "mm/dd/yyyy")
End If

End Sub just so there's no confusion.

and i don't understand this:
C2 populates C# on sheet 1.
C4 populates A# on sheet 1.
L14 populates B# on sheet 1.

so would C# be the first unpopulated cell in C? and so on with the others?

another question (bare with me): are all the workbooks going to be open at the same time when you transfer? and what triggers this, clicking a button, on exit, save? and lastly, are you going to be using the same sheets, or creating new ones?

as for finding the last empty cell in a column, this would be an example:
Range("A65536").End(xlUp).Offset(1).Selectwhich we could work into a routine for you. just need to get down the specifics (especially helpful for any would-be helpers passing by ;) ).

rcbricker
06-02-2004, 03:14 PM
so would C# be the first unpopulated cell in C? and so on with the others?
yes


another question (bare with me): are all the workbooks going to be open at the same time when you transfer? and what triggers this, clicking a button, on exit, save? and lastly, are you going to be using the same sheets, or creating new ones?


No. Sheet (workbook) 2 or 3 will be open and worked on. Then a button will be hit which needs to open sheet (workbook) 1, copy the appropriate info into the correct cells, Save sheet (workbook) 1, and close it. The same button should also incorporate the SaveAs VBA That I am using (posted earlier in this thread).

How is that?

Sorry for all the work i am reading my first VBA book now.:cool:

Zack Barresse
06-02-2004, 05:55 PM
okay, lemme see if i can get this straight..i've got them seperated now, into different workbooks. the three colored fields are the only information you want copied over to sheet (book) 1 (at the click of a button)?

rcbricker
06-02-2004, 06:04 PM
you got it.

Zack Barresse
06-02-2004, 06:24 PM
rcbricker, one last question (i'm sorry :confused: ), the code you posted above (1st post) - you know that you have to have the file path in cell A1 for that to work, right? are you still wanting to use this? if not, how would you differ it? if so, which of the three are you wanting that on?

rcbricker
06-02-2004, 06:28 PM
Man yer like a pit bull :rofl .

Basically i want the file to autosave with the company's name and the date the button is pushed. SHeets 2 & 3 need the save as. THe VBA for all this can be put into a personal file that is hidden. that way it is available for all XL files. That is probably what I am going to do with it. I mean I don't need to push the macro button unless it is one of these sheets. WHichever is easier.

The cell next to Firm (or company) name is the cell that needs to become the file name.

rcbricker
06-02-2004, 06:31 PM
Yer gonna kill me but i just came up with one more item i need added to the VBA code.

WHen the button is pushed I need it to do everything that has been requested...and I would like it to Email itself (meaning only sheet 2 or 3 whichever is being processed) to the company that is billing me.

This last request is something i am not even sure you can do. :confused:

Zack Barresse
06-02-2004, 06:53 PM
well, it can be done. :) i'd need to ask more questions tho. ;) and i'm just hoping that you're not getting sick of my questions!

but, what mail software do you use? outlook? and how do you want to determine what the recipients email address is, input box?

rcbricker
06-02-2004, 07:20 PM
I thought about it some more and the Emial will have to be a different button. I want to try and do some other stuff too. So lets finish the original problem first then we can make Email button. Who knows maybe we can edit this VBA Code or link them after we finish them.

Can an Access Form be copied into excel and have the fields reflected as cells?

If so I will be looking to do that and have the fields auto populated. I know I know I am lazy but what can you do:dunno .

The Email will be based on the Form or Sheets 2 or 3 and will need to take new contacts (companies) into account by allowing the user to enter the new contact info in a form that pops up. The emial command will also need to allow (and warn) when there is no email to send. In other words I don't want an error message when their is no email known or entered, but i want the VBA to finish running.

So to do the Email it will need to be anther Macro Button. It will need to search for the Email addresses from....:dunno dunno. I use Outlook at work and i am not sure if we can write a VBA to look through the outside business address book. If we can then it will need to look there. IF no contact info for that comapny (name from C4) then new contact needs to be added. Address is not required but we definitely want a warning if the VBA code doesn't find one (that way it can be entered or emailed manually).

The same button as the Email will need to first populate the Form that I will take from access( this is a form used by the accounting department, and for some reason they do not have it set up so that it moves electronically from where i access the database to their computers - I have to print out the form when i finish populating it and take it to them and they have to reenter it....Stupid!). If the form can be taken from Access then I will do so and post the sample here with what info I will need and from where.

BTW Thanks for all the hard work!!! I appreciate it.

rcbricker
06-03-2004, 11:28 AM
...how do you want to determine what the recipients email address is, input box?
If you mean where or how will the Email address will be entered I was thinking using a list of Email addresses so that the sheet can look up the company's name in a list and grab the Email addy from there.

Anne Troy
06-03-2004, 11:31 AM
Where would that list begin, RC? What sheet name and cell reference? You really need to be more specific...
:)

rcbricker
06-03-2004, 11:49 AM
Unfortunately that sheet is not created yet. I was just trying to find out if this all will work before i Generate the Spreadsheet. I mean can we write a VBA that will have the code look through my Outlook contacts for the email address?

If so then i do not need the spreadsheet. If not then I will build the spreadsheet from the Outlook contacts.

Anne Troy
06-03-2004, 12:01 PM
Yes. It can be done.

Zack Barresse
06-03-2004, 12:12 PM
..but probably a seperate issue, better taken up in the Outlook VBA Help. (and i'm still workin' on it! 'bout halfway there, just FYI)

Daxton A.
06-03-2004, 12:12 PM
If you ever get it to work...would you think about sending it to me. I dont need it right now but i would like to know and would like to learn from it. I know what its like creating something for about 6 months and your proud of it extremely. So its up to you if you want to send it to me my email is: (email removed by Dreamboat)

rcbricker
06-03-2004, 12:16 PM
gonnahave to sent you a case of beer for this. You are working yer (explict word here).

Anne Troy
06-03-2004, 12:28 PM
No. Emailing from Excel should stay right here. We've got code to do that. Just let me know what you need.

Dax: Please don't post your email like that; the bots will pick it up and spam you to death. If you make sure you can accept personal messages here under User CP--options, and you make sure you're receiving instant email notifications, you'll likely be just about as informed as anyone when that workbook is complete. You'll also find we offer email addresses @VBAExpress.com, just in case you'd rather keep your personal/work email address separate. Just PM me with a password; you'd be DaxtonA@ (here). I've removed your email address from your post.

rcbricker
06-03-2004, 12:40 PM
OK what i need the next step to this super sheet to do is to Automatically email the sheet to the company as a way of notifying how much is going to be paid.

What I would like to do is have the spreadsheet open up a new mail (outlook is always open so no need to open the program) with the correct email addy from my contacts list (in Outlook), put the subject line "placed in line for payment (date of email) and finally to attach itself to the email and hit send.

you think you can do it?

If so there is more (if and ONLY if i can recreate or import the Access form - I would like the Email button to perform an automated Form and print it out before it starts the Email function * same button though as i want the email to coincide with sending to accounting*)

Daxton A.
06-03-2004, 12:46 PM
Thank you for telling me that. Im a newb too pretty much. I used to know alot about VB & VBA but since I had a hemirage last August I have to learn it all over again. But i will try to remember that & Thank you for telling me that and for taking my email off of there. The only thing that I have a problem with is remembering short term things. Like if i try to remember something about 7 times by repeating or seeing it over and over, I have a better chance of remembering it.

Im sorry

Anne Troy
06-03-2004, 12:47 PM
correct email addy from my contacts list

Are you going to choose one from your contacts list? I thought you were going to put a list of email addies on a worksheet? I mean...if the email addy is known beforehand, it would of course be easier to do it that way. You could even hide the sheet that the email addies are on...

Anne Troy
06-03-2004, 12:47 PM
No apologies necessary, Dax. But now that we know, we'll do our best to help you even more. :)

rcbricker
06-03-2004, 03:07 PM
I can make a sheet but then would need to generate away to have it update itself when new companies are brought in.

I tried exporting the contacts into XL but found that it didn't bring over the street addresses and such and I will need them for the form.

I guess I will just take the time to make a spreadsheet.

One thing though is if I am going to be using a spreadsheet I would need to have the VBA acknowledge when an address is not found for a given company (a new one or an expert's company - they don't work enough to warrant their own default spreadsheet). I would like the VBA to run a form (which would be used to enter the new Company info onto the addy spreadsheet) and then finish once the new info is entered.

LOL this sheet is gonna have a mass of buttons on it.

rcbricker
06-03-2004, 04:09 PM
ok here is the sample Email sheet. Once you have it we can work on getting the code to do what I need it to do.

Anne Troy
06-03-2004, 04:15 PM
Ok. So you're only going to send it to one person?
Or anyone whose email address is listed in Sheet1!I2:I65536?

:)

rcbricker
06-03-2004, 05:04 PM
Anyone listed. The Firm name will match the names used in sheets 2 & 3. Unless it is a new company.

If it is new we need a way to add it to the list with out going directly to the email sheet ( which will probably be hidden).

Anne Troy
06-03-2004, 05:09 PM
So. You should probably have a userform that allows entry of a new company then...?

rcbricker
06-03-2004, 09:46 PM
Nope not yet threw that spreadsheet together so you could see it. WIll work on Userform tomorrow at work

rcbricker
06-04-2004, 10:22 AM
here is a better form it also includes the user form. just select A# and then data/form.

Anne Troy
06-04-2004, 11:30 AM
I have called rcbricker on the phone and he's going to fax and email some stuff. It sounds like we may be able to just emulate the form in an Excel file and be done with it. :)

Will keep all informed.

rcbricker
06-04-2004, 11:42 AM
here are a couple sheets that we may need.

The policy sheet is to automate the Policy info

The State list is for umm...states make it easier to manually enter an address i guess

The Payment for is to make a list box on the form for the Payment for line (upper right box first line).

Anne Troy
06-04-2004, 12:01 PM
Okay. What we have here are several workbooks, which can all be combined into one. I've got the form and I'm making it. Have sent a piece of it to rcbricker to see if it's close so I can continue. If they truly want it EXACT EXACT then we have a little tweaking, but just looking at the hard copy and my screen, they're already darn close.

As for the data file, we're going to have to do something different there, and one of the coders will be able to help with that.

You and I should get your workbook figured out, then upload it here for coding.

I need to know...you sent me one workbook for Acme company. How many companies are there? Your current layout isn't good if you want to be able to review this data...

Anne Troy
06-06-2004, 09:46 AM
Found out that there's multiple companies; like 150 of them, and rc keeps these in their own files. I told him we ought to put them in one worksheet and then automate, at the end of the month, the extraction of data to individual files for storage...

It's dawned on me that we are working with the assumption that you're going to continue to copy and paste from this other file that you receive.

Can I get a sample of that file? Perhaps there's no reason for you to copy and paste...

rcbricker
06-07-2004, 05:05 AM
ok by the end of the day I will have uploaded a couple files so that you all can see what I am talking about Dream has been chastising me about my lack of detail. So give me a couple hours and we will have some stuff to look at.

Daxton A.
06-07-2004, 07:01 AM
I wanna Thank you both if I haven't already done this for helping me out so much. Your not like everybody else i tell you that.

Daxton;)

rcbricker
06-07-2004, 07:33 AM
Ok here are the four forms that I see being needed.

Default Expense is the Form that will be used when processing Expense payments. Info from here is automatically added to Total ongoing. There is a Vlookup formula that will be used to find the email for the company. The spreadsheet will then email itself to that email address.

Default Indemnity will be used for processing Indemnity payments. Info from here is automatically added to Total ongoing. There is a Vlookup formula that will be used to find the email for the company. The spreadsheet will then email itself to that email address.

The Email Sheet will hold the Firm/Company info (including Email).

The Total Ongoing will be where the info is gathered for the whole year/per company.

I sure hope this makes theings a bit clearer.

Daxton A.
06-07-2004, 07:35 AM
I sent it to your email b/c it was to big to send in here.

Daxton

Daxton A.
06-07-2004, 07:53 AM
in this one instead of putting it in the validating one. I was just reading this one & left for a few seconds & forgot I had switched them b/c I wanted to see if I could be any help. Im sorry.:blush

rcbricker
06-07-2004, 08:10 AM
hey Dax, Not sure if you know this or not. There are no attachments.

Anne Troy
06-07-2004, 08:21 AM
Dax: I should delete your two posts above?
If so, I'll also delete THIS post of mine and rcbricker's.
:)

rcbricker
06-08-2004, 11:16 AM
Attachments must have been good cause haven't heard anything from you all.

Anne Troy
06-08-2004, 11:52 AM
RC: Dax is working on an Access database to perform this task.

I sent your form via email. How is it?

rcbricker
06-08-2004, 11:57 AM
didn't get it.:confused:

Anne Troy
06-08-2004, 12:01 PM
Re-sent to @randall...

rcbricker
06-08-2004, 12:11 PM
got it. server was fuzzy earlier. Very nice job Dream:vv

Anne Troy
06-14-2004, 09:39 AM
Where are we on this one, Richard? Still need our help?

rcbricker
06-14-2004, 07:41 PM
sorry was sick. Yes still need help have not heard anything back yet.

Anne Troy
06-14-2004, 07:45 PM
Have not heard anything back from who, rc?

rcbricker
06-15-2004, 05:57 AM
I got your Form spreadsheet and got a couple PMs from Firefly. But i have not gotten anything on the spreadsheets that i submitted.

Anne Troy
06-15-2004, 06:04 AM
Okay.
The Default Indemnity: You're going to take all of these and put them into one worksheet, right?
The Email Tests: Isn't needed, you just need to add a worksheet with company info, including email address(es) as one of the columns.
The Default Expense: This is the same as indemnity. You were just going to add a column for TYPE. Now you can choose the type indemnity, expense, and there were two others, right?
The Total Ongoing: Is kinda of void since we've discussed the way you'll do it now.

Did you forget our phone conversation?

rcbricker
06-15-2004, 10:20 AM
nope but firefly wasn't part of it. I remember our conversation. I thought there were still some VBA that was being done. If not I will test the spreadsheet.

Anne Troy
06-15-2004, 10:30 AM
LOL. He's firefytr (as in fireman), and we can't do VBA until we know your exact spreadsheet layout. :)

Zack Barresse
07-08-2004, 12:13 PM
...If not I will test the spreadsheet.

And the results... :dunno