Need help on Excel macro.

[robin thobiyas]

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.

 

[Guest]

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]

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









- 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.

 

[Guest]

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]

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









- 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.