Solved

Consolidating XLS files

Posted on 2014-07-24
49
112 Views
Last Modified: 2014-10-18
I have three XLS files. I have data that is subtotaled by rep, I need to take information by rep from each of the three files and cut and paste it into one file on three different tabs).

I would like each file to be saved individually that is created with a file name convention of 000_Jun2014.

I can certainly provide the four files that are required for this with previous information for someone who has the ability to help me do this process.
0
Comment
Question by:trayceejay
  • 26
  • 23
49 Comments
 
LVL 29

Expert Comment

by:gowflow
ID: 40218976
Yes able to pls post the files.
gowflow
0
 

Author Comment

by:trayceejay
ID: 40218981
Following is how it the three files map to the Monthly Rep Statement:
SA646 = Comm
SA612 = Shipped
OP512 = Booked

I want it to include all for each rep # and the subtotal line within each tab
SA612-JUNE2014.xls
SA646-JUNE2014.xls
OP512-JUNE2014.xls
MonthlyRepStatement.xlsx
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40219006
ok tks for the files

BUT
1) in the individual files we have 2 tabs All and Subtotal which one we pull info from ?
2) in the tab Shipping the first column is Sort what is this ? it does not exist in file SA612 also you have PriSlp but not in shipping.
3) in sheet BOOKING (Not yet shipped) your missing column N Country and same for PriSlp

Basically what I gather from your request is that you wish to save form each and every file (to their corresponding tab Shipped, Comm, Booked) the full data per salesman or only the subtotal ??? if latter then the row that have subtotal does not have the full info like address name etc... if you want the full data then can't we simply pull it from the All tab ???

Not clear what you want I prefer you stated in plain English.
gowflow
0
 

Author Comment

by:trayceejay
ID: 40219021
use the subtotals tabs

the information in there lines up to each of the tabs in the other

the first column is PriSlp or Sort -- same thing to me

Country can be added to the two it's missing or not there.

I usually just cut and paste all the lines  for each in the subtotal tab of the three files into each tab. Titles can be changed. The All tab could be used but then I would need to total or subtotal on the monthly xls and that why i was using the subtotals. I am open to change these as necessary to help automate the consolidation. I just did them all and it took me 45 minutes. I would think there's an easier way.
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40219034
45 minutes for you manually will be reduced to a simple click in a fraction of a second.

I proposed the following you would correct me if the info (as do not know the content) is wrong:

1) Copy the All tab to the corresponding Tab (Comm, Shipped, Ord) depending of the origin file
2) Sort the destination tab by Sales rep
3) Subtotal by sales rep with the indicated subtotals in the destination tab
4) Save the file ... ??? this is not clear as in your original post you say:

I would like each file to be saved individually that is created with a file name convention of 000_Jun2014.

and here we have only 1 file that have 3 tabs. So is it 3 files with 1 tab each or 1 file with 3 tabs ???

Let me know
gowflow
0
 

Author Comment

by:trayceejay
ID: 40219045
i need to generate a different file with the information in those 3 files combined into one file for each rep that i can provide as their individual commission statement. I need to extract by prislp code and send to them individually. Each does not see the others.
0
 

Author Comment

by:trayceejay
ID: 40219048
So I create 25+ files each month. I just created 014_0614, 015_0614, 020_0614, 100_0614 and so on. Does that make sense?


I attached a few here to show you.
014-0614.xlsx
100-0614.xlsx
405-0614.xlsx
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40219144
yes now I understand !!!!
make sense let me see I will hv to work on this but now need to step out will get back to you
gowlfow
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40222366
Sake good order and for simplicity reason I will prompt the user to type in a monthyear sequence which will be the one used for all file naming ie 0614 or 0714 etc... as a bit tricky to find what month the data is layed out as in 2 files you have it june 2014 but in 1 file invoivcing date is May 2014 and furthermore the dates are not Excel dates but strings YYYYMMDD

Do you have any problem with the fact that once you launch the macro it will prompt you for a month-year sequence that you will need to input ?

