Create Excel Worksheets based on column value..

in Excel...I have a workbook called ClientReport...

This report has a Microsoft Query as a Data Source...

One of the column is called Yr and the other is Mo...(with many other columns)

What I am looking for is the ability to run a "Refresh All" and be able to have New Excel Worksheets created from the Mo (month) column and Enter in the Yr...

The report that I am currently running has 1000's of rows per month, per year.. I need a prompt to ask for Year and then display in worksheets January for 1, February for 2, March for 3... etx... for however many months are listed per the year entered.

Hopefully this makes sense
Michael KatzAsked:
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.

Martin LissOlder than dirtCommented:
Can you supply a sample workbook? It doesn't need the query, just the results.
0
Michael KatzAuthor Commented:
I attached file..

Hopefully it has January, February, March as a few worksheets.. Thank you
ExcelWithNameTabs.xls
0
Martin LissOlder than dirtCommented:
Run the RefreshAll macro that you'll find in Module1. I renamed and left the old monthly sheets in place.

A question. I'm giving the sheets a name that ends with a hard-coded " - Table1". Can that value be different? Can I get the value from the "Data Extract" sheet?
28689645.xlsm
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

Michael KatzAuthor Commented:
Hi Martin,

Not sure why there wouldb be a Table1...no needed..The value is from the Mo Column... How do I run the Macro..cant find it
0
Martin LissOlder than dirtCommented:
To find the macro...

1.    Press Alt+f11 which will take you do the Visual Basic Environment (VBE)
2.    Double-click 'Module1' that you'll find in the 'Project - VBAProject' window
3.    With your mouse, select any line in the RefreshAll macro
4.    Press f5 to run the macro

...but I've attached a new version of the workbook where you don't have to do that. In this workbook you can just press Ctrl+Shift+R (in other words while holding down both the Control and Shift keys, press the "R" key). Note that in this version of the workbook the new sheets will be named with just the month name.
28689645a.xlsm
0
Michael KatzAuthor Commented:
I have tried it in my actual form…I had to replace the C which was the year in my sample to B…and D to C ..

It asks for Year.. Which I enter…and nothing happens…any ideas??
0
Martin LissOlder than dirtCommented:
Please post the macro with your changes.
0
Michael KatzAuthor Commented:
HI Martin,

Thanks for your reply...I appreciate it...

I am thinking that my data extraction to populate the Excel spreadsheet is too big...Is there any way to add in the data extraction piece to this as well??  in other words can the year also be the parameter where by the information populates the Excel spreadsheet??    If that is possible it may be best to add another parameter that looks at Column "E" as well which is a 4 character field (which may be blank)

Sorry just trying to give you as much info as possible.... The Data extraction is using an ODBC Connection with a connection string

DSN=ClientReporting;UID=bvreporting;PWD=via88Brand;APP=Microsoft Office 2010;WSID=EWHSERVER1993;DATABASE=BVSysproReporting

in the command text of the Data > Properties I am using the following as a  select  statement

SELECT     Branch as Br, GlYear as Yr, GlPeriod as Mo, CustomerNumber as CustNo, PGM, Right(Invoice,6) as InvNo,
InvoiceDate as InvDate, ShipDate, Right(SalesOrder,5) as OrderNo,
WebNo, Typ, ShipToContact as ShipName, ShipToCompany as ShipCompany, ShipToAddress as ShipAddress,
                      ShipToCity as ShipCity, ShipToSt as ShipSt,ShipToPostalCode as ShipZip, ShipToCountry as SCtry, EmailDomain as Domain,
MerchandiseValue as ProdAmt, TaxValue, FreightValue as FrtAmt, InvTotalAmt as TotalAmt, CustomerPoNumber PONo, GCID,
                      ProductClass, StockCode as SKU, Description as ItemName, LineQtyInvoiced as Qty, LineNetSalesValue as ExtPrice
FROM         BV_Rpt_Invoiced

Below is the Macro

Option Explicit

Sub RefreshAll()
Dim lngLastRow As Long
Dim lngRow As Long
Dim strYear As String
Dim strMonth As String
Dim rngYear As Range
Dim wsSource As Worksheet
Dim wsMonth As Worksheet
Dim lngNewRow As Long

