related question - next step in process from last question

I have a workbook that has a sheet named 'Visa Consolidated'. I need a button on that sheet, that when clicked does the following WHEN date in Col F = today's date:

1. Opens up a new spreadsheet (example attached: Visa - smDec16-11-example.xls)

2. Pastes value from Col B (from Visa Consolidated) to Col A (Visa - smDec16-11-example.xls)

3. Pastes Value from Col E (from Visa Consolidated) to Col C (Visa - smDec16-11-example.xls)

4. Insert text 'EUR' into Col B (from Visa - smDec16-11-example.xls)

5. Insert text: 'smCurrentYearMonthDate1' into Col D (of Visa - smDec16-11-example.xls), where 'smCurrentYearMonthDate1' would format as: sm201112161.  2011 is tomorrow's year, 12 is tomorrow's month and 16 is tomorrow's date and 1 is the first in the sequence of numbers. (sm201112161, sm201112162, sm201112163, etc.)

If tomorrow's date was January 5, 2012, the value in Col D would be: 'sm201201051. If tomorrow's date were
February 18, 2012 Col D would be: sm201202181)

HOWEVER tomorrow's date cannot be a Saturday or Sunday. If tomorrow's date falls on a Saturday or Sunday it must go to Monday's date.

6. Save the file as "Visa - Sovereign smTomorrow's Date-year.xls" (example: 'Visa - smDec16-11.xls' If tomorrow's date was January 5, 2012, the file would be saved as: 'Visa - smJan05-12.xls')

HOWEVER it CANNOT save the file with a date that falls on a Saturday or Sunday. If tomorrow's date falls on a
Saturday or Sunday it must go to Monday's date. It is saved to this folder:
C:\Users\Michael\Desktop\My Dropbox\Sovereign\Visa\VisaLoadRequests

- saved, but is kept open in case edits have to be made.


Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

working on it
JaseStAuthor Commented:
Hi gowflow. Any progress with this one?
Yes working on it
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Just to make sure I understood well in Col D of the new file you want sm+value of next working day +sequential number from 1 to total number of records is that correct ?
like if you have 10 items in this file and next day is 12/28/2011 and is not Sat or Sun then the value in Col D would be

Correct ?

and an other one the Sheet name would be in today's date ( and the file name would be in Next working day date is that ok ?
JaseStAuthor Commented:
That is correct on the sequential number.

Not sure what you mean by "sheet name" but the file name would be the next working day.
Well here it is and sorry for late reply on this one due to holidays.

1) Make a new copy of you current visa file and give it a new name
2) Open VBA and doubleclik on Module1 and view 1 sub at a time by clicking on the bottom left icon.
3) Paste this new Sub after any end sub

Sub GenerateVisaFile()
Dim WS As Worksheet
Dim WSS As Worksheet
Dim NewWS As Worksheet
Dim MaxRow As Long, I As Long, J As Long, K As Long, DateCol As Long
Dim NewWb As Workbook
Dim NewWorkB As String
Dim TDate As String, Todate As Date, SDate As Date

If gstFolderWesternUnion = "" Then
    MsgBox ("You need to select a destination folder to store the Create Visa File created. Please go to Sheet 'Main' and select a folder before proceeding further.")
    Exit Sub
    If MsgBox("This process will create a new workbook with today's date and load in it all records in sheets 'Visa Consolidated' that bear today's date." & Chr(10) & Chr(10) _
        & "Are you ready to start this process ?", vbQuestion + vbYesNo, "Create Visa File") = vbYes Then
        Set WS = Sheets("Visa Consolidated")
        Set NewWb = Workbooks.Add
        Set NewWS = NewWb.Sheets("Sheet1")
        NewWS.Name = Format(Now, "mm-dd-yyyy")
        '---> Setup Next Date Visa file that should be on a working day
        '---> If Date is Saturday or Sunday it should go to Next Monday
        '---> or else on Next Day
        If Weekday(Now + 1) = 7 Then
            SDate = Now + 3
            If Weekday(Now + 1) = 1 Then
                SDate = Now + 2
                SDate = Now + 1
            End If
        End If
        NewWb.SaveAs Filename:=gstFolderWesternUnion & "Visa - Sovereign sm" & Format(SDate, "Mmmdd-yy") & ".xls", FileFormat:=xlExcel8
        NewWorkB = NewWb.Name

        J = 1
        K = 1
        'Date Col F
        DateCol = 6
        TDate = Format(Now, "mm/dd/yyyy")
        Todate = DateValue(TDate) + 1
        MaxRow = WS.UsedRange.Rows.Count
        WS.UsedRange.AutoFilter Field:=DateCol, Criteria1:=">=" & TDate, Operator:=xlAnd, Criteria2:="<" & Todate
        For I = 1 To MaxRow
            If WS.Range(I & ":" & I).EntireRow.Hidden = False Then
                If J = 1 Then
                    '---> Create the Header for the First Time
                    'Customer Identifier Currency    Amount  Merchant Reference
                    NewWS.Cells(J, "A") = "Customer Identifier"
                    NewWS.Cells(J, "B") = "Currency"
                    NewWS.Cells(J, "C") = "Amount"
                    NewWS.Cells(J, "D") = "Merchant Reference"
                    NewWS.Range(J & ":" & J).Font.Bold = True
                    '---> Copy Col B from Visa to Col A of New File
                    NewWS.Cells(J, "A") = WS.Cells(I, "B")
                    '---> Copy Col E from Visa to Col C of New File
                    NewWS.Cells(J, "C") = WS.Cells(I, "E")
                    '---> Put EUR to Col B of New File
                    NewWS.Cells(J, "B") = "EUR"
                    '---> Insert 'smcurrentYearMonthDate#' Visa to Col D of New File
                    NewWS.Cells(J, "D") = "sm" & Format(Year(SDate), "0000") & Format(Month(SDate), "00") & Format(Day(SDate), "00") & J - 1
                End If
                J = J + 1
            End If
        Next I
        WS.AutoFilterMode = False
        With NewWS.Columns("A:D")
           .HorizontalAlignment = xlCenter
        End With
        Application.DisplayAlerts = False
        For Each WSS In NewWb.Worksheets
            If WSS.Name <> NewWS.Name Then WSS.Delete
        Next WSS
        'Set NewWb = Nothing
        'Set NewWS = Nothing
        Application.DisplayAlerts = True
        MsgBox ("Workbook: '" & NewWorkB & "' has been created successfully")
    End If