Also what happens if for some reason while saving a file we find out that the file already exist in the directory ? here should we override ? I propose to save all the files created in a new folder created that will be as follows:
MMYY hhmmss
where mmyy = 0614
hhmmss = hours minutes seconds that the macro is ran
like 0614 102432
this way you will very unlikely happen in a scenario where files already exists and this way your data is always preserved and you have a way to know when you created the files for a specific run.

Let me know your thoughts.

Sorry for delay but I am taking my time to make it right.
gowflow
0
 

Author Comment

by:trayceejay
ID: 40223789
Entering in the year and month is fine.
I like you idea for preserving the data.

I appreciate your time and effort, trust me, I do!
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40228686
Tks sorry for the delay as having here couple of days of holidays but will soon attend yours b patient !! :)
gowflow
0
 

Author Comment

by:trayceejay
ID: 40228708
thanks, I appreciate that!
0
 

Author Comment

by:trayceejay
ID: 40256421
just curious if you are able to finish this for me?
0
 

Author Comment

by:trayceejay
ID: 40279618
are you helping me on this still? I need to do this again this weekend. please advise ASAP
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40285261
Extremely sorry for this delay due to extended holiday am working on it right now are you still interested with the solution ?
gowflow
0
 

Author Comment

by:trayceejay
ID: 40285592
absolutely still interested, thanks!
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40286311
good so keep hanging it wont be late.

and ...
sorry again !!!
gowflow
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40287679
question
Will always the 3 files to pull info from will be named as follows ?
OP512-
SA612-
SA646-
?? for sure followed by the month and year ?

like is the sequence OPS512 and SA612 and SA646 always there or it may change every month ?
gowflow
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40287706
Sorry dumm question I just read your initial post and you clearly mention:
SA646 = Comm
SA612 = Shipped
OP512 = Booked

So it answers it !!!
tks will revert
gowflow
0
 
LVL 29

Accepted Solution

by:
gowflow earned 500 total points
ID: 40290423
Finally here it is

The below file comprises the macro that will generate the sales rep statements.

How it works:
Load the file and make sure your macros are enabled.
Activate the green button
You will be prompt to choose a month/year sequence and by default it takes the current one you may override this
Then you are prompt to locate the Comm,Shipp,Book files that by default will look in the current directory where this file has been saved you may look elsewhere that is no problem but best is to put the monthly files where this macro file resides.
You have a trace of what is happening and once all files are opened it will create a new directory where it will put all the individual sales files created.
Once it is finished it will let you know you may see the trace moving on.

1 draw back to this you will notice is that when you open a sales rep file it is not always poitioned on the first cell and this may be annoying I have been working on trying to resolve this but found only 1 way where the screnn would flicker wjhen it is producing the files and this is also annoying.

Anyway check it run the macro and let me know.

here is the code for all this it is in a module you can see it.

Option Explicit


Function CreateMonthlyStatementperRep(sMonth As String) As String
Dim WS As Worksheet
Dim WBCOMM As Workbook
Dim WSCOMM As Worksheet
Dim WBSHIP As Workbook
Dim WSSHIP As Worksheet
Dim WBBOOK As Workbook
Dim WSBOOK As Worksheet
Dim WBREPS As Workbook
Dim WSREPS As Worksheet
Dim WSCURR As Worksheet

Dim MaxRowCOMM As Long, MaxRowSHIP As Long, MaxRowBOOK As Long, MaxRowCURR As Long, SrtRow As Long, EndRow As Long
Dim I As Long, J As Long, K As Long
Dim sFilePath As String, sFileName As String, sQuestion As String, sFilter As String, sRepFile As String, sSheetName As String

'---> Initialise
Set WS = ActiveSheet
Application.ActiveWindow.WindowState = xlMaximized
sFilePath = ActiveWorkbook.Path
WS.Range("C8:C" & Rows.Count).ClearContents
WS.Range("C8").NumberFormat = "@"
WS.Range("C8") = sMonth

'---> Disable Events
With Application
    .EnableEvents = False
    .ScreenUpdating = False
    .DisplayAlerts = False
End With

