Solved Multiple Source Sheets Sorts by Date into New Document

April 9, 2019 at 08:47:28
Specs: Windows
I have an excel document with two sheets. One called "Outstanding Orders" and one called "Closed Orders". In both sheets, each row is a different order with the date populated in column A.

I want to send out a monthly sheet with all orders for the past 30 days. However, the sheet I have contains information in columns A-G for each order, some of which I do not want to send out.

Goal:
Create a separate excel document that pulls information from the current one. The new document pulls data from both "Outstanding Orders" and "Closed Orders", based on the date in column A, pulling the last 30 days worth of dates, and populates the data into a single sheet on the new document. I want the row data to include columns A-D only (E, F, and G contain sensitive info). Once populated on a single sheet, have the data sort by date in descending order. The desire is to have the new sheet be protected, so that I can send it out to multiple people without risking them editing information or viewing information they do not need to know.

Can this be done? Can I lock the entire sheet after and still have the formulas/codes work as intended?

Thank you.


See More: Multiple Source Sheets Sorts by Date into New Document

Reply ↓  Report •

✔ Best Answer
April 17, 2019 at 13:10:55
Try this version.

Additions included:

1 - Code to Password protect Summary Sheet (commented out at this point, just for ease of use.)
2 - Code to alternate the fill color of every other month using 2 shades of Green.


Sub CreateSummary()

Dim lstSrcRw As Long
Dim nxtDstRw As Long
Dim fPath As String
Dim fName As String
Dim dateRw As Long
Dim dateColor As Integer
Dim colorToggle As Integer


'Don't let the user see what is happening
     Application.ScreenUpdating = False

'Add a new sheet, give it a name
    Sheets.Add After:=Sheets(Sheets.Count)
    ActiveSheet.Name = "Summary Sheet"
    
'Copy data from "Outstanding Orders"
    Sheets("Outstanding Orders").Columns("A:D").Copy _
         Sheets("Summary Sheet").Range("A1")
         
'Copy data from "Closed Orders"
'First determine how many rows to copy and where to put it
    lstSrcRw = Sheets("Closed Orders").UsedRange.Rows.Count
    nxtDstRw = Sheets("Summary Sheet").Range("A" & Rows.Count).End(xlUp).Row + 1

    Sheets("Closed Orders").Range("A2:D" & lstSrcRw).Copy _
         Sheets("Summary Sheet").Range("A" & nxtDstRw)
         
'Sort the Summary Sheet
    ActiveWorkbook.Worksheets("Summary Sheet").Sort.SortFields.Add Key:=Range( _
        "A2:A" & Rows.Count), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Summary Sheet").Sort
        .SetRange Range("A1:D" & Rows.Count)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .Apply
    End With
    
'Delete everything after row 31
    Rows("32:" & Rows.Count).Delete Shift:=xlUp
    
'Move the Summary Sheet to its own Workbook
    Sheets("Summary Sheet").Move

'Start "Color Dates By Month" Section

'Clear Alternate Row Coloring For Column A
  Columns("A:A").FormatConditions.Delete
  
'Swap Green fill on every other month

'Set initial Green
  dateColor = 4
  
'Value to set alternate Green
  colorToggle = 39
  
'If consecutive cells contain the same month, fill with same Grenn
    Cells(2, 1).Interior.ColorIndex = dateColor
        For dateRw = 3 To 31
          If Month(Cells(dateRw, 1)) = Month(Cells(dateRw - 1, 1)) Then
                   Cells(dateRw, 1).Interior.ColorIndex = dateColor
                   
'If month has changed, toggle the Greens and fill cell
          Else
             dateColor = dateColor + colorToggle
             colorToggle = -colorToggle
                   Cells(dateRw, 1).Interior.ColorIndex = dateColor
          End If
        Next
'End "Color Dates By Month" Section
    
'Protect sheet with password
    'ActiveSheet.Protect Password:="QQQQ"
    
'Create Path and File name variables
      fPath = "You Need A Path Name Here "
      fName = "Summary Sheet.xlsx"
      
'Save options
'*** This will open the SaveAs Dialog Box to your default Save As folder until
'*** you fill in the fPath variable information
  
' fSaveName = Application.GetSaveAsFilename(fPath & fName)

'*** This will actually save the file without asking. You'll still to
'*** fill in a proper path.

'   Application.DisplayAlerts = False
'    ActiveWorkbook.SaveAs Filename:= _
'        fPath & fName _
'   Application.DisplayAlerts = True

'*** Close the Summary Sheet workbook
'   ActiveWindow.Close

End Sub

message edited by DerbyDad03



#1
April 9, 2019 at 19:42:46
If you are going to send the sheet to other people and you don't want them to change anything, locking the sheet is certainly possible. However, I don't see why it would contain any formulas or code.

Why not create the summary sheet by pasting in Values?

As far as the sorting goes, is there any reason that all rows from both sheets can't be copied to the summary sheet, sorted and then have anything beyond Row 30 (31 if there is a header Row) deleted?

That seems easier than sorting the dates in each source code and then figuring out which 30 rows (some from each sheet, I assume) to copy to the summary sheet.

