This is the answer ( I had to answer my own question) Sub CopySheet_NewWorkbook() Sheets(Array("Sheet1", "Shhet2")).Copy End Sub
Please check the following link: http://vbaf1.com/q/a/compile-all-the-3-excel-sheet-data-in-one-sheet-in-new-excel-file/ Here is the sample project: http://analysistabs.com/vba-code/excel-projects/consolidate-data-from-multiple-worksheets-row/ Also, please check the other topic answered...
I think you should add a line to sort the first column first before you group. Try sorting the column...
Thanks for the reply and ur time :) I found the answer to this :)
Hi Amoljagdale, Please try to answer our forum questions. You will be having different type of questions. Its very good...
Hi, Please share your sample workbook at firstname.lastname@example.org Thanks!
let us know the Specific sentence.
HI, Can you share your VBA code? l Thanks!
I want the user to be able to sort a field by choosing the column title from a combobox. I have a vertical list of the column titles and the associated number (0-16) in an array that I can access with vlookup and can access the header with hlookup, but can’t get the column designation into the sortkey of the macro
Here is the set of instructions to sort data based on header selection from the combobox using VBA
1. Go to VBA editior by clicking on Alt+F11.
2. Insert Label , Combo box and command button.
3.Change the cation of the label to “Select Header to Sort Data”.
4.Change the caption of the CommandButton to “Sort Data”.
5. Here is the design of the screen shot for your reference.
6. Double click on the Userform and add the following code.
Private Sub UserForm_Initialize()
'Populate Color combo box.
Dim rngHdr As Range
Dim ws As Worksheet
Dim HdrRange As Range
Set HdrRange = Range("A1:Z1")
Set ws = Sheets("Sheet1")
For Each rngHdr In HdrRange
7. Double click on the command button and add the following code.
Private Sub CommandButton1_Click()
'Here Range("A1:E20") is target range to sort
KeyToSort = ComboBox1.Text
Sheets("Sheet1").Range("A1:F20").Sort Key1:=KeyToSort, Header:=xlYes
8. Go to worksheet, Insert shape from the illustration group.
9. Right click on the UserForm, click on edit text as “Show Userform”.
Here is the screen shot for your reference.
11. Right click on the shape, click on assign macro.
12. Click on ‘New’ from Assign Macro window and add the following code.
13. Now, click on the ‘Show userform’ from the Worksheet.
14. Select header from the ComboBox and click on Sort data .
15. You can see the output on the worksheet.
Example: I want to sort data based on 2nd column. I have selected Hdr2 from the ComboBox and clicked on Sort Data button.
Now, you can see the following output. data has sorted based on 2nd column.
Hope, It clarifies your query.
You can find related information in the specified link for more information.
VBA Sort Data with headers Excel Example Macro Code
Thanks for your response. I’m new to VBA but not to Excel so in the interim I came across the following solution. (I should add that the data populating the table is based on formulae from other sheets in the book and so is protected and the macro unprotects and reprotects the worksheet).
I used a combo box to list the column headers vertically and gave each its corresponding number and its cell address in adjacent cells. Then used the Combo box output cell to define the cell address via a vlookup function. A button then starts the macro which reads that address as a variable and inserts as the rangekey. I like the idea of a single box instead of a combo box and then a button and I ma going to experiment with your code to help me learn more about VBA.