Application.ScreenUpdating = False
strYear = Trim(InputBox("Please enter the 4-digit year"))
If strYear = "" Then
    MsgBox "No year selected"
    Exit Sub
End If

Set wsSource = Sheet1
With wsSource
    Set rngYear = .Cells.Find(What:=strYear, LookIn:=xlFormulas, LookAt _
        :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False)
    If rngYear Is Nothing Then
        MsgBox "No records for year " & strYear & " exist"
        Exit Sub
    End If
   
    lngLastRow = .UsedRange.Rows.Count
    For lngRow = 3 To lngLastRow
        If .Cells(lngRow, "B") <> strYear Then
            Exit Sub
        End If
        If .Cells(lngRow, "C") <> strMonth Then
            Worksheets.Add
            Set wsMonth = ActiveSheet
            wsMonth.Move After:=Worksheets(Worksheets.Count)
            wsMonth.Name = MonthName(wsSource.Cells(lngRow, "C")) ' & " - Table 1"
            strMonth = wsSource.Cells(lngRow, "C")
            wsSource.Rows(1).Copy Destination:=wsMonth.Rows(1)
            wsSource.Rows(2).Copy Destination:=wsMonth.Rows(2)
            lngNewRow = 3
        End If
        wsSource.Rows(lngRow).Copy Destination:=wsMonth.Rows(lngNewRow)
        lngNewRow = lngNewRow + 1
    Next
End With

Application.ScreenUpdating = True
End Sub
0
Martin LissOlder than dirtCommented:
I am thinking that my data extraction to populate the Excel spreadsheet is too big
I'm not sure what you mean. Not that it should matter but do you mean that there are a large number of rows?

Is there any way to add in the data extraction piece to this as well??
Yes I'm sure it can be done but it's not in my area of expertise so you'd need to find someone else to help you with that.

If that is possible it may be best to add another parameter that looks at Column "E" as well which is a 4 character field (which may be blank)
Do you mean the "PGM" field? If so what does that have to do with the year?

But ignoring all that I'd like to first try to figure out why nothing happens after you enter the year. I looked at your code and assuming that column "B" is the year and that month is column "C", your code looks correct, so if you want to let's do a little debugging.

First go to the macro and place a breakpoint on the Set wsSource = Sheet1 line. You do that by clicking the mouse in the margin at the left of that line. (The breakpoint will show up as a red dot and the backcolor of the line will be made red).
Press f5 to start the macro and after you enter the year, the code should stop at the breakpoint. Press f8. That will take you to the next line to be executed. Press it a few times and stop at the For lngRow = 3 To lngLastRow line. At that point move the cursor over "lngLastRow" in the previous line and tell me what the value is.

For more information on debugging,  see my article on the subject.
0
Martin LissOlder than dirtCommented:
I think I know what's wrong. Go to the Visual Basic Environment and look at the DataExtract sheet. Does it say that it's "Sheet1"?

VBE
I assume it doesn't but you have another sheet that does say "Sheet1". In that case change the "Sheet1" part of the Set wsSource = Sheet1 line so that it matches the sheet number for the DataExtract sheet. If that value changes each time you import new data, but the name for the sheet is always the same, then change the line to Set wsSource = Sheets("name of the sheet") where "name of the sheet" is something like "DataExtract - Table 1".
0
Michael KatzAuthor Commented:
Hi Martin... I must be an idiot..



I am looking at the code ... and Excel...not showing after I click Run at top
VBCodeWindow.JPG
VBCodeExcel.JPG
0
Martin LissOlder than dirtCommented:
Did you try the debugging suggestions I made it post ID: 40832635?

Are you using Excel 2010?

I could see much better what is happening if I had your actual workbook. If i's a matter of sensitive data, all I really need are the values in columns B & C and you could change or clear the rest.
0
Michael KatzAuthor Commented:
0
Martin LissOlder than dirtCommented:
It's hard to help you if you don't answer my questions, so I'll ask them again.

Did you try the debugging suggestions I made it post ID: 40832635?

Are you using Excel 2010?
But I do thank you for posting your recent workbook. It pointed out that a few things needed to be changed including that the source sheet needed to be sorted so that the records for each month were sequential and so I added code in the macro to do that. I also found that because you have >90,000 rows that the code takes a while to run. To help you see the progress of the macro I added code that displays a count in the lower lefthand corder of the workbook.

The attached is your workbook after I ran the modified code. The 'January' to 'June' sheets show the results.
28689645b.xlsm
0
Michael KatzAuthor Commented:
I am reviewing it .. Sorry for my lack of response.. been sick as hell…I guess a bit of "burn out"
0
Michael KatzAuthor Commented:
I am thinking I should give up…I can't get it to work…I refreshed the backend data…Ran the report.. and it has 300000 rows…I run the macro…and nothing changes…doesn't do anything
0
Martin LissOlder than dirtCommented:
Please post the workbook where you refreshed the backend data.
0
Michael KatzAuthor Commented:
0
Martin LissOlder than dirtCommented:
When I ran the RefreshAll macro it asked me for a year and I entered 2013 and it created a sheet for December (of 2013). If you like I can remove the portion of the code that asks for the year and produce sheets for every month but we'd have to add the year to the name of the new sheet if there were records for the same month name in more than one year.

Are you using Excel 2010?
0
Michael KatzAuthor Commented:
When i run it for 2013 i get December as well…when i delete the tab for December re run the macro RefreshAll and enter 2014.. it doesn't produce any tabs for any months…

I guess remove the year and see what happens…

yes for excel 2010
0
Martin LissOlder than dirtCommented:
I realize now what's wrong and I'll have a fix in the morning.
0
Michael KatzAuthor Commented:
ok.. thanks for all your help.
0
Martin LissOlder than dirtCommented:
This works and it much faster.

Sub RefreshAll()
Dim lngLastRow As Long
Dim lngRow As Long
Dim strYear As String
Dim strMonth As String
Dim rngYear As Range
Dim wsSource As Worksheet
Dim wsMonth As Worksheet
Dim lngStartRow As Long

Application.ScreenUpdating = False
strYear = Trim(InputBox("Please enter the 4-digit year"))
If strYear = "" Then
    MsgBox "No year selected"
    Exit Sub
End If

Set wsSource = Sheet1
With wsSource
    Set rngYear = .Cells.Find(What:=strYear, LookIn:=xlFormulas, LookAt _
        :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False)
    If rngYear Is Nothing Then
        MsgBox "No records for year " & strYear & " exist"
        Exit Sub
    End If
        
    'Sort on year and month
    .ListObjects("Table_Query_from_ClientReporting").Sort.SortFields.Clear
    .ListObjects("Table_Query_from_ClientReporting").Sort.SortFields.Add Key:=Range( _
        "Table_Query_from_ClientReporting[Yr]"), SortOn:=xlSortOnValues, Order:= _
        xlAscending, DataOption:=xlSortNormal
    .ListObjects("Table_Query_from_ClientReporting").Sort.SortFields.Add Key:=Range( _
        "Table_Query_from_ClientReporting[Mo]"), SortOn:=xlSortOnValues, Order:= _
        xlAscending, DataOption:=xlSortNormal
    With wsSource.ListObjects( _
        "Table_Query_from_ClientReporting").Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

    lngLastRow = .UsedRange.Rows.Count + 1
    lngStartRow = rngYear.Row
    strMonth = .Cells(rngYear.Row, "C")
    
    For lngRow = rngYear.Row To lngLastRow
        If .Cells(lngRow, "C") <> strMonth Then
            Worksheets.Add
            Set wsMonth = ActiveSheet
            wsMonth.Move After:=Worksheets(Worksheets.Count)
            wsMonth.Name = MonthName(.Cells(lngRow - 1, "C"))
            strMonth = .Cells(lngRow, "C")
            .Rows(1).Copy Destination:=wsMonth.Rows(1)
            .Rows(lngStartRow & ":" & lngRow - 1).Copy Destination:=wsMonth.Rows(2)
            lngStartRow = lngRow
        End If
        If .Cells(lngRow, "B") <> strYear Then
            Exit For
        End If
    Next
End With

Application.ScreenUpdating = True
End Sub

Open in new window

0

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
Martin LissOlder than dirtCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0
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.