(I don't want offer any code until I understand your requirements.)

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03


Reply ↓  Report •

#2
April 10, 2019 at 07:22:32
I would agree with you that a simple copy paste is by far the simplest solution. I want to automate this because one of our receptionists will be handling the monthly updates and they aren't very computer savvy. Maybe that will change in the future, but this adds some protection for me for the time being. Just having a completed document for the receptionist to attach to an email is less concerning with practically no risk of mistake.

I thought about simply using formulas to pull data, and then having a VBA code sort it once pulled. I wanted to check if this was the right method for going about this, or if you have other suggestions.

"As far as the sorting goes, is there any reason that all rows from both sheets can't be copied to the summary sheet, sorted and then have anything beyond Row 30 (31 if there is a header Row) deleted?"

-- I have no issue with that method. As long as we delete columns E, F and G.


Reply ↓  Report •

#3
April 10, 2019 at 08:46:48
re: "I would agree with you that a simple copy paste is by far the simplest solution. I want to automate this"

I was not suggesting a manual copy/paste. I would still use VBA to create the summary sheet, but I would have the VBA paste in the values from the other sheets, not the formulas.

I was asking for clarification on this question: Can I lock the entire sheet after and still have the formulas/codes work as intended?

My question is: Why do you need the formulas (or the code) in the summary sheet? If no one will be changing it, why does it need to contain any formulas?

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

Related Solutions

#4
April 10, 2019 at 08:58:52
I misunderstood what you were saying. Values would be fine.

I'm not sure I understand what you're asking here. I've had scenarios in the past where locking a cell or sheet resulted in an error in the macro. I was just curious if this error would happen in this scenario.

As for why I need formulas: My initial thought process was using formulas to pull the data into the summary sheet. There would not be any data changing formulas, just the code needed to import data.

Does that clarify this better for you?

message edited by Steven4321


Reply ↓  Report •

#5
April 10, 2019 at 09:56:53
I am envisioning a scenario where no macro resides in the summary sheet. The code can reside in a standard VBA module and reference the appropriate sheets. It would unprotect the summary sheet, move data into it, sort it, delete the rows and columns that you don't want, then protect it again.

The summary sheet could then be sent to whoever you want, protected against changes, but containing no formulas or macro.

Does that sound like it will work for you?

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03


Reply ↓  Report •

#6
April 10, 2019 at 10:15:46
Yes. How would you go about activating the code though? How would the list update as needed?

The source sheets will change, so the monthly summary sheet will be different.


Reply ↓  Report •

#7
April 10, 2019 at 11:46:54
You know your work process better than I do. Maybe put a button on the QAT to run the code whenever you want to create a new summary sheet. That's what I do with code that I use a lot. You could store it in a personal.xlsb workbook which would make it available whenever Excel is open.

BTW...I don't know how much you know about VBA, but I'm sure that whatever I offer isn't going to be 100% to your satisfaction. I don't know anything about your header rows, etc.

I'm hoping that you can edit whatever it is that I offer to make it work for your exact requirements.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#8
April 10, 2019 at 14:07:17
I think having the code run automatically whenever the document is opened would be the easiest. Least room for error. Im sure I can teach someone to click a button, but I want to avoid any possible stupidity. So, lets work off the goal of this being 100% automated. The only manual work may be opening and saving the document. Something along those lines. I'll defer to your judgement here. You'll know your code better than I will.

My knowledge is still what I would consider beginner. Ive used it a bunch and have found many websites like this one to be very helpful in learning. I'll do what I can, but do you mind if we keep this conversation going in the event I have more questions?

Also for your knowledge, my header is nothing special. Its just text and fill color in row 1 of both sheets. No formulas or odd formatting. Not sure if that helps.


Reply ↓  Report •

#9
April 10, 2019 at 18:11:57
OK, now I'm confused.

In your very first post you said:

"Create a separate excel document that pulls information from the current one."

and

"The desire is to have the new sheet be protected, so that I can send it out to multiple people without risking them editing information or viewing information they do not need to know."

So, in my mind I'm picturing you, as the Master Of This Universe, possessing the main document (the one with all the sheets and all the data) and using this code to create the single sheet document that you would then send to the other people. After all, you did say "that I can send it out to multiple people", didn't you?

What would be the point of having the code create the separate document every time you opened it? What happens after you make changes to the "Outstanding Orders" and "Closed Orders" sheets? Would you save it, close and then open it again just to have the code run?

"I'm sure I can teach someone to click a button, but I want to avoid any possible stupidity. "

I must be missing something about your work process. Who, besides yourself, would ever have to click the button? Even if you were going to hand this entire thing off to someone else, how would you expect them to create the summary sheet after changes were made to the other ones?

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#10
April 10, 2019 at 22:19:20
Back in #2, he said a receptionist was going to run the macro and email the resulting summery. He also implied he didn't want to spend more than 5 minutes training the receptionist to do this task, which I guess is why he wants this to be as automated as possible?

That said, he seems so singularly focused on this specific task, he's forgetting the other times this spreadsheet will be opened. Like when adding an outstanding order, or when closing an order. That, or this spreadsheet is automatically generated from some other program, in which case we've got an entirely different can of worms here.

How To Ask Questions The Smart Way


Reply ↓  Report •

#11
April 11, 2019 at 03:26:02
Ok, I forgot the receptionist part.

In any case, the point about all the other times that the workbook is opened and closed still stands. 100% automation is provably too aggressive of a goal.

I also have my concerns about the use of this spreadsheet to help run a business. Even if we get it right and "100% automated" at the start, who will be making updates to the code as things change? Running a business based on free code from a help forum could be a risky proposition.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#12
April 11, 2019 at 07:15:43
Let me first apologize for any confusion. I am certainly focused on the one task, so I may be overlooking some things and vaguely explaining others. Let me try to clarify.

I control the master/source/orders document. Myself and one other person (who is savvy enough to where I'm not worried about issues) are the people who update the orders. We send out a monthly update for pure informative purposes to keep everyone on the same page, so the recipients aren't neccesarily working directly off of this log. Your concern about running a business is well recieved, but this is more used to improve communication and transparency between staff.

The issue is that my superiors want this sent on the 1st of every month. That is sometimes difficult as I (and the other gentlemen who uses the master document) are often out of the office. As such, we'd like to have our department receptionist/secretary send out the monthly log. In a perfect world, she would just have to write an email, select the document to attach, and send it.

I was initially thinking the second document automatically update. I have no idea if that is even possible, which is why in post #8, I suggested a code run to update the sheet when the receptionist opens (or clicks a button in) the second document. The receptionist does not have access to the master. If you have other execution suggestions, im open to ideas

Ideally, I've been trying to convince the company to get an orders management solution. That would solve a lot of our difficulties, but those solutions have a cost that the company is not yet willing to invest in. This is for internal orders for business needs. Our business doesnt revolve around orders or sales, so it isnt the priority investment. Im trying to make things as easy as possible without something more robust available.


Reply ↓  Report •

#13
April 11, 2019 at 09:28:41
re: "I was initially thinking the second document automatically update. I have no idea if that is even possible, which is why in post #8, I suggested a code run to update the sheet when the receptionist opens (or clicks a button in) the second document. The receptionist does not have access to the master."

A few thoughts come to mind...

In order for the opening of the second document (which I have been calling the "summary sheet") to initiate the update (100% automatic) some code must reside in that document. So Sam the receptionist opens the document, it updates, it gets saved and then sent to Bob in Accounting. Now Bob opens the document. The code is going to want to update the document again. The same will happen when Barb from HR opens the document, but of course she opened it 3 days later so Barb's update doesn't match the update that Bob printed out and brought to the meeting.

Since we don't want the updates to happen unless it is Sam that opens it, we need a way to prevent updates from happening when anybody else opens the document. Should the code check the user name or computer name via the OS? Should Sam know the secret value to enter in cell AZ1752 to allow the code to run? How do we prevent the sheet from updating when other people open it. On the other hand, how do we allow others (e.g. you) to update the sheet but not let e.g. Bob in Accounting update it?

So, I don't particularly care for any "100% automatic" process. I'd go all the back to what I said in #7:

Put a button on the QAT to run the code whenever you want to create a new summary sheet. That's what I do with code that I use a lot. You could store it in a personal.xlsb workbook which would make it available whenever Excel is open. Set up the receptionist's workstation the same way so that Sam has the button too.

Maybe R2.3 has some other thoughts on this issue. Until we resolve this matter, there is no sense in writing any code.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#14
April 11, 2019 at 12:21:01
You bring up a great point of concern. I definitely overlooked this. Your QAT option seems to make the most sense then. I can add it to the receptionists computer and teach her to click on the button.

Im now considering a button on my end to simply generate them for her. But that too requires me be in the office...at which point I may as well send the log myself. You've given me something to think about. I'm going to run this by the other gentlemen who can change the master and see what decision we come to. Ill get back to you likely tomorrow.

Thank you for your valued input. Im the meantime, if R2.3 has another suggestion, I'd love to hear it.


Reply ↓  Report •

#15
April 11, 2019 at 12:49:49
Another thought came to mind.

There are ways to have the master workbook available to anyone that needs to create the summary sheet while at the same time hiding information from those that shouldn't see it, e.g. Sam the receptionist.

Code could be placed inside the workbook such that all sheets are hidden when opened and a password is requested. Depending on the password entered, certain sheets would be available to that user. Sam knows the password that makes a single sheet visible. On that sheet is a button that allows them (Sam) to create the Summary Sheet. That's all that they can do.

Enter a different password and all the sheets become visible. That password is only known to you and the other authorized individual.

Or maybe the workbook opens to a single "Dashboard Sheet" with 2 buttons:

"Create Summary Sheet"

"Edit Master Sheets"

When a user (Sam) clicks "Create Summary Sheet" that's all that happens.

If the user chooses "Edit Master Sheets" a password box pops up. After the correct password is entered, the sheets that you don't want anyone else (Sam) to see will become visible. With that method even a temp could create the summary sheet, because no password is required.

With either of those methods, the Master workbook could be stored on a network drive and all the code would reside within it. No need for QAT buttons on multiple machine, no maintenance of personal.xlsb files on Sam's, your or your buddy's machines, etc.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#16
April 11, 2019 at 20:13:55
Let's step outside of Excel for a moment.

If I had to set up an automatically generated email, I'd pick a server I controlled. In this instance, preferably one where I could install MS Office. Then I'd write a VBScript that does the summary, and emails it. To get the script to run, I'd use Scheduled Tasks on that server. Would something like that be an option here?

If not, you could have the user run a similar script, possibly wrapped up in an HTA so you get a UI, to generate the summary document.

In either case, the same problems remain. Any major changes to this workbook would require this external script to be updated. There's a similar problem with VBA, but at least that's in the workbook you're modifying. The other major problem is the main document needs to be easily accessible to the script, and that would mean keeping its name and location fairly static, and presumably network accessible.

How To Ask Questions The Smart Way


Reply ↓  Report •

#17
April 12, 2019 at 07:28:53
Thank you both for your continued assistance. I respect the commitment you two have to helping others with this sort of thing. Hopefully one day I can be as knowledgeable.

Razor2.3:
Using scheduled tasks on a server would not be possible here. Not only do I not control the server, but that method seems to be far beyond my skillset even if I did.

The HTA method could work, but that too seems to be more complicated than I was prepared for. Im always willing to learn, but I think keeping this within Excel in some way, as you've seemingly alluded to, would be best.

DerbyDad03:
I like your idea of using a "Dashboard Sheet". I'm not opposed to placing the file on a network drive as long as some information can be password protected. A few thoughts with this method:

1) Would this be done with an additional "Dashboard" sheet and a workbook VBA, or a VBA for the "Dashboard" sheet only?

2) How would the "Create Summary Sheet" button execute? Would it open a different sheet that then needs to be saved in an additional step (think a print preview page)? Or would it simply pop open a file save window and skip the preview?