'---> Start opening files
For I = 1 To 3
    Select Case I
        Case 1
            sQuestion = "Please Choose Commission File - SA646 for the month " & sMonth
            sFilter = "SA646*.xls*"
            
        Case 2
            sQuestion = "Please Choose Shipped File - SA612 for the month " & sMonth
            sFilter = "SA612*.xls*"
            
        Case 3
            sQuestion = "Please Choose Booked File - OP512 for the month " & sMonth
            sFilter = "OP512*.xls*"
            
    End Select
    
    Do
        sFileName = GFileName(sFilePath, sQuestion, sFilter)
        If sFileName = "" Then
            If MsgBox("No file has been selected" & Chr(10) _
                & "[OK]     to continue and select a file." & Chr(10) _
                & "[Cancel] to Exit." & Chr(10) & Chr(10) _
                & "Please make a selection.", vbInformation + vbOKCancel, sQuestion) = vbCancel Then
                GoTo CloseAndExit
            End If
        End If
    Loop Until sFileName <> ""
    
    Application.ScreenUpdating = True
    WS.Activate
    Application.ActiveWindow.WindowState = xlMaximized
    Application.ScreenUpdating = False
    
    Select Case I
        Case 1
            On Error Resume Next
            Set WBCOMM = Workbooks.Open(sFileName)
            Application.Windows(I).WindowState = xlMinimized
            Set WSCOMM = WBCOMM.Worksheets("Subtotals")
            MaxRowCOMM = WSCOMM.Range("A" & WSCOMM.Rows.Count).End(xlUp).Row
            If Err <> 0 Then
                Application.ScreenUpdating = True
                WS.Range("C" & I + 9) = "ERROR: While openning file or file not opened, abort"
                Application.ScreenUpdating = False
                On Error GoTo CloseAndExit
            Else
                Application.ScreenUpdating = True
                WS.Range("C" & I + 9) = sFileName & " - opened successfully"
                Application.ScreenUpdating = False
                On Error GoTo 0
            End If
            
        Case 2
            On Error Resume Next
            Set WBSHIP = Workbooks.Open(sFileName)
            Application.Windows(I - 1).WindowState = xlMinimized
            Set WSSHIP = WBSHIP.Worksheets("Subtotals")
            MaxRowSHIP = WSSHIP.Range("A" & WSSHIP.Rows.Count).End(xlUp).Row
            If Err <> 0 Then
                Application.ScreenUpdating = True
                WS.Range("C" & I + 9) = "ERROR: While openning file or file not opened, abort"
                Application.ScreenUpdating = False
                On Error GoTo CloseAndExit
            Else
                Application.ScreenUpdating = True
                WS.Range("C" & I + 9) = sFileName & " - opened successfully"
                Application.ScreenUpdating = False
                On Error GoTo 0
            End If
            
        Case 3
            On Error Resume Next
            Set WBBOOK = Workbooks.Open(sFileName)
            Application.Windows(I - 2).WindowState = xlMinimized
            Set WSBOOK = WBBOOK.Worksheets("Subtotals")
            MaxRowBOOK = WSBOOK.Range("A" & WSBOOK.Rows.Count).End(xlUp).Row
            If Err <> 0 Then
                Application.ScreenUpdating = True
                WS.Range("C" & I + 9) = "ERROR: While openning file or file not opened, abort"
                Application.ScreenUpdating = False
                On Error GoTo CloseAndExit
            Else
                Application.ScreenUpdating = True
                WS.Range("C" & I + 9) = sFileName & " - opened successfully"
                Application.ScreenUpdating = False
                On Error GoTo 0
            End If
            
    End Select
    
Next I

'---> Show Trace
Application.ScreenUpdating = True
WS.Activate
Application.ActiveWindow.WindowState = xlMaximized
Application.ScreenUpdating = False
    
'---> Create Folder
sFilePath = sFilePath & "\" & sMonth & " - " & Format(Now, "dd-mmm-yy hhmmss")
MkDir sFilePath
Application.ScreenUpdating = True
WS.Range("C13") = sFilePath
WS.Activate
DoEvents
Application.ScreenUpdating = False

'---> start Process
For I = 1 To 3
    
    Select Case I
        Case 1
            Set WSCURR = WSCOMM
            MaxRowCURR = MaxRowCOMM
            sSheetName = "COMMISSIONS"
        Case 2
            Set WSCURR = WSSHIP
            MaxRowCURR = MaxRowSHIP
            sSheetName = "SHIPPINGS"
        Case 3
            Set WSCURR = WSBOOK
            MaxRowCURR = MaxRowBOOK
            sSheetName = "BOOKINGS (NOT YET SHIPPED)"
    End Select
    
    '---> Heart of the routine loop thru the current file and start creating
    '     Sub workbooks by sales rep.
    '     if the sales rep alreay exit then open it and add a new sheet
    '     if the sales rep does not exist then create a new workbook and then add the worksheet
    
    For J = 2 To MaxRowCURR - 1
        '---> Get Start and End row for Rep
        SrtRow = J
        K = J
        Do
            K = K + 1
        Loop Until InStr(1, WSCURR.Cells(K, "A"), WSCURR.Cells(J, "A")) = 0
        EndRow = K - 1
        
        '---> Test to see if the rep found already have a workbook
        sRepFile = Dir(sFilePath & "\" & WSCURR.Cells(J, "A") & "-" & sMonth & ".xlsx")
        If sRepFile = "" Then
            '---> Create the new Workbook
            WSCURR.Copy
            Set WBREPS = ActiveWorkbook
            sRepFile = sFilePath & "\" & WSCURR.Cells(J, "A") & "-" & sMonth & ".xlsx"
            WBREPS.SaveAs Filename:=sRepFile
            Set WSREPS = ActiveSheet
            WSREPS.Name = sSheetName
            
        Else
            '---> Open the Existing workbook and add the current worksheet
            sRepFile = sFilePath & "\" & WSCURR.Cells(J, "A") & "-" & sMonth & ".xlsx"
            Set WBREPS = Workbooks.Open(sRepFile)
            WSCURR.Copy after:=WBREPS.Worksheets(WBREPS.Worksheets.Count)
            Set WSREPS = ActiveSheet
            WSREPS.Name = sSheetName
        End If
        
        
        '---> Remove Unecessary Rows
        WSREPS.Range("A" & EndRow + 1 & ":A" & WSREPS.Rows.Count).EntireRow.Delete
        If SrtRow > 2 Then
            WSREPS.Range("A2:A" & SrtRow - 1).EntireRow.Delete
        End If
        
        '---> Autofit
        WSREPS.UsedRange.EntireColumn.AutoFit
        
        WSREPS.Activate
        WSREPS.Cells(1, "A").Select
        
        '---> Save and close file
        WBREPS.Close savechanges:=True
        
        
        '---> Update Trace
        WS.Activate
        Application.ScreenUpdating = True
        WS.Range("C" & I + 14) = "Creating / updating Rep: " & WSCURR.Cells(J, "A")
        DoEvents
        Application.ScreenUpdating = False
        
        '---> release variables
        Set WBREPS = Nothing
        Set WSREPS = Nothing
        
        '---> Goto next Rep
        J = EndRow
        
        Application.ScreenUpdating = True
        WS.Activate
        DoEvents
        Application.ScreenUpdating = False
        
    Next J
    
Next I

'---> Completed Successfully flag it TRUE
CreateMonthlyStatementperRep = sFilePath
Application.ScreenUpdating = True
WS.Range("C18") = "Rep Statements for " & sMonth & " Completed Successfully"
Application.ScreenUpdating = False
GoTo NormalExit





CloseAndExit:
WS.Range("C" & I + 9) = "ERROR: While openning file or file not opened, abort"
DoEvents

NormalExit:
On Error Resume Next
WBCOMM.Close savechanges:=False
WBSHIP.Close savechanges:=False
WBBOOK.Close savechanges:=False
Set WBCOMM = Nothing
Set WSCOMM = Nothing
Set WBSHIP = Nothing
Set WSSHIP = Nothing
Set WBBOOK = Nothing
Set WSBOOK = Nothing
On Error GoTo 0

'---> Enable Events
With Application
    .EnableEvents = True
    .ScreenUpdating = True
    .DisplayAlerts = True
End With

End Function

Function GFileName(Fol As String, Title As String, sFilter As String) As String
Dim vrtSelectedItem

With Application.FileDialog(msoFileDialogFilePicker)
    .InitialFileName = Fol & "\" & sFilter
    .Title = Title
    .Filters.Add "Excel ", "*.xls*", 1
    .InitialView = msoFileDialogViewDetails
    .Show
    
    
    For Each vrtSelectedItem In .SelectedItems
    GFileName = vrtSelectedItem
    Next vrtSelectedItem
End With

Set vrtSelectedItem = Nothing

End Function

Open in new window

gowflow
MonthlyRepStatement.xlsm
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40307633
Any chance to have seen or checked out the proposed solution ?
gowflow
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40310333
Any news on this question did you have a chance to see the proposed solution ?
gowflow
0
 

Author Comment

by:trayceejay
ID: 40316480
i did see the proposed solution. I hope to be able to try it over the weekend here.
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40316791
ok fine let me know of results.
gowflow
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:trayceejay
ID: 40321147
testing this now and i got a bug
0
 

Author Comment

by:trayceejay
ID: 40321158
here's the what it looked like where it was in the main file as well as when I clicked debug if that helps. let me know if you need the files that i am using this month, I can certainly send them to you...is it possible that I can email them to you instead of posting them here?
tracy@cementexusa.com

thanks!
bug-whereat.docx
bug.docx
0
 

Author Comment

by:trayceejay
ID: 40321161
BTW this is an awesome macro, as quickly as it is running this is going to save me SOOOOOOOO much time.
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40321429
I saw the files you posted can you please tell me what is the error that you got ? what did it say ?
gowflow
0
 

Author Comment

by:trayceejay
ID: 40321653
It was a Microsoft Visual Basic error that reads:

Run-time error '1004':
That name is already taken. Try a different one.

Continue is grayed out. End, Debug (default) and Help buttons are available.
0
 

Author Comment

by:trayceejay
ID: 40321656
It happens AFTER the trace shows cycling through all reps (through 775) on Creating / updating for SHIPPED
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40321690
I know my question is when you run the macro you get an error at this point there is a window that gives a message I need to know what is the message and in this window there is Debug End you pressed on debug it gave you the line in yellow. I need to know the error.

Pls post the 3 files you used that gave you the error. I will check here and see why.
gowflow
0
 

Author Comment

by:trayceejay
ID: 40321691
can i email the files to you, since they are current files, I would prefer to not make them public
0
 

Author Comment

by:trayceejay
ID: 40321692
This was the error. I have snapped a snippet of it in the attached word doc, is that what you wanted?

I attached the files, I will delete them as soon as you get them.

Thanks,
mvberror.docx
SA646-AUG2014.xls
SA612-AUG2014.xls
OP512-AUG2014.xls
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40321693
Oops yes I saw it.
Open file SA612-June2014.xls the one you posted here and presume you are getting the same in your files.

select A1 in the sheet subtotals then press on the keyboard touch called END then press on the down arrow you will see the cursor go to line Total 775. If again you press on END then down arrow you will see that the cursor will move downward couple of blank lines and then stop at Grand Total

This is the problem I had noticed that so I removed all the blank lines and macro worked well. Don't know it is a bug in your system that produces this file and it seems it adds blank lines before printing Grand total.

So you will need to check always the 3 files and make sure that your last row is the grand total and no blank rows in between.

Let me know
gowflow
0
 

Author Comment

by:trayceejay
ID: 40321696
also didn't realize there were 2 windows when you click debug
bug2.docx
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40321697
I just saw your August file and it is the same problem in SA612-AUG2014.xls you will see Total 775 and if you press END then arrow down you will see Grand Total way down you need to delete the blank lines in between.
gowflow
0
 

Author Comment

by:trayceejay
ID: 40321699
ok, let me fix that and then try it.
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40321700
read my post it give you the solution.
gowflow
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40321702
ok
gowflow
0
 

Author Comment

by:trayceejay
ID: 40321713
that was exactly the problem! I will remember to do that each time!

Wow this is super fast and exactly what I needed - you have been a life saver. Wish I had posted this years ago!!
0
 

Author Closing Comment

by:trayceejay
ID: 40321717
PERFECT!
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40321744
Your welcome glad I could help.
gowflow
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40321771
Just a thought, Presume when these files are produced you take each one and mail it to the appropriate salesman is that correct ?

Would you like to automate this ? if the answer is yes then you need to have an excel file that have in 1 column the salesman code and in the second column his/her email

If you can get this and will to go an extra mile then post a new question and put a link of this new question here and I will be glad to assist.
gowflow
0
 

Author Comment

by:trayceejay
ID: 40321930
Yes and it would be awesome.

Could I cc: myself to insure it went? There may be multiple email addresses.

Subject line would always be: Cementex Commission Statement: Month
Where the month would always change.

Can I include the body of the email that would go to each and perhaps a mail merge style of the names?

For instance: 405 goes to three people and I would address the email to the following Bob, Lynne and Mike,
and then the body of the email would be the same for all of the agencies.

Let me know if possible and if so what fields you would need.
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40321972
yes as long as these details are mention somehow in an excel file.

Just post the new question and explain all this and post the excel in the new question and put a link in here I will assist.
gowflow
0
 

Author Comment

by:trayceejay
ID: 40322068
0
 

Author Comment

by:trayceejay
ID: 40389161
same error code so haven't even gotten to text for the message:
 
 Loop Until InStr(1, WSCURR.Cells(K, "A"), WSCURR.Cells(J, "A")) = 0
        EndRow = K - 1
        
        '---> Test to see if the rep found already have a workbook
        sRepFile = Dir(sFilePath & "\" & WSCURR.Cells(J, "A") & "-" & sMonth & ".xlsx")
        If sRepFile = "" Then
            '---> Create the new Workbook
            WSCURR.Copy
            Set WBREPS = ActiveWorkbook
            sRepFile = sFilePath & "\" & WSCURR.Cells(J, "A") & "-" & sMonth & ".xlsx"
            WBREPS.SaveAs Filename:=sRepFile
            Set WSREPS = ActiveSheet
            WSREPS.Name = sSheetName

Open in new window

0
 

Author Comment

by:trayceejay
ID: 40389182
I cannot get this to work - only does the commission tab. there's no blank spaces. I am not sure what the problem is :(
 Loop Until InStr(1, WSCURR.Cells(K, "A"), WSCURR.Cells(J, "A")) = 0
        EndRow = K - 1
        
        '---> Test to see if the rep found already have a workbook
        sRepFile = Dir(sFilePath & "\" & WSCURR.Cells(J, "A") & "-" & sMonth & ".xlsx")
        If sRepFile = "" Then
            '---> Create the new Workbook
            WSCURR.Copy
            Set WBREPS = ActiveWorkbook
            sRepFile = sFilePath & "\" & WSCURR.Cells(J, "A") & "-" & sMonth & ".xlsx"
            WBREPS.SaveAs Filename:=sRepFile
            Set WSREPS = ActiveSheet
            WSREPS.Name = sSheetName

Open in new window

0
 

Author Comment

by:trayceejay
ID: 40389198

 Loop Until InStr(1, WSCURR.Cells(K, "A"), WSCURR.Cells(J, "A")) = 0
        EndRow = K - 1
        
        '---> Test to see if the rep found already have a workbook
        sRepFile = Dir(sFilePath & "\" & WSCURR.Cells(J, "A") & "-" & sMonth & ".xlsx")
        If sRepFile = "" Then
            '---> Create the new Workbook
            WSCURR.Copy
            Set WBREPS = ActiveWorkbook
            sRepFile = sFilePath & "\" & WSCURR.Cells(J, "A") & "-" & sMonth & ".xlsx"
            WBREPS.SaveAs Filename:=sRepFile
            Set WSREPS = ActiveSheet
            WSREPS.Name = sSheetName

Open in new window

SA646-SEPT2014.xls
SA612-SEPT2014.xls
OP512-SEPT2014.xls
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Is your Office 365 signature not working the way you want it to? Are signature updates taking up too much of your time? Let's run through the most common problems that an IT administrator can encounter when dealing with Office 365 email signatures.
In  today’s increasingly digital world, managed service providers (MSPs) fight for their customers’ attention, looking for ways to make them stay and purchase more services. One way to encourage that behavior is to develop a dependable brand of prod…
Viewers will learn how to maximize accessibility options in an Excel workbook for users with accessibility issues.
Video by: Zack
Viewers will learn about various customizable options in Excel 2013.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now