Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help on Excel macro.
I have 5 sheet in a workbook, and all the sheet contains a field which contains different name for example: sheet1 contains 9 fields and sheet2 may contain 11 fields but both may contain a field called marks which is different position on the 2 sheets. And in marks field if there is a mark of 50 is there in that then i have to select some more detail of that raw to another sheet. Finally i need to take some data from all the sheets raws where the mark is 50. could you please help me to create the macro for this. Thanks, Robin. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help on Excel macro.
We really need you to be more descriptive in your request. As such:
1. What do you mean by "field"? Do you mean a named range, a single cell, or a group of cells? 2. If a cell contains 50, then specifically what do you want to do? Do you want to copy a part of a row to another sheet or... what? 3. Does the data that must be copied from all sheets follow any pattern? Please post code or specific locations of the data to work one, and we can help you come up with something. Matthew Pfluger "robin thobiyas" wrote: I have 5 sheet in a workbook, and all the sheet contains a field which contains different name for example: sheet1 contains 9 fields and sheet2 may contain 11 fields but both may contain a field called marks which is different position on the 2 sheets. And in marks field if there is a mark of 50 is there in that then i have to select some more detail of that raw to another sheet. Finally i need to take some data from all the sheets raws where the mark is 50. could you please help me to create the macro for this. Thanks, Robin. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help on Excel macro.
On Sep 27, 10:07 pm, Matthew Pfluger
wrote: We really need you to be more descriptive in your request. As such: 1. What do you mean by "field"? Do you mean a named range, a single cell, or a group of cells? 2. If a cell contains 50, then specifically what do you want to do? Do you want to copy a part of a row to another sheet or... what? 3. Does the data that must be copied from all sheets follow any pattern? Please post code or specific locations of the data to work one, and we can help you come up with something. Matthew Pfluger "robin thobiyas" wrote: I have 5 sheet in a workbook, and all the sheet contains a field which contains different name for example: sheet1 contains 9 fields and sheet2 may contain 11 fields but both may contain a field called marks which is different position on the 2 sheets. And in marks field if there is a mark of 50 is there in that then i have to select some more detail of that raw to another sheet. Finally i need to take some data from all the sheets raws where the mark is 50. could you please help me to create the macro for this. Thanks, Robin.- Hide quoted text - - Show quoted text - Sorry for the confusion. Column is what i meant to say by field. I have 5 different sheets in my workbook with different set of columns, But there is one column with one specific heading(eg:Marks) in all the sheets.I want to find one particular data in all the sheets on this specific column(Marks) and if the column data match with the searching data, then i want to extract that matched row into another sheet. I hope this made you clear. Thanks in advance. And it may be in different locations in the sheets. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help on Excel macro.
If you don't know where the correct column is, you are going to have to
search for it. This macro will go through each sheet, find the marks column (if it exists), and find all marks of 50. You will need to change it slightly to copy all the data you need to a reports worksheet. Sub getMarks() ' Create variables Dim wksTarget As Worksheet ' Worksheet to search through Dim rMarksColStart As Range ' Location of marks column Dim iMarksCol As Integer ' Marks column number Dim lLastRow As Long ' Last row in target worksheet Dim wksReport As Worksheet ' Report worksheet Set wksReport = Sheets.Add(after:=Sheets(Sheets.Count)) wksReport.Name = "Report" wksReport.Range("a1").Value = "Item 1" wksReport.Range("b1").Value = "Item 2" ' Create additional column headings if necessary Dim lReportRow As Long ' Current data row in Report sheet lReportRow = 2 ' Loop through each sheet looking for data For Each wksTarget In ThisWorkbook.Sheets If wksTarget.Name = wksReport.Name Then Exit For ' Find "Marks" column wksTarget.Activate Set rMarksColStart = Range("1:1").Find(what:="Marks", LookIn:=xlValues) ' If "Marks" column exists If Not rMarksColStart Is Nothing Then ' Store column number and get final row number iMarksCol = rMarksColStart.Column lLastRow = Cells(Application.Rows.Count, iMarksCol).End(xlUp).Row ' If data exists If Not lLastRow = 1 Then ' Loop through all cells in column looking for "50" For i = 2 To lLastRow If Cells(i, iMarksCol).Value = 50 Then Cells(i, iMarksCol).Select ' Change and add new lines to this part to get all the information from the row you need wksTarget.Cells(i, 1).Copy Destination:=wksReport.Cells(lReportRow, 1) wksTarget.Cells(i, iMarksCol).Copy Destination:=wksReport.Cells(lReportRow, 2) ' Increment the report row lReportRow = lReportRow + 1 End If Next End If End If Next wksTarget End Sub "robin thobiyas" wrote: On Sep 27, 10:07 pm, Matthew Pfluger wrote: We really need you to be more descriptive in your request. As such: 1. What do you mean by "field"? Do you mean a named range, a single cell, or a group of cells? 2. If a cell contains 50, then specifically what do you want to do? Do you want to copy a part of a row to another sheet or... what? 3. Does the data that must be copied from all sheets follow any pattern? Please post code or specific locations of the data to work one, and we can help you come up with something. Matthew Pfluger "robin thobiyas" wrote: I have 5 sheet in a workbook, and all the sheet contains a field which contains different name for example: sheet1 contains 9 fields and sheet2 may contain 11 fields but both may contain a field called marks which is different position on the 2 sheets. And in marks field if there is a mark of 50 is there in that then i have to select some more detail of that raw to another sheet. Finally i need to take some data from all the sheets raws where the mark is 50. could you please help me to create the macro for this. Thanks, Robin.- Hide quoted text - - Show quoted text - Sorry for the confusion. Column is what i meant to say by field. I have 5 different sheets in my workbook with different set of columns, But there is one column with one specific heading(eg:Marks) in all the sheets.I want to find one particular data in all the sheets on this specific column(Marks) and if the column data match with the searching data, then i want to extract that matched row into another sheet. I hope this made you clear. Thanks in advance. And it may be in different locations in the sheets. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help on Excel macro.
On Sep 28, 7:17 pm, Matthew Pfluger
wrote: If you don't know where the correct column is, you are going to have to search for it. This macro will go through each sheet, find the marks column (if it exists), and find all marks of 50. You will need to change it slightly to copy all the data you need to a reports worksheet. Sub getMarks() ' Create variables Dim wksTarget As Worksheet ' Worksheet to search through Dim rMarksColStart As Range ' Location of marks column Dim iMarksCol As Integer ' Marks column number Dim lLastRow As Long ' Last row in target worksheet Dim wksReport As Worksheet ' Report worksheet Set wksReport = Sheets.Add(after:=Sheets(Sheets.Count)) wksReport.Name = "Report" wksReport.Range("a1").Value = "Item 1" wksReport.Range("b1").Value = "Item 2" ' Create additional column headings if necessary Dim lReportRow As Long ' Current data row in Report sheet lReportRow = 2 ' Loop through each sheet looking for data For Each wksTarget In ThisWorkbook.Sheets If wksTarget.Name = wksReport.Name Then Exit For ' Find "Marks" column wksTarget.Activate Set rMarksColStart = Range("1:1").Find(what:="Marks", LookIn:=xlValues) ' If "Marks" column exists If Not rMarksColStart Is Nothing Then ' Store column number and get final row number iMarksCol = rMarksColStart.Column lLastRow = Cells(Application.Rows.Count, iMarksCol).End(xlUp).Row ' If data exists If Not lLastRow = 1 Then ' Loop through all cells in column looking for "50" For i = 2 To lLastRow If Cells(i, iMarksCol).Value = 50 Then Cells(i, iMarksCol).Select ' Change and add new lines to this part to get all the information from the row you need wksTarget.Cells(i, 1).Copy Destination:=wksReport.Cells(lReportRow, 1) wksTarget.Cells(i, iMarksCol).Copy Destination:=wksReport.Cells(lReportRow, 2) ' Increment the report row lReportRow = lReportRow + 1 End If Next End If End If Next wksTarget End Sub "robin thobiyas" wrote: On Sep 27, 10:07 pm, Matthew Pfluger wrote: We really need you to be more descriptive in your request. As such: 1. What do you mean by "field"? Do you mean a named range, a single cell, or a group of cells? 2. If a cell contains 50, then specifically what do you want to do? Do you want to copy a part of a row to another sheet or... what? 3. Does the data that must be copied from all sheets follow any pattern? Please post code or specific locations of the data to work one, and we can help you come up with something. Matthew Pfluger "robin thobiyas" wrote: I have 5 sheet in a workbook, and all the sheet contains a field which contains different name for example: sheet1 contains 9 fields and sheet2 may contain 11 fields but both may contain a field called marks which is different position on the 2 sheets. And in marks field if there is a mark of 50 is there in that then i have to select some more detail of that raw to another sheet. Finally i need to take some data from all the sheets raws where the mark is 50. could you please help me to create the macro for this. Thanks, Robin.- Hide quoted text - - Show quoted text - Sorry for the confusion. Column is what i meant to say by field. I have 5 different sheets in my workbook with different set of columns, But there is one column with one specific heading(eg:Marks) in all the sheets.I want to find one particular data in all the sheets on this specific column(Marks) and if the column data match with the searching data, then i want to extract that matched row into another sheet. I hope this made you clear. Thanks in advance. And it may be in different locations in the sheets.- Hide quoted text - - Show quoted text - Thanks Mathew. I think it may work for my purpose. I will get back to you if i have any issues. Once again thanks for your help. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro Help Needed - Excel 2007 - Print Macro with Auto Sort | Excel Worksheet Functions | |||
My excel macro recorder no longer shows up when recording macro | Excel Discussion (Misc queries) | |||
Call macro stored in Excel workbook from Outlook's macro | Excel Programming | |||
Need syntax for RUNning a Word macro with an argument, called from an Excel macro | Excel Programming | |||
translate lotus 1-2-3 macro into excel macro using excel 2000 | Excel Programming |