I would prefer the latter method if possible. A preview page is unneeded if none of the information can be edited and the code always creates the same format.

3) A point of concern here as well. Can the code be a "Save As" function and force text to be placed in the 'File name' text box? Or even better, already populate the file name as "Order Summary"? The summary sheet can be overwritten monthly.

In other words, I don't want this to execute a 'Save' function (Unless it always overwrites the "Order Summary" file) nor do I want the save window to pre-populate the 'File name' as my master document name. It may sound silly but I'm sure someone will just click 'Save' and overwrite my file.

4) Is it possible to access the "Dashboard Sheet" once I've entered the "Edit Master Sheets" sheets? In the event I am in the office and can do this on my own after editing the master sheets, would I be able to simply click on the "Dashboard Sheet" on the botton of excel and be able to "Create Summary Sheet"? Or would I have to save the file and then reopen it to view the dashboard again?

Not an issue either way. Just a curiosity for ease of use.

5) You may have to highlight the password part of the code you provide for my own knowledge. I'll need to know where this code is so that I can know how to create/change passwords. I hope I'm able to identify it myself, but I wanted to put this out there just in case I can't. I already know this code will be more complicated than I've used before.
______

Let me know if any of that is confusing, and what additional information you may need to write your code.

Thanks again.


Reply ↓  Report •

#18
April 12, 2019 at 07:48:18
Sticking with VBA, I played around a bit to show you how a "dashboard sheet" might work. This set-up is very rudimentary. It's just to illustrate the concept. It obviously needs to be fleshed out. The goal is to allow for a single workbook that all parties can access. All of the code would be contained within that workbook. The workbook will open with just the Dashboard sheet visible. Once the workbook is open, only those that know the password for the "master sheets" will be able to access them.

Try this:

1 - Create a workbook with 3 sheets:

Dashboard
Outstanding Orders
Closed Orders

2 - On the Dashboard sheet, create 2 shapes/buttons with text that could read something like...

Click Here To Create Summary Sheet

and

Click Here To Edit Master Sheets (Password Required)

3 - In the VBA editor, place these macros in a Standard Module:

Sub CreateSummary()

    MsgBox "Your Summary Sheet Has Been Created." & vbCrLf & vbCrLf & _
           "(Just kidding, this is only a test.)"

    'The code to actually create the Summary sheet would go here.

End Sub


Sub MasterSheets()
Dim masterPW As String
Dim badPW As Integer

'Get Password from user
getPW:
   masterPW = Application.InputBox("Enter Password For Access To Master Sheets", vbOKCancel)
    
'If Password is correct, Make the master sheets visible
    If masterPW = "QQQQ" Then
      Sheets("Outstanding Orders").Visible = True
      Sheets("Closed Orders").Visible = True
      Sheets("Outstanding Orders").Activate
      
'Allow for 3 passwords attempts, then Exit Sub
    Else
     
     badPW = badPW + 1
      If badPW <> 3 Then
        MsgBox "Incorrect Password. Please try again" & vbCrLf & vbCrLf & _
               3 - badPW & " Attempts left"
        GoTo getPW
      
      Else
        
        MsgBox "Sorry, Too Many Incorrect Passwords Entered"
        Exit Sub
      
      End If
    End If
End Sub

4 - In the VBA Editor, place these macros in the ThisWorkbook module:

Private Sub Workbook_BeforeClose(Cancel As Boolean)

'Hide the Master sheet before closing
    Me.Sheets("Outstanding Orders").Visible = False
    Me.Sheets("Closed Orders").Visible = False
    
End Sub

Private Sub Workbook_Open()

'Just in case, make sure that the Master sheets are hidden on Open
    Me.Sheets("Outstanding Orders").Visible = False
    Me.Sheets("Closed Orders").Visible = False
    
End Sub

5 - On the Dashboard sheet assign the "CreateSummary" macro to one of your shapes/buttons and assign the "MasterSheets" macro to the other.

6 - Eventually you'll need to password protect the VBA code and hide it from viewing, but for the purpose of this test, don't worry about it.

7 - Save the workbook, open it, test the shapes/buttons.

8 - Let me know what you think.

message edited by DerbyDad03


Reply ↓  Report •

#19
April 12, 2019 at 08:41:07
I like the proof of concept. Tested and works as described. You've answered my thoughts for #1 and #4. Since there is no actual code to create a summary sheet, can you address my #2 and #3?

Reply ↓  Report •

#20
April 12, 2019 at 10:33:29
Before I post any more code, I want to let you know about this tutorial. It will help you fix little problems with your code instead of having to come back here for every minor glitch. It will also help you reverse-engineer code that you find on the web. I use most of these debugging techniques on a daily basis. I also learned a lot about writing code by applying them to code that I been given or found. They can help answer the question "How does the code do that?"

https://www.computing.net/howtos/sh...


message edited by DerbyDad03


Reply ↓  Report •

#21
April 12, 2019 at 10:55:49
Make sure that you do this in a test workbook.

Replace the Create Summary macro from my previous post with the following code. This code will actually create the Summary Sheet as a separate workbook and then save it (eventually).

The comments that I've included throughout the code should explain what it is doing in each section. You will note that the entire Save Options section is commented out because I don't know your Path name. (That’s why I used the word “eventually” earlier)

What I did was include that section just so that you can see some options. You can either have the code open the Save As dialog box (with the path and filename included) or you can have the code simply overwrite the existing Summary Sheet file (without asking) each time it runs. You have other options also, so just ask if you want to do something different. In any case, you are going to have to fill in the path information for the fPath variable. You'll need a complete path, including the server name, folder/sub-folder name(s), slashes, etc. It must be a properly formatted path name that your OS will recognize it.

A good way to obtain the correct format is to record a macro while doing a Save As to the proper location and then do a copy/paste of the path name from the recorded macro.

If you run the code as is, it will create the Summary Sheet as a new workbook and then End because the Save and Close section is commented out. The main goal is to show what the code can do. Saving it is relatively easy.

You also won't see anything happen until the Summary sheet is created because I set Application.ScreenUpdating = False. If you want to watch the code run, step by step, add a break point in the VBA editor so that when you click the button the code will start to run and then stop at the breakpoint. From there you can position the VBA editor window such that you can see both the code and the workbook. You can then Single Step through the code by pressing F8 and watch the code do its thing.

Try this and let me know what you think.


Sub CreateSummary()

Dim lstSrcRw As Long
Dim nxtDstRw As Long
Dim fPath As String
Dim fName As String

'Don't let the user see what is happening
     Application.ScreenUpdating = False

'Add a new sheet, give it a name
    Sheets.Add After:=Sheets(Sheets.Count)
    ActiveSheet.Name = "Summary Sheet"
    
'Copy data from "Outstanding Orders"
    Sheets("Outstanding Orders").Columns("A:D").Copy _
         Sheets("Summary Sheet").Range("A1")
         
'Copy data from "Closed Orders"
'First determine how many rows to copy and where to put it
    lstSrcRw = Sheets("Closed Orders").UsedRange.Rows.Count
    nxtDstRw = Sheets("Summary Sheet").Range("A" & Rows.Count).End(xlUp).Row + 1

    Sheets("Closed Orders").Range("A2:D" & lstSrcRw).Copy _
         Sheets("Summary Sheet").Range("A" & nxtDstRw)
         
'Sort the Summary Sheet
    ActiveWorkbook.Worksheets("Summary Sheet").Sort.SortFields.Add Key:=Range( _
        "A2:A" & Rows.Count), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Summary Sheet").Sort
        .SetRange Range("A1:D" & Rows.Count)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .Apply
    End With
    
'Delete everything after row 31
    Rows("32:" & Rows.Count).Delete Shift:=xlUp
    
'Move the Summary Sheet to its own Workbook
    Sheets("Summary Sheet").Move
    
'Create Path and File name variables
      fPath = "You Need A Path Name Here "
      fName = "Summary Sheet.xlsx"
      
'Save options
'*** This will open the SaveAs Dialog Box to your default Save As folder until
'*** you fill in the fPath variable information
  
' fSaveName = Application.GetSaveAsFilename(fPath & fName)

'*** This will actually save the file without asking. You'll still need to
'*** fill in a proper path.

'   Application.DisplayAlerts = False
'    ActiveWorkbook.SaveAs Filename:= _
'        fPath & fName _
'   Application.DisplayAlerts = True

'*** Close the Summary Sheet workbook
'   ActiveWindow.Close

End Sub

message edited by DerbyDad03


Reply ↓  Report •

#22
April 12, 2019 at 12:00:51
I cleaned up the MasterSheets macro a little. For one thing, the user can Cancel out of the password entry dialog boxes and not have to enter 3 bad passwords.


Sub MasterSheets()
Dim masterPw As Variant
Dim badPW As Integer
Dim myAns As Integer

getPW:

