Automating some functions

[rcbricker]

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]

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]

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]

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]

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]

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. . 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]

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 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).Select

which 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]

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.

[Zack Barresse]

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]

you got it.

[Zack Barresse]

rcbricker, one last question (i'm sorry ), 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]

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]

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.

[Zack Barresse]

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]

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 .

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. 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]

...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]

Where would that list begin, RC? What sheet name and cell reference? You really need to be more specific...

[rcbricker]

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]

Yes. It can be done.

[Zack Barresse]

..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)