End If
End Sub

Open in new window

4) SAVE the workbook.
5) Display sheet Main
6) click on Developper menu and click on Design Icon
7) click on commanbutton 'Cross Check Visa Consolidated w Database' and right click select copy and paste the new button under this one and name it Genrate Visa File like the below snapshot

  Generate Visa File
8) click anywhere in the sheet main to change focus and SAVe the workbook
9) doubleclick on the newly created button and insert GenerateVisaFile this way
Private Sub CommandButton10_Click()
End Sub
10) SAVE and Exit the workbook
11) give it a try

Let me know

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Sheet name is sheet name like Main is a sheet name Visa consolidated is sheet name the smnextworkingday.xls is workbookname !
JaseStAuthor Commented:
Oh, I see what you mean by sheet name. I know what sheet name means, but I didn't know what sheet you were talking about.

So... it worked perfectly except for a few minor issues:

1. The sheet name needs to be the same date as the file name. So, running the code today (12-27-2011) the sheet name should be 12-28-2011, the next business day.

Also, the Amount brought over from Col E in Visa Consolidated is not always in a two decimal format. (I know, you did not write that code.) However, when it is published to the sheet you just created, the amount brought into Col C needs to be formatted as currency with only 2 decimals, if possible.

One other point is that I'd really rather not have the newly created sheet saved in the Western Union folder. I try to keep things organized and separated. I already have the Access database there, which isn't where it use to be, and I'd rather not have this sheet in there as well.

Where it needs to go is C:\Users\Michael\Desktop\My Dropbox\Sovereign\Visa\VisaLoadRequests. However, if that is way too much work for you, then, well, I guess I'll have to live with it.

Let me know.

Thank you, gowflow. Works wonderful.  After this step is done, then we'll want to attach it to an email, with a specific subject and body.
well I understand that it get messy if you mix files in dffrent directories. I can add same as we did for Western Union a location for the Visa file is this what you want ?
for the other items it is already fixed, will wait for your reply to put alltogether.

For the email I understand once this one is done you said previoously that you will post a realted question right ?
Sorry looking at the issue if we need to add the folder I prefer it is in a separate quesiton (not for points) but as the details on how to implement will be quite lengthy as it affect several parts of the workbook.

And if you prefer it is hard coded then let it be, but I warn you I do not like thesehard coded issues as they open the door for playing in the code that could be detrimental at some point in time if not done by knowledgable person, anyway you have if I recall well experienced bad implementation of code which resulted in your workbook beeing ruined !!! so think well about it.

JaseStAuthor Commented:
Yes, once this is done I will post a related question to email the file.

In regards to where and how to save the newly created spreadsheet, I'll defer to you. It does NOT need to be a hardcoded location, but I'd prefer to not have to designate the place to save it every  time I open up the workbook as I have to do for the Western Union file (which I'll ask to have fixed in a related question.)

So, I will pose the saving file method and location in a related question and add the updates to the code to it as well.
JaseStAuthor Commented:
Wonderful work, gowflow. There seems to be nothing you can't do. I am greatly appreciative.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.