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 email@example.com Thanks!
let us know the Specific sentence.
HI, Can you share your VBA code? l Thanks!
I have total 20 columns in sheet1,
First – 8 columns are – region, Dept, leader, manager, product,subprodcut, employee name, employee type,
12 columns are 12 months (Jan – Dec)
I need the userform – where user can select any of the above first – 8 columns (single or multiple),
Once the list item selected, only those columns should paste in sheet2 along with 12 months data,
Need solution very badly 🙁
Here is the solution for your query to get columns data from Sheet1 to Sheet2 based on list box selection.
Here are the instructions.
Private Sub CommandButton1_Click()
Dim iCnt As Integer
Dim MyHdr() As String, count As Integer, lastRow As Integer, destCol As Integer
count = 0: destCol = 1
For iCnt = 0 To Me.ListBox1.ListCount - 1
If Me.ListBox1.Selected(iCnt) = True Then
ReDim Preserve MyHdr(count)
MyHdr(count) = ListBox1.List(iCnt)
count = count + 1
'Find last row in Sheet1
lastRow = Sheet1.Cells.SpecialCells(xlLastCell).Row
For i = LBound(MyHdr) To UBound(MyHdr)
shdr = MyHdr(i)
For j = 1 To 8
If Sheet1.Cells(1, j) = shdr Then
Sheet1.Range(Sheet1.Cells(1, j), Sheet1.Cells(lastRow, j)).Copy Destination:=Sheet2.Cells(1, destCol)
destCol = destCol + 1
'Move Jan to Dec Data to Sheet2
Sheet1.Range(Sheet1.Cells(1, 9), Sheet1.Cells(lastRow, 20)).Copy Destination:=Sheet2.Cells(1, destCol)
Private Sub UserForm_Initialize()
For i = 1 To 8
ListBox1.AddItem Sheet1.Cells(1, i)
Hope it clarifies your query.
No words, just Awesome!!!!! exactly what i wanted , saved me a lot of time, thank you sooo much,
really great!!, worth joining this forum :):):)
I need one small extension here, apologies for that –
Can I get columns pattern/series based on the user’s selection – Ex –
1st instance – user select Dept first and then manger – here col 1 is Dept and col 2 is manager
2nd instance – user select manger first and then Dept ( vice -versa) here col 1 is manager and col 2 is Dept
and goes on
thanks in advance,
Here is some useful information about the ListBox.
Once the columns pasted in sheet2, i need those columns to subtotal and show only total rows (similar to subtotal level 2 ), please help,, this will end my complete project.