Can you please show me how to perform the following in Excel, I am not an
Excel Programmer. I would like to have a command button that calls a
subroutine that
takes two parameters (orglevel2name and status.)
sub Copy_Contents(orglevel2name as string, status as string)
The routine needs to loop through a worksheet called "detail"
The routine needs to loop through column p starting in row 4 until the end
and compare the value of the cell to value of orglevel2name. If the value is
the same it then needs to get value from column i (same row) and compare to
status value. If that is also true it then needs to copy the row from column
a to column t to another existing worksheet called "detail_format"
Can you please show me the syntax to reference the source sheet "detail",
loop though the column, get the value of the cell and copy to the destination
sheet "detail_format"
Thanks

Try the below procedure..Comments included..

Sub Macro()
Call CopyRow("Organisation1", "statusYes")
End Sub

Sub CopyRow(strOrglevel2name As String, varStatus As Variant)
Dim lngRow As Long, lngDestRow As Long
Dim ws1 As Worksheet, ws2 As Worksheet

'Refer sheets
Set ws1 = Sheets("Detail")
Set ws2 = Sheets("detail_format")

'Get next available sheet in destination sheet
lngDestRow = ws2.Cells(Rows.Count, "A").End(xlUp).Row + 1

'Loop until end of data for column P
For lngRow = 4 To ws1.Cells(Rows.Count, "P").End(xlUp).Row
If Range("P" & lngRow) = strOrglevel2name And _
Range("I" & lngRow) = varStatus Then
ws1.Range("A" & lngRow & ":T" & lngRow).Copy ws2.Range("A" & _
lngDestRow): lngDestRow = lngDestRow + 1
End If
Next
End Sub


If this post helps click Yes
---------------
Jacob Skaria


"James" wrote:

Can you please show me how to perform the following in Excel, I am not an
Excel Programmer. I would like to have a command button that calls a
subroutine that
takes two parameters (orglevel2name and status.)
sub Copy_Contents(orglevel2name as string, status as string)
The routine needs to loop through a worksheet called "detail"
The routine needs to loop through column p starting in row 4 until the end
and compare the value of the cell to value of orglevel2name. If the value is
the same it then needs to get value from column i (same row) and compare to
status value. If that is also true it then needs to copy the row from column
a to column t to another existing worksheet called "detail_format"
Can you please show me the syntax to reference the source sheet "detail",
loop though the column, get the value of the cell and copy to the destination
sheet "detail_format"
Thanks

At first it wasn't copying. I added the ws1 to the beginning of Range("P" &
lngRow) and Range("I" & lngRow) and it worked. This was a huge help. Thanks.

"Jacob Skaria" wrote:

Try the below procedure..Comments included..

Sub Macro()
Call CopyRow("Organisation1", "statusYes")
End Sub

Sub CopyRow(strOrglevel2name As String, varStatus As Variant)
Dim lngRow As Long, lngDestRow As Long
Dim ws1 As Worksheet, ws2 As Worksheet

'Refer sheets
Set ws1 = Sheets("Detail")
Set ws2 = Sheets("detail_format")

'Get next available sheet in destination sheet
lngDestRow = ws2.Cells(Rows.Count, "A").End(xlUp).Row + 1

'Loop until end of data for column P
For lngRow = 4 To ws1.Cells(Rows.Count, "P").End(xlUp).Row
If Range("P" & lngRow) = strOrglevel2name And _
Range("I" & lngRow) = varStatus Then
ws1.Range("A" & lngRow & ":T" & lngRow).Copy ws2.Range("A" & _
lngDestRow): lngDestRow = lngDestRow + 1
End If
Next
End Sub


If this post helps click Yes
---------------
Jacob Skaria


"James" wrote:

Can you please show me how to perform the following in Excel, I am not an
Excel Programmer. I would like to have a command button that calls a
subroutine that
takes two parameters (orglevel2name and status.)
sub Copy_Contents(orglevel2name as string, status as string)
The routine needs to loop through a worksheet called "detail"
The routine needs to loop through column p starting in row 4 until the end
and compare the value of the cell to value of orglevel2name. If the value is
the same it then needs to get value from column i (same row) and compare to
status value. If that is also true it then needs to copy the row from column
a to column t to another existing worksheet called "detail_format"
Can you please show me the syntax to reference the source sheet "detail",
loop though the column, get the value of the cell and copy to the destination
sheet "detail_format"
Thanks