'Get password from user, allow Cancel
   masterPw = Application.InputBox("Enter Password For Access To Master Sheets", _
                                   "Master Sheets Access")
    If masterPw = False Then Exit Sub
    
'Unhide sheets if correct password is entered
    If masterPw = "QQQQ" Then
     Sheets("Outstanding Orders").Visible = True
     Sheets("Closed Orders").Visible = True
     Sheets("Outstanding Orders").Activate
     
'Allow 3 bad passwords then exit, allow cancel
    Else
     badPW = badPW + 1
      If badPW = 3 Then
        MsgBox "Sorry, Too Many Incorrect Passwords Entered"
        Exit Sub
      Else
        myAns = MsgBox("Incorrect Password. Please try again" & vbCrLf & vbCrLf & _
                3 - badPW & " Attempts left", vbOKCancel)
           If myAns = vbCancel Then
                Exit Sub
           Else
                GoTo getPW
           End If
      End If
     End If
End Sub


message edited by DerbyDad03


Reply ↓  Report •

#23
April 12, 2019 at 12:03:10
BTW...the CreateSummary macro doesn't lock/protect the Summary Sheet. I can make that happen, but I'm sure that there will be other issues to deal with, so I will wait to hear back from you.

message edited by DerbyDad03


Reply ↓  Report •

#24
April 15, 2019 at 13:39:59
DerbyDad03,

I was hoping to get on this today and play around with the code in my document, but I haven't had the opportunity. I'll try to tackle it tomorrow and get back to you.


Reply ↓  Report •

#25
April 16, 2019 at 07:02:23
Post #22 works perfectly without issue.

Post #21 works as intended as well, but it opens a new workbook with the correctly formatted Summary Sheet visible and unsaved. The new document simply says "Book1" and then I can edit as usual as if it were a basic excel sheet. In other words, no save function runs. Ive tried both save methods and I can't figure out what I'm doing wrong.

Also is it possible to overwrite conditional formatting in the summary sheet via this code? I'd like to keep all formatting it pulls from the other sheets, but I'd love to change the formatting of column A to highlight dates.

Example: Current month dates have green background, last month has yellow background. The 30 rows likely will only have around 5 dates from the previous month, but it'll be nice to easily differentiate. Also if in the future I were to increase the rows to the last 60, it would certainly help.

A concern: Ive set up the document to show the "Dashboard" sheet only with the other sheets hidden. I saved the document and when I reopen, only as Dashboard sheets shows (As it should). However, when I access master sheets and view all of them, they remain shown with subsequent saves. If I add data and want to save that, the sheets remain shown upon next open. I can easily "fix" this by hiding the sheets again everytime, but is there a way to automatically hide them when the document is opened? This would make saving data easier without having to lock my sheets again.


Reply ↓  Report •

#26
April 16, 2019 at 07:27:38
re: Post #21 works as intended as well, but it opens a new workbook with the correctly formatted Summary Sheet visible and unsaved. The new document simply says "Book1" and then I can edit as usual as if it were a basic excel sheet. In other words, no save function runs. Ive tried both save methods and I can't figure out what I'm doing wrong.

What you did wrong is not read what I said in the opening remarks of Post 21. I explained (in detail) why the Summary sheet is not being saved. I'll leave it to you to go back and read it again.

As far as you being able to edit it, I addressed that in #23.

re: Also is it possible to overwrite conditional formatting in the summary sheet via this code? I'd like to keep all formatting it pulls from the other sheets, but I'd love to change the formatting of column A to highlight dates.

There was no mention of formatting in your previous posts. It would really, really help if you supplied all your requirements so I don't have to keep modifying the code. Each new bolt-on makes it less efficient and often times leads to a complete re-write. What else aren't you telling me about the output that you are looking for?

re: However, when I access master sheets and view all of them, they remain shown with subsequent saves. If I add data and want to save that, the sheets remain shown upon next open.

If you set up the workbook as described in #18, that should not be happening. #18 includes code that should hide the Master sheets when the workbook is closed and then, just be safe, hides them again when the workbook is opened.

In your #19 you said that the code "worked as described". It works every time for me. The only sheet that is visible upon opening is the Dashboard. Are you sure that you have included the code that needs to go in the ThisWorkbook module? That is the code that hides the sheets upon closing (and opening).

message edited by DerbyDad03


Reply ↓  Report •

#27
April 16, 2019 at 09:01:36
I misunderstood what you were explaining in regards to the save function. I read and understood what you said about the code ending after creating the summary sheet. However, I wrongly assumed that adding the correct path would prompt a save function. I had thought that areas were commented out because you dont have the path, but I understand now the entirety of that function is left out.

Upon reviewing this all this morning, I overlooked post #23. I must have thought it was my own #24 as I was rushing to test this. My apologies.

The conditional formatting was a thought I just had when testing this. It was not an initial requirement, which is why I just asked if such a thing is possible. There is nothing you don't already know. I understand why adding things can add complications, but I figured it wouldnt hurt to ask. I didn't intend to cause any issue with my question.

As for hiding the sheets. Good catch; I forgot to add the code in the workbook. I forget that I tested that in a different test workbook and the code wasnt in what I'm using now (I copied my actual document and am using the copy as a test sheet so that I have data to see the code work with).

With all of that said, your code is working as you've described. Beyond my own oversight, I don't see any issue's arising here from the code.


Reply ↓  Report •

#28
April 16, 2019 at 11:15:05
OK, I'm glad that you sorted out all the existing issues.

Now I ask that you explain what you are looking for as far the formatting. Since I can't see your workbook from where I am sitting, I know know what you mean by "overwrite conditional formatting in the summary sheet" and "I'd like to keep all formatting it pulls from the other sheets, but I'd love to change the formatting of column A to highlight dates."

I obviously don't know what is Conditionally Formatted and how, what isn't CF, what formatting stays, what goes, what is new, etc.

If you can supply some specifics, I'll see what I can do.

message edited by DerbyDad03


Reply ↓  Report •

#29
April 16, 2019 at 12:19:28
I have two conditional formatting rules on the "Outstanding Orders" and "Closed Orders" sheets. One is to alternate row colors, set from $A:$N using this formula:

=MOD(ROW(),2)=0

I would like to keep this alternating colors rule. Your code already does exactly that.

The second is a "Format only cells with: This Week" rule, set to Column A only. This is the rule I was thinking needs to be overwritten. I was curious if a code could instead add a background color in Column A cells based on the month. The color doesn't need to be specifically set for each month. Even alternating colors by month would be useful. Not a necessity at all; just a curiosity I had.

My reasoning is that I use the "This Week" rule to better identify outstanding orders that I need to complete this week, and track how many orders were completed this week. For the summary sheet, I don't need that function. But it would be nice to send people a sheet that more easily identifies the month of the order.

Does that help clarify?


Reply ↓  Report •

#30
April 16, 2019 at 18:19:31
There are lots of things that can be done with the month column, such as using VBA to apply a built-in Conditional Format, (e.g. Last Month, This Month, Next Month) or letting VBA set the format directly, e.g. setting a different color for every month of the year.

Those are just 2 of the probably uncountable things that VBA could do based on a dates in a column.

If you'll tell me what you want, instead of leaving it up to me, I'll have something to aim for and we can be closer to wrapping this project up.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#31
April 17, 2019 at 10:27:19
To be more direct:

Have the VBA code alternate colors by every other month. Preferably switching between two different shades of green. Applied only to column A that contains the date.


Reply ↓  Report •

#32
April 17, 2019 at 13:10:55
✔ Best Answer
Try this version.

Additions included:

1 - Code to Password protect Summary Sheet (commented out at this point, just for ease of use.)
2 - Code to alternate the fill color of every other month using 2 shades of Green.


Sub CreateSummary()

Dim lstSrcRw As Long
Dim nxtDstRw As Long
Dim fPath As String
Dim fName As String
Dim dateRw As Long
Dim dateColor As Integer
Dim colorToggle As Integer


'Don't let the user see what is happening
     Application.ScreenUpdating = False

'Add a new sheet, give it a name
    Sheets.Add After:=Sheets(Sheets.Count)
    ActiveSheet.Name = "Summary Sheet"
    
'Copy data from "Outstanding Orders"
    Sheets("Outstanding Orders").Columns("A:D").Copy _
         Sheets("Summary Sheet").Range("A1")
         
'Copy data from "Closed Orders"
'First determine how many rows to copy and where to put it
    lstSrcRw = Sheets("Closed Orders").UsedRange.Rows.Count
    nxtDstRw = Sheets("Summary Sheet").Range("A" & Rows.Count).End(xlUp).Row + 1

    Sheets("Closed Orders").Range("A2:D" & lstSrcRw).Copy _
         Sheets("Summary Sheet").Range("A" & nxtDstRw)
         
'Sort the Summary Sheet
    ActiveWorkbook.Worksheets("Summary Sheet").Sort.SortFields.Add Key:=Range( _
        "A2:A" & Rows.Count), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Summary Sheet").Sort
        .SetRange Range("A1:D" & Rows.Count)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .Apply
    End With
    
'Delete everything after row 31
    Rows("32:" & Rows.Count).Delete Shift:=xlUp
    
'Move the Summary Sheet to its own Workbook
    Sheets("Summary Sheet").Move

'Start "Color Dates By Month" Section

'Clear Alternate Row Coloring For Column A
  Columns("A:A").FormatConditions.Delete
  
'Swap Green fill on every other month

'Set initial Green
  dateColor = 4
  
'Value to set alternate Green
  colorToggle = 39
  
'If consecutive cells contain the same month, fill with same Grenn
    Cells(2, 1).Interior.ColorIndex = dateColor
        For dateRw = 3 To 31
          If Month(Cells(dateRw, 1)) = Month(Cells(dateRw - 1, 1)) Then
                   Cells(dateRw, 1).Interior.ColorIndex = dateColor
                   
'If month has changed, toggle the Greens and fill cell
          Else
             dateColor = dateColor + colorToggle
             colorToggle = -colorToggle
                   Cells(dateRw, 1).Interior.ColorIndex = dateColor
          End If
        Next
'End "Color Dates By Month" Section
    
'Protect sheet with password
    'ActiveSheet.Protect Password:="QQQQ"
    
'Create Path and File name variables
      fPath = "You Need A Path Name Here "
      fName = "Summary Sheet.xlsx"
      
'Save options
'*** This will open the SaveAs Dialog Box to your default Save As folder until
'*** you fill in the fPath variable information
  
' fSaveName = Application.GetSaveAsFilename(fPath & fName)

'*** This will actually save the file without asking. You'll still to
'*** fill in a proper path.

'   Application.DisplayAlerts = False
'    ActiveWorkbook.SaveAs Filename:= _
'        fPath & fName _
'   Application.DisplayAlerts = True

'*** Close the Summary Sheet workbook
'   ActiveWindow.Close

End Sub

message edited by DerbyDad03


Reply ↓  Report •

#33
April 17, 2019 at 13:23:07
As a reminder, you should eventually password protect and hide the VBA code. If not, the code and the passwords for the Master sheets will be be accessible to anyone that has access to the "Dashboard" workbook.

DAGS 'hide vba project from viewing' for instructions on how to protect and hide the VBA code.


message edited by DerbyDad03


Reply ↓  Report •

#34
April 19, 2019 at 12:42:26
I apologize about the delay.

The code works as desired with the addition of the conditional formatting.

I just want to clarify something. I read everything through again and can't find a definitive answer. I removed the apostrophes in your save code and included a path and file name, but the function does not work. It prompts an error message. If these codes are complete, I may simply be inputting the path wrong, in which case I'll need a little more clarification. Or is the code still just an example and not fully complete?

Once the save function is complete and working, we should be done with this project.

Again, I greatly appreciate your time and assistance.


Reply ↓  Report •

#35
April 19, 2019 at 14:19:31
You may recall that I mentioned something about not being able to see your workbook from where I'm sitting.

Telling me that something "does not work" and "It prompts an error" doesn't give me anything work with. I have no idea what you changed with the code, what your path string looks like, what you uncommented, etc. I also don't know what error you are seeing. It could be an error because of the code (e.g. a syntax error) or it could be an error thrown up by Excel because of the save process.

There is nothing I can do without some specifics.

message edited by DerbyDad03


Reply ↓  Report •

#36
April 24, 2019 at 08:39:14
This is what I am using. I've replaced the username with [username] when posting this. The code prompts a "Compile error: Syntax error" message and highlights the "fPath & fName..." line.


'Create Path and File name variables
      fPath = "\\SBBV-FS1\Users\[username]\Desktop"
      fName = "NewHireSummary.xlsx"
      
'*** This will actually save the file without asking. You'll still need to
'*** fill in a proper path.

   Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs (NewHireSummary.xlsx)
        fPath & fName \\SBBV-FS1\Users\[username]\Desktop\NewHireSummary.xlsx
   Application.DisplayAlerts = True


Reply ↓  Report •

#37
April 24, 2019 at 09:44:35
You are mixing Text strings with variables.

These 2 instructions set the variables fPath and fName so that they contain the Text strings for your Path and Filename:

'Create Path and File name variables
      fPath = "\\SBBV-FS1\Users\[username]\Desktop"
      fName = "NewHireSummary.xlsx"

Once the variables have been set, you don't need the Text strings anymore. The text strings are stored inside the variables. Therefore, to pass the Path and Filename to the SaveAs instruction, all you need is:

ActiveWorkbook.SaveAs fPath & fName

The & (Concatenation operator) will combine the 2 Text strings that are stored within the variables into 1 Text string for the SaveAs command to use:

 ActiveWorkbook.SaveAs fPath & fName

will resolve to:

ActiveWorkbook.SaveAs "\\SBBV-FS1\Users\[username]\DesktopNewHireSummary.xlsx"

However, this still won't work because you are missing the backslash before the filename. The easiest way to fix this is to add the backslash to the fPath string:

fPath = "\\SBBV-FS1\Users\[username]\Desktop\"

Now when fPath and fName are concatenated, a properly formatted Text string will be pass to the SaveAs command.

Another option is to eliminate the variables and just hardcode the full Path and Filename string into the SaveAs command

ActiveWorkbook.SaveAs "\\SBBV-FS1\Users\[username]\Desktop\NewHireSummary.xlsx"

You will notice that I put quotes around the entire string. Any Text string used in VBA must have quotes around it so that VBA recognizes it as a Text string. If VBA doesn't see the quotes, it will (in most cases) assume that the text is a variable.

thisText is seen as a VBA variable
"thisText" is seen as a Text string

Also be aware that the Application.DisplayAlerts = False instruction is going to suppress the warning that the file already exists. The code is going to overwrite the file without asking. If you want to be warned before the file is overwritten, then eliminate the Application.DisplayAlerts instructions.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#38
April 24, 2019 at 12:52:45
Fixed and works perfectly. I'm going to implement this starting next month and I anticipate it will make things a lot easier.

Thank you for all of your help with this document.


Reply ↓  Report •

#39
April 24, 2019 at 14:14:22
This is worth repeating:

As a reminder, you should eventually password protect and hide the VBA code. If not, the code and the passwords for the Master sheets will be be accessible to anyone that has access to the "Dashboard" workbook.

DAGS 'hide vba project from viewing' for instructions on how to protect and hide the VBA code.


message edited by DerbyDad03


Reply ↓  Report •

#40
April 25, 2019 at 08:15:24
I did protect the VBA sheet as recommended. Thank you.

Reply ↓  Report •

Ask Question