RE: $$Excel-Macros$$ Query continuous copy paste
Try this : Sub Transformdata() Dim rngCEll As Range Dim rngRange As Range Dim wksOutput As Worksheet Set wksOutput = ThisWorkbook.Worksheets.Add Set rngRange = ThisWorkbook.Worksheets(Query).Rows(1) For Each rngCEll In rngRange.Cells Application.ScreenUpdating = False If rngCEll.Value = Then Exit For If rngCEll.Value = Name Then rngCEll.Resize(rngCEll.End(xlDown).Row, 2).Copy wksOutput.Range(A WorksheetFunction.CountA(wksOutput.Range(A:A)) + 1) End If Next With wksOutput .UsedRange.WrapText = False .Rows.AutoFit .Columns.AutoFit .Range($A$1:$B$114).AutoFilter Field:=1, Criteria1:=Name .Cells.SpecialCells(xlCellTypeVisible).EntireRow.Delete .Range(A1).EntireRow.Insert .Range(A1).Value = Name .Range(B1).Value = No End With Application.ScreenUpdating = False End Sub -Original Message- From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of Mukesh Kumar Sent: Jan/Tue/2012 01:10 To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ Query continuous copy paste Dear Sir, Please tell me the solution for the query given in the attached file. In it the data is distributed in the different columns. The solution is manually copy paste the data which is time consuming process. Please tell me the solution or formula to automate the process. Also please note that here two columns are having label i.e. Name No. There may be certain time the columns may having more than 2 or more labels. So please keep this situation too in mind. Thanks Regards, Mukesh Kumar -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com
Re: $$Excel-Macros$$ Query continuous copy paste
Thanks Noorain Ansari Ji for the solution but it works is working with this sheet only i.e if the column headings are having two same titles. If I have to transform three or four columns to first three or four columns then this macro will not work there. Please suggest any formula or user form according to variable number of columns titles. Waiting for the reply. Regards, Mukesh kumar On 1/31/12, NOORAIN ANSARI noorain.ans...@gmail.com wrote: Dear Mukesh, Please try it.. and see attached sheet. Sub mukesh() Dim i, c, j As Integer c = Application.WorksheetFunction.CountA(Sheet1.Range(1:1)) Sheet2.Range(A1:B1) = Array(Name, No) counter = 2 For j = 2 To c Step 2 For i = 2 To Sheet1.Cells(Rows.Count, j).End(xlUp).Row Sheet2.Cells(counter, 1).Value = Sheet1.Cells(i, j - 1) Sheet2.Cells(counter, 2).Value = Sheet1.Cells(i, j) counter = counter + 1 Next i Next j Sheet2.Columns.AutoFit Sheet2.Activate End Sub On Tue, Jan 31, 2012 at 1:10 PM, Mukesh Kumar mukeshka...@gmail.com wrote: Dear Sir, Please tell me the solution for the query given in the attached file. In it the data is distributed in the different columns. The solution is manually copy paste the data which is time consuming process. Please tell me the solution or formula to automate the process. Also please note that here two columns are having label i.e. Name No. There may be certain time the columns may having more than 2 or more labels. So please keep this situation too in mind. Thanks Regards, Mukesh Kumar -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- Thanks regards, Noorain Ansari *http://excelmacroworld.blogspot.com/*http://excelmacroworld.blogspot.com/ *http://noorain-ansari.blogspot.com/* http://noorain-ansari.blogspot.com/ -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com
Re: $$Excel-Macros$$ Query continuous copy paste
Dear Mukesh, Can you provide that worksheet, where macro is not running... Please share workbook with group.. On Tue, Jan 31, 2012 at 7:17 PM, Mukesh Kumar mukeshka...@gmail.com wrote: Thanks Noorain Ansari Ji for the solution but it works is working with this sheet only i.e if the column headings are having two same titles. If I have to transform three or four columns to first three or four columns then this macro will not work there. Please suggest any formula or user form according to variable number of columns titles. Waiting for the reply. Regards, Mukesh kumar On 1/31/12, NOORAIN ANSARI noorain.ans...@gmail.com wrote: Dear Mukesh, Please try it.. and see attached sheet. Sub mukesh() Dim i, c, j As Integer c = Application.WorksheetFunction.CountA(Sheet1.Range(1:1)) Sheet2.Range(A1:B1) = Array(Name, No) counter = 2 For j = 2 To c Step 2 For i = 2 To Sheet1.Cells(Rows.Count, j).End(xlUp).Row Sheet2.Cells(counter, 1).Value = Sheet1.Cells(i, j - 1) Sheet2.Cells(counter, 2).Value = Sheet1.Cells(i, j) counter = counter + 1 Next i Next j Sheet2.Columns.AutoFit Sheet2.Activate End Sub On Tue, Jan 31, 2012 at 1:10 PM, Mukesh Kumar mukeshka...@gmail.com wrote: Dear Sir, Please tell me the solution for the query given in the attached file. In it the data is distributed in the different columns. The solution is manually copy paste the data which is time consuming process. Please tell me the solution or formula to automate the process. Also please note that here two columns are having label i.e. Name No. There may be certain time the columns may having more than 2 or more labels. So please keep this situation too in mind. Thanks Regards, Mukesh Kumar -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- Thanks regards, Noorain Ansari *http://excelmacroworld.blogspot.com/* http://excelmacroworld.blogspot.com/ *http://noorain-ansari.blogspot.com/* http://noorain-ansari.blogspot.com/ -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- Thanks regards, Noorain Ansari *http://excelmacroworld.blogspot.com/*http://excelmacroworld.blogspot.com/ *http://noorain-ansari.blogspot.com/* http://noorain-ansari.blogspot.com/ -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem,
Re: $$Excel-Macros$$ Query continuous copy paste
Try Option Explicit Sub copycolumstorowsSAS() Dim i As Long For i = 1 To Cells(1, Columns.Count).End(xlToLeft).Column Step 2 Cells(2, i).Resize(6, 2).Copy Cells(Rows.Count, 1).End(xlUp).Offset(1) Next i End Sub Don Guillett SalesAid Software dguille...@gmail.com -Original Message- From: Mukesh Kumar Sent: Tuesday, January 31, 2012 1:40 AM To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ Query continuous copy paste Dear Sir, Please tell me the solution for the query given in the attached file. In it the data is distributed in the different columns. The solution is manually copy paste the data which is time consuming process. Please tell me the solution or formula to automate the process. Also please note that here two columns are having label i.e. Name No. There may be certain time the columns may having more than 2 or more labels. So please keep this situation too in mind. Thanks Regards, Mukesh Kumar -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com
Re: $$Excel-Macros$$ Query continuous copy paste
@ Don Guillett Dear sir, your macro after running giving wrong results, please check. @ Noorain Ansari Sir, macro after running showing error compile error. Sub or function not defined. Please note, i am having different workbooks in which some workbooks contains 2 columns similar, some 3 columns headings similar other workbooks =5 similar column headings. So for all my workbooks, can you guys give common macro code or formulas. Regards, Mukesh Kumar On Tue, Jan 31, 2012 at 8:59 PM, NOORAIN ANSARI noorain.ans...@gmail.comwrote: Dear Mukesh, Please see attached sheet... I hope your query has been resolved... On Tue, Jan 31, 2012 at 8:48 PM, Mukesh Kumar mukeshka...@gmail.comwrote: Dear Ansari ji, Macro is running fine if sheet is having set of two similar header columns i.e let say a b. Problem occurs when header columns are more than two sets i.e a, b c. You can see in the attached file. You can also consider my query better as consolidate columns on basis of Column Titles. Hope you understand my query. Regards, Mukesh Kumar On Tue, Jan 31, 2012 at 8:24 PM, NOORAIN ANSARI noorain.ans...@gmail.com wrote: Dear Mukesh, Can you provide that worksheet, where macro is not running... Please share workbook with group.. On Tue, Jan 31, 2012 at 7:17 PM, Mukesh Kumar mukeshka...@gmail.comwrote: Thanks Noorain Ansari Ji for the solution but it works is working with this sheet only i.e if the column headings are having two same titles. If I have to transform three or four columns to first three or four columns then this macro will not work there. Please suggest any formula or user form according to variable number of columns titles. Waiting for the reply. Regards, Mukesh kumar On 1/31/12, NOORAIN ANSARI noorain.ans...@gmail.com wrote: Dear Mukesh, Please try it.. and see attached sheet. Sub mukesh() Dim i, c, j As Integer c = Application.WorksheetFunction.CountA(Sheet1.Range(1:1)) Sheet2.Range(A1:B1) = Array(Name, No) counter = 2 For j = 2 To c Step 2 For i = 2 To Sheet1.Cells(Rows.Count, j).End(xlUp).Row Sheet2.Cells(counter, 1).Value = Sheet1.Cells(i, j - 1) Sheet2.Cells(counter, 2).Value = Sheet1.Cells(i, j) counter = counter + 1 Next i Next j Sheet2.Columns.AutoFit Sheet2.Activate End Sub On Tue, Jan 31, 2012 at 1:10 PM, Mukesh Kumar mukeshka...@gmail.com wrote: Dear Sir, Please tell me the solution for the query given in the attached file. In it the data is distributed in the different columns. The solution is manually copy paste the data which is time consuming process. Please tell me the solution or formula to automate the process. Also please note that here two columns are having label i.e. Name No. There may be certain time the columns may having more than 2 or more labels. So please keep this situation too in mind. Thanks Regards, Mukesh Kumar -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- Thanks regards, Noorain Ansari *http://excelmacroworld.blogspot.com/* http://excelmacroworld.blogspot.com/ *http://noorain-ansari.blogspot.com/* http://noorain-ansari.blogspot.com/ -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use
Re: $$Excel-Macros$$ Query continuous copy paste
Always nice to tell us the ENTIRE problem at the FIRST post. It appears that, if you give proper info, then the last offering by Noorain should work. Don Guillett SalesAid Software dguille...@gmail.com From: Mukesh Kumar Sent: Tuesday, January 31, 2012 9:50 AM To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ Query continuous copy paste @ Don Guillett Dear sir, your macro after running giving wrong results, please check. @ Noorain Ansari Sir, macro after running showing error compile error. Sub or function not defined. Please note, i am having different workbooks in which some workbooks contains 2 columns similar, some 3 columns headings similar other workbooks =5 similar column headings. So for all my workbooks, can you guys give common macro code or formulas. Regards, Mukesh Kumar On Tue, Jan 31, 2012 at 8:59 PM, NOORAIN ANSARI noorain.ans...@gmail.com wrote: Dear Mukesh, Please see attached sheet... I hope your query has been resolved... On Tue, Jan 31, 2012 at 8:48 PM, Mukesh Kumar mukeshka...@gmail.com wrote: Dear Ansari ji, Macro is running fine if sheet is having set of two similar header columns i.e let say a b. Problem occurs when header columns are more than two sets i.e a, b c. You can see in the attached file. You can also consider my query better as consolidate columns on basis of Column Titles. Hope you understand my query. Regards, Mukesh Kumar On Tue, Jan 31, 2012 at 8:24 PM, NOORAIN ANSARI noorain.ans...@gmail.com wrote: Dear Mukesh, Can you provide that worksheet, where macro is not running... Please share workbook with group.. On Tue, Jan 31, 2012 at 7:17 PM, Mukesh Kumar mukeshka...@gmail.com wrote: Thanks Noorain Ansari Ji for the solution but it works is working with this sheet only i.e if the column headings are having two same titles. If I have to transform three or four columns to first three or four columns then this macro will not work there. Please suggest any formula or user form according to variable number of columns titles. Waiting for the reply. Regards, Mukesh kumar On 1/31/12, NOORAIN ANSARI noorain.ans...@gmail.com wrote: Dear Mukesh, Please try it.. and see attached sheet. Sub mukesh() Dim i, c, j As Integer c = Application.WorksheetFunction.CountA(Sheet1.Range(1:1)) Sheet2.Range(A1:B1) = Array(Name, No) counter = 2 For j = 2 To c Step 2 For i = 2 To Sheet1.Cells(Rows.Count, j).End(xlUp).Row Sheet2.Cells(counter, 1).Value = Sheet1.Cells(i, j - 1) Sheet2.Cells(counter, 2).Value = Sheet1.Cells(i, j) counter = counter + 1 Next i Next j Sheet2.Columns.AutoFit Sheet2.Activate End Sub On Tue, Jan 31, 2012 at 1:10 PM, Mukesh Kumar mukeshka...@gmail.com wrote: Dear Sir, Please tell me the solution for the query given in the attached file. In it the data is distributed in the different columns. The solution is manually copy paste the data which is time consuming process. Please tell me the solution or formula to automate the process. Also please note that here two columns are having label i.e. Name No. There may be certain time the columns may having more than 2 or more labels. So please keep this situation too in mind. Thanks Regards, Mukesh Kumar -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- Thanks regards, Noorain Ansari *http://excelmacroworld.blogspot.com/*http://excelmacroworld.blogspot.com/ *http://noorain-ansari.blogspot.com/* http://noorain-ansari.blogspot.com
Re: $$Excel-Macros$$ Query continuous copy paste
Sub kTest() Dim x, ka, k(), i As Long, n As Long, c As Long, j As Long, p As Long With Worksheets(Query) ka = .Range(a1).CurrentRegion End With With CreateObject(scripting.dictionary) .comparemode = 1 For c = 1 To UBound(ka, 2) If Not .exists(ka(1, c)) Then j = j + 1 .Add ka(1, c), j End If Next ReDim k(1 To UBound(ka, 1) * UBound(ka, 2), 1 To j) For c = 1 To UBound(ka, 2) Step j For i = 2 To UBound(ka, 1) n = n + 1 For p = 0 To j - 1 k(n, .Item(ka(1, c + p))) = ka(i, c + p) Next Next Next x = .keys End With If n Then With Worksheets(Sheet1).Range(a1) .Parent.UsedRange.ClearContents .Range(a1).Resize(, j) = x .Range(a2).Resize(n, j) = k .Parent.UsedRange.Columns.AutoFit End With End If End Sub Kris -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com
Re: $$Excel-Macros$$ Query continuous copy paste
Dear Kris, Following error is coming after macro execution Run time error '9': Subscript out of range. Please correct it. Regards, Mukesh kumar On Tue, Jan 31, 2012 at 10:00 PM, Kris krishnak...@gmail.com wrote: Sub kTest() Dim x, ka, k(), i As Long, n As Long, c As Long, j As Long, p As Long With Worksheets(Query) ka = .Range(a1).CurrentRegion End With With CreateObject(scripting.dictionary) .comparemode = 1 For c = 1 To UBound(ka, 2) If Not .exists(ka(1, c)) Then j = j + 1 .Add ka(1, c), j End If Next ReDim k(1 To UBound(ka, 1) * UBound(ka, 2), 1 To j) For c = 1 To UBound(ka, 2) Step j For i = 2 To UBound(ka, 1) n = n + 1 For p = 0 To j - 1 k(n, .Item(ka(1, c + p))) = ka(i, c + p) Next Next Next x = .keys End With If n Then With Worksheets(Sheet1).Range(a1) .Parent.UsedRange.ClearContents .Range(a1).Resize(, j) = x .Range(a2).Resize(n, j) = k .Parent.UsedRange.Columns.AutoFit End With End If End Sub Kris -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com
Re: $$Excel-Macros$$ Query continuous copy paste
Sub Consolidator() Dim rngSource As Range Dim lngCols As Long, lngRows As Long Set rngSource = Range(A1).CurrentRegion lngRows = rngSource.CurrentRegion.Rows.Count - 1 For lngCols = 3 To rngSource.Columns.Count - 1 Step 2 rngSource.Cells(2, lngCols).Resize(lngRows, 2).Copy Cells(Rows.Count, 1).End(xlUp)(2) Next lngCols rngSource.Offset(, 2).Delete xlUp End Sub Regards, Sam Mathai Chacko On Tue, Jan 31, 2012 at 10:10 PM, Mukesh Kumar mukeshka...@gmail.comwrote: Dear Kris, Following error is coming after macro execution Run time error '9': Subscript out of range. Please correct it. Regards, Mukesh kumar On Tue, Jan 31, 2012 at 10:00 PM, Kris krishnak...@gmail.com wrote: Sub kTest() Dim x, ka, k(), i As Long, n As Long, c As Long, j As Long, p As Long With Worksheets(Query) ka = .Range(a1).CurrentRegion End With With CreateObject(scripting.dictionary) .comparemode = 1 For c = 1 To UBound(ka, 2) If Not .exists(ka(1, c)) Then j = j + 1 .Add ka(1, c), j End If Next ReDim k(1 To UBound(ka, 1) * UBound(ka, 2), 1 To j) For c = 1 To UBound(ka, 2) Step j For i = 2 To UBound(ka, 1) n = n + 1 For p = 0 To j - 1 k(n, .Item(ka(1, c + p))) = ka(i, c + p) Next Next Next x = .keys End With If n Then With Worksheets(Sheet1).Range(a1) .Parent.UsedRange.ClearContents .Range(a1).Resize(, j) = x .Range(a2).Resize(n, j) = k .Parent.UsedRange.Columns.AutoFit End With End If End Sub Kris -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- Sam Mathai Chacko -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com
Re: $$Excel-Macros$$ Query continuous copy paste
Here's the working model. Have given a button for ease. Sam Mathai Chacko On Tue, Jan 31, 2012 at 10:21 PM, Sam Mathai Chacko samde...@gmail.comwrote: Sub Consolidator() Dim rngSource As Range Dim lngCols As Long, lngRows As Long Set rngSource = Range(A1).CurrentRegion lngRows = rngSource.CurrentRegion.Rows.Count - 1 For lngCols = 3 To rngSource.Columns.Count - 1 Step 2 rngSource.Cells(2, lngCols).Resize(lngRows, 2).Copy Cells(Rows.Count, 1).End(xlUp)(2) Next lngCols rngSource.Offset(, 2).Delete xlUp End Sub Regards, Sam Mathai Chacko On Tue, Jan 31, 2012 at 10:10 PM, Mukesh Kumar mukeshka...@gmail.comwrote: Dear Kris, Following error is coming after macro execution Run time error '9': Subscript out of range. Please correct it. Regards, Mukesh kumar On Tue, Jan 31, 2012 at 10:00 PM, Kris krishnak...@gmail.com wrote: Sub kTest() Dim x, ka, k(), i As Long, n As Long, c As Long, j As Long, p As Long With Worksheets(Query) ka = .Range(a1).CurrentRegion End With With CreateObject(scripting.dictionary) .comparemode = 1 For c = 1 To UBound(ka, 2) If Not .exists(ka(1, c)) Then j = j + 1 .Add ka(1, c), j End If Next ReDim k(1 To UBound(ka, 1) * UBound(ka, 2), 1 To j) For c = 1 To UBound(ka, 2) Step j For i = 2 To UBound(ka, 1) n = n + 1 For p = 0 To j - 1 k(n, .Item(ka(1, c + p))) = ka(i, c + p) Next Next Next x = .keys End With If n Then With Worksheets(Sheet1).Range(a1) .Parent.UsedRange.ClearContents .Range(a1).Resize(, j) = x .Range(a2).Resize(n, j) = k .Parent.UsedRange.Columns.AutoFit End With End If End Sub Kris -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- Sam Mathai Chacko -- Sam Mathai Chacko -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com
Re: $$Excel-Macros$$ Query continuous copy paste
One of the header is 'No' instead of 'No.' Correct the headers, it should work Kris -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com
Re: $$Excel-Macros$$ Query continuous copy paste
Thanks a lot for solving my queries Ansari ji. But still one problem knocking my mind that these macroes are suitable for respective problems i have given i.e for two similar headers separate code for three similar headers another coding is required so on. Can u give common macro irrespective of Header names or count ? Else i feels i am unable to make clear my query to you ! Regards, Mukesh Kumar On Tue, Jan 31, 2012 at 11:18 PM, NOORAIN ANSARI noorain.ans...@gmail.comwrote: Dear Mukesh, See attached sheet Only procedure name is incorrect during calling under Command Button On Tue, Jan 31, 2012 at 9:20 PM, Mukesh Kumar mukeshka...@gmail.comwrote: @ Don Guillett Dear sir, your macro after running giving wrong results, please check. @ Noorain Ansari Sir, macro after running showing error compile error. Sub or function not defined. Please note, i am having different workbooks in which some workbooks contains 2 columns similar, some 3 columns headings similar other workbooks =5 similar column headings. So for all my workbooks, can you guys give common macro code or formulas. Regards, Mukesh Kumar On Tue, Jan 31, 2012 at 8:59 PM, NOORAIN ANSARI noorain.ans...@gmail.com wrote: Dear Mukesh, Please see attached sheet... I hope your query has been resolved... On Tue, Jan 31, 2012 at 8:48 PM, Mukesh Kumar mukeshka...@gmail.comwrote: Dear Ansari ji, Macro is running fine if sheet is having set of two similar header columns i.e let say a b. Problem occurs when header columns are more than two sets i.e a, b c. You can see in the attached file. You can also consider my query better as consolidate columns on basis of Column Titles. Hope you understand my query. Regards, Mukesh Kumar On Tue, Jan 31, 2012 at 8:24 PM, NOORAIN ANSARI noorain.ans...@gmail.com wrote: Dear Mukesh, Can you provide that worksheet, where macro is not running... Please share workbook with group.. On Tue, Jan 31, 2012 at 7:17 PM, Mukesh Kumar mukeshka...@gmail.comwrote: Thanks Noorain Ansari Ji for the solution but it works is working with this sheet only i.e if the column headings are having two same titles. If I have to transform three or four columns to first three or four columns then this macro will not work there. Please suggest any formula or user form according to variable number of columns titles. Waiting for the reply. Regards, Mukesh kumar On 1/31/12, NOORAIN ANSARI noorain.ans...@gmail.com wrote: Dear Mukesh, Please try it.. and see attached sheet. Sub mukesh() Dim i, c, j As Integer c = Application.WorksheetFunction.CountA(Sheet1.Range(1:1)) Sheet2.Range(A1:B1) = Array(Name, No) counter = 2 For j = 2 To c Step 2 For i = 2 To Sheet1.Cells(Rows.Count, j).End(xlUp).Row Sheet2.Cells(counter, 1).Value = Sheet1.Cells(i, j - 1) Sheet2.Cells(counter, 2).Value = Sheet1.Cells(i, j) counter = counter + 1 Next i Next j Sheet2.Columns.AutoFit Sheet2.Activate End Sub On Tue, Jan 31, 2012 at 1:10 PM, Mukesh Kumar mukeshka...@gmail.com wrote: Dear Sir, Please tell me the solution for the query given in the attached file. In it the data is distributed in the different columns. The solution is manually copy paste the data which is time consuming process. Please tell me the solution or formula to automate the process. Also please note that here two columns are having label i.e. Name No. There may be certain time the columns may having more than 2 or more labels. So please keep this situation too in mind. Thanks Regards, Mukesh Kumar -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- Thanks regards, Noorain Ansari *http://excelmacroworld.blogspot.com/* http://excelmacroworld.blogspot.com/ *http://noorain-ansari.blogspot.com/* http://noorain-ansari.blogspot.com/ -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be
Re: $$Excel-Macros$$ Query continuous copy paste
Thanks very very much Sam Mathai Chacko sir. This was the resultant code i was looking for. You are marvellous in excel sir. Thanks again. Regards, Mukesh Kumar On Wed, Feb 1, 2012 at 12:01 AM, Sam Mathai Chacko samde...@gmail.comwrote: The code I gave was exactly for assigning any number of header groups. Sam On Tue, Jan 31, 2012 at 11:44 PM, Mukesh Kumar mukeshka...@gmail.comwrote: Thanks a lot for solving my queries Ansari ji. But still one problem knocking my mind that these macroes are suitable for respective problems i have given i.e for two similar headers separate code for three similar headers another coding is required so on. Can u give common macro irrespective of Header names or count ? Else i feels i am unable to make clear my query to you ! Regards, Mukesh Kumar On Tue, Jan 31, 2012 at 11:18 PM, NOORAIN ANSARI noorain.ans...@gmail.com wrote: Dear Mukesh, See attached sheet Only procedure name is incorrect during calling under Command Button On Tue, Jan 31, 2012 at 9:20 PM, Mukesh Kumar mukeshka...@gmail.comwrote: @ Don Guillett Dear sir, your macro after running giving wrong results, please check. @ Noorain Ansari Sir, macro after running showing error compile error. Sub or function not defined. Please note, i am having different workbooks in which some workbooks contains 2 columns similar, some 3 columns headings similar other workbooks =5 similar column headings. So for all my workbooks, can you guys give common macro code or formulas. Regards, Mukesh Kumar On Tue, Jan 31, 2012 at 8:59 PM, NOORAIN ANSARI noorain.ans...@gmail.com wrote: Dear Mukesh, Please see attached sheet... I hope your query has been resolved... On Tue, Jan 31, 2012 at 8:48 PM, Mukesh Kumar mukeshka...@gmail.comwrote: Dear Ansari ji, Macro is running fine if sheet is having set of two similar header columns i.e let say a b. Problem occurs when header columns are more than two sets i.e a, b c. You can see in the attached file. You can also consider my query better as consolidate columns on basis of Column Titles. Hope you understand my query. Regards, Mukesh Kumar On Tue, Jan 31, 2012 at 8:24 PM, NOORAIN ANSARI noorain.ans...@gmail.com wrote: Dear Mukesh, Can you provide that worksheet, where macro is not running... Please share workbook with group.. On Tue, Jan 31, 2012 at 7:17 PM, Mukesh Kumar mukeshka...@gmail.com wrote: Thanks Noorain Ansari Ji for the solution but it works is working with this sheet only i.e if the column headings are having two same titles. If I have to transform three or four columns to first three or four columns then this macro will not work there. Please suggest any formula or user form according to variable number of columns titles. Waiting for the reply. Regards, Mukesh kumar On 1/31/12, NOORAIN ANSARI noorain.ans...@gmail.com wrote: Dear Mukesh, Please try it.. and see attached sheet. Sub mukesh() Dim i, c, j As Integer c = Application.WorksheetFunction.CountA(Sheet1.Range(1:1)) Sheet2.Range(A1:B1) = Array(Name, No) counter = 2 For j = 2 To c Step 2 For i = 2 To Sheet1.Cells(Rows.Count, j).End(xlUp).Row Sheet2.Cells(counter, 1).Value = Sheet1.Cells(i, j - 1) Sheet2.Cells(counter, 2).Value = Sheet1.Cells(i, j) counter = counter + 1 Next i Next j Sheet2.Columns.AutoFit Sheet2.Activate End Sub On Tue, Jan 31, 2012 at 1:10 PM, Mukesh Kumar mukeshka...@gmail.com wrote: Dear Sir, Please tell me the solution for the query given in the attached file. In it the data is distributed in the different columns. The solution is manually copy paste the data which is time consuming process. Please tell me the solution or formula to automate the process. Also please note that here two columns are having label i.e. Name No. There may be certain time the columns may having more than 2 or more labels. So please keep this situation too in mind. Thanks Regards, Mukesh Kumar -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- Thanks regards, Noorain Ansari
