Hello,
I am trying to create a button that generates 3 reports based on one condition. Please see the attached file.
There are 3 tables to be generated, namely, worksheet A, B and C.
In worksheet A, there is a master button, "Generate quarter report". I would like to first select a specific quarter(Q 1, Q 2, Q 3 or Q 4) from the dropdown list in cell D2, and hit that master button. The purpose of the button is to filter all of the records from three worksheets based on that quarter selection and output the 3 worksheet recordsets to a new workbook.
Additionally, I would like the new workbook to be able to prevent users from modifying records in the output files by default. (simply just want to have columns locked)
Is this possible?
Thank you
Your request is not very clear. All i see in tabs A B and C is Q1, Q2, etc & P1, P2 etc. There is no data available.
Cheers,
Arlette
If I helped, Don't forget to add to my reputation (click on the star below the post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
Use code tags when posting your VBA code: [code] Your code here [/code]
Oops sorry.Originally Posted by arlu1201
Please see the attached with more fields.
So basically, when the button is clicked, I would like to have Excel filter on all worksheets based on either the period selection OR the quarter selection, depending on what user's needs are, and then output the filtered recordsets to a new workbook.
The generated files in the new workbook just need to contain all 4 original columns, so data in this context means everything you see from Col A to Col D.
Please see the attached file
Thanks
Hi, could you respond to my subsequent post when you get a chance? Is it possible?
Thank you
Hey Lifeseeker,
I guess many were away for the christmas vacations so your post remain un-answered. Do you still need a resolution?
Cheers,
Arlette
If I helped, Don't forget to add to my reputation (click on the star below the post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
Use code tags when posting your VBA code: [code] Your code here [/code]
Hi,
Yes please. Happy New Year first!
What I would like:
To have a button that when clicked, filters all records based specified quarter and export the filtered recordset to a new workbook with same properties. (for example, if columns are locked, i want the exported table to have the same property)
Is this clear?
Thank you
Just one last question - I understand that there will be 3 sheets with data- A, B and C which needs to be filtered. Should the records be exported to 3 different worksheets of the new workbook? Or should it be exported to the same worksheet of the new workbook?
Cheers,
Arlette
If I helped, Don't forget to add to my reputation (click on the star below the post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
Use code tags when posting your VBA code: [code] Your code here [/code]
Hi,
Actually....would it be possible to export to 3 different workbooks where each new workbook just contains one table? So workbookA will store A, new workbookB will store B, etc.
If this is not possible, then 3 different worksheets of the new workbook please.
Thank you
Both are possible. You just tell me which will be your preference -
A - Export to different workbooks
B - Export to different worksheets
Cheers,
Arlette
If I helped, Don't forget to add to my reputation (click on the star below the post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
Use code tags when posting your VBA code: [code] Your code here [/code]
A please, different workbooks.
Thank you
You can use this code. It creates 3 new workbooks with the required headers and copies the data over. Sheet1 data from the main file goes to workbook by name of Bk1, Sheet2 to Bk2, etc. This is automated and if you need to change it to something specific let me know.Option Explicit Dim i As Long Dim j As Long Dim lrow As Long Sub qly_report() For i = 1 To 3 Workbooks.Add ActiveWorkbook.SaveAs "Bk" & i ThisWorkbook.Worksheets("Sheet1").Range("A1:D1").Copy Workbooks("Bk" & i).Worksheets(1).Range("A1") Next i For i = 1 To ThisWorkbook.Worksheets.Count With ThisWorkbook.Worksheets(i) If .Name = "Sheet1" Or .Name = "Sheet2" Or .Name = "Sheet3" Then lrow = .Range("A" & Rows.Count).End(xlUp).Row For j = 2 To lrow If .Range("A" & j).Value = ThisWorkbook.Worksheets("Master").Range("F3").Value Then If .Range("B" & j).Value = ThisWorkbook.Worksheets("Master").Range("G3").Value Then .Range("A" & j & ":D" & j).Copy Workbooks("Bk" & Right(.Name, 1)).Worksheets(1).Range("A" & Rows.Count).End(xlUp).Offset(1, 0) End If End If Next j End If End With Next i End Sub
Cheers,
Arlette
If I helped, Don't forget to add to my reputation (click on the star below the post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
Use code tags when posting your VBA code: [code] Your code here [/code]
Hi,
Thank you for doing this.
I am getting an error: "subscript out of range" on
i thought I didn't have to activate this workbook for it to recognize?ThisWorkbook.Worksheets("Sheet1").Range("A1:D1").Copy Workbooks("Bk" & i).Worksheets(1).Range("A1")
i am also attaching the file.
I actually didnt use this file that you provided. Since you said that the data belonged to 3 different worksheets, i had copied the data in sections A, B and C in your Master file to another file with 3 worksheets. Those worksheets were named, Sheet1, Sheet2 and Sheet3.
If you will be using this file, the code will need to be changed to some extent, as there wont be 3 sheets but 1 having all the 3 datasets in it.
The line that gave you the error is the one that was creating the header in the new workbook. If you do not want to use that code line, we can hard-code the headers asworkbooks("Bk" & i).worksheets(1).range("A1").value = "Quarter" workbooks("Bk" & i).worksheets(1).range("B1").value = "Period" workbooks("Bk" & i).worksheets(1).range("C1").value = "Status" workbooks("Bk" & i).worksheets(1).range("D1").value = "Counts"
Cheers,
Arlette
If I helped, Don't forget to add to my reputation (click on the star below the post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
Use code tags when posting your VBA code: [code] Your code here [/code]
Ah I see. I'm sorry for not being 100% clear...
Would it be possible if all 3 recordsets are in 1 worksheet as the way it is(this file), but when I do the filter and export, I would like to have 3 recordsets( A B and C) be in 3 different workbooks as opposed to 3 different worksheets?
As you can see, all recordsets have exactly the same format, which would be easy.
So after the export is done, that recordset table A will be in workbook A, table B will be in workbook B, etc. I want like to keep them separate please.
Please see the attached.
Is anyone able to assist?
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks