Move Records from Sheet1 to Sheet2

My Sheet 1 contains 25 Columns - 30 rows of data, where row1 = The Header
rows;
In Sheet1 - Column 19 is label "STATUS".
How can I MOVE all records (the complete row data) from Sheet1 to Sheet2
(with the Same headers) ONLY
for records (on Sheet1) where STATUS = "RELEASED" (without the quote marks)
?
Tks in Advance,,
Jim

Hi Jim,

Try:
'===================>>
Public Sub CopyRows()
Dim Rng As Range
Dim Rng2 As Range
Dim rCell As Range
Dim WB As Workbook
Dim SH As Worksheet
Dim SH2 As Worksheet
Dim CalcMode As Long

Set WB = ActiveWorkbook '<<========== CHANGE
Set SH = WB.Sheets("Sheet1") '<<========== CHANGE
Set SH2 = WB.Sheets("Sheet2") '<<========== CHANGE

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

Set Rng = SH.Range("A1").CurrentRegion
Set Rng2 = Rng.Rows(1)

For Each rCell In Rng.Columns(1).Cells
If UCase(rCell.Offset(0, 18).Value) = "RELEASED" Then
Set Rng2 = Union(rCell, Rng2)
End If
Next rCell

If Not Rng2 Is Nothing Then
Rng2.EntireRow.Copy Destination:=SH2.Range("A1")
Else
'nothing found, do nothing
End If

With Application
.Calculation = CalcMode
.ScreenUpdating = True
End With

End Sub
'<<===================


You could also use the Advanced filter feature, invokink th filter from the
destination sheet.


---
Regards,
Norman



"Jim May" <[email protected]> wrote in message
news:XQG_e.123055$Ep.21820@lakeread02...
> My Sheet 1 contains 25 Columns - 30 rows of data, where row1 = The Header
> rows;
> In Sheet1 - Column 19 is label "STATUS".
> How can I MOVE all records (the complete row data) from Sheet1 to Sheet2
> (with the Same headers) ONLY
> for records (on Sheet1) where STATUS = "RELEASED" (without the quote
> marks) ?
> Tks in Advance,,
> Jim
>
>
>

Great, Thanks for the code;
I have a command button CmdCopyToReleasedSheet
on a Form frmReleaseMenu which has the following code:
Private Sub CmdCopyToReleasedSheet_Click()
Application.Visible = True
Sheets("POReqs").Visible = True
Sheets("POReqs").Activate
Sheets("POReqsSent").Visible = True
CopyRows ' Your Suggested Code referenced here
End Sub

With your std Module CopyRows as Public, shouldn't things work?

Tks for your help.

"Norman Jones" <[email protected]> wrote in message
news:[email protected]...
> Hi Jim,
>
> Try:
> '===================>>
> Public Sub CopyRows()
> Dim Rng As Range
> Dim Rng2 As Range
> Dim rCell As Range
> Dim WB As Workbook
> Dim SH As Worksheet
> Dim SH2 As Worksheet
> Dim CalcMode As Long
>
> Set WB = ActiveWorkbook '<<========== CHANGE
> Set SH = WB.Sheets("Sheet1") '<<========== CHANGE
> Set SH2 = WB.Sheets("Sheet2") '<<========== CHANGE
>
> With Application
> CalcMode = .Calculation
> .Calculation = xlCalculationManual
> .ScreenUpdating = False
> End With
>
> Set Rng = SH.Range("A1").CurrentRegion
> Set Rng2 = Rng.Rows(1)
>
> For Each rCell In Rng.Columns(1).Cells
> If UCase(rCell.Offset(0, 18).Value) = "RELEASED" Then
> Set Rng2 = Union(rCell, Rng2)
> End If
> Next rCell
>
> If Not Rng2 Is Nothing Then
> Rng2.EntireRow.Copy Destination:=SH2.Range("A1")
> Else
> 'nothing found, do nothing
> End If
>
> With Application
> .Calculation = CalcMode
> .ScreenUpdating = True
> End With
>
> End Sub
> '<<===================
>
>
> You could also use the Advanced filter feature, invokink th filter from
> the destination sheet.
>
>
> ---
> Regards,
> Norman
>
>
>
> "Jim May" <[email protected]> wrote in message
> news:XQG_e.123055$Ep.21820@lakeread02...
>> My Sheet 1 contains 25 Columns - 30 rows of data, where row1 = The Header
>> rows;
>> In Sheet1 - Column 19 is label "STATUS".
>> How can I MOVE all records (the complete row data) from Sheet1 to Sheet2
>> (with the Same headers) ONLY
>> for records (on Sheet1) where STATUS = "RELEASED" (without the quote
>> marks) ?
>> Tks in Advance,,
>> Jim
>>
>>
>>
>
>

Hi Jim,

Yes, you can call the CopyRows procedure from the Userform command button.

Incidentally, I do not hnow which sheets you are unhiding in the button's
click event, but both the source and destination sheets can both be hidden
when the CopyRows procedure runs.

---
Regards,
Norman



"Jim May" <[email protected]> wrote in message
news:aQI_e.123066$Ep.28313@lakeread02...
> Great, Thanks for the code;
> I have a command button CmdCopyToReleasedSheet
> on a Form frmReleaseMenu which has the following code:
> Private Sub CmdCopyToReleasedSheet_Click()
> Application.Visible = True
> Sheets("POReqs").Visible = True
> Sheets("POReqs").Activate
> Sheets("POReqsSent").Visible = True
> CopyRows ' Your Suggested Code referenced here
> End Sub
>
> With your std Module CopyRows as Public, shouldn't things work?
>
> Tks for your help.
>
> "Norman Jones" <[email protected]> wrote in message
> news:[email protected]...
>> Hi Jim,
>>
>> Try:
>> '===================>>
>> Public Sub CopyRows()
>> Dim Rng As Range
>> Dim Rng2 As Range
>> Dim rCell As Range
>> Dim WB As Workbook
>> Dim SH As Worksheet
>> Dim SH2 As Worksheet
>> Dim CalcMode As Long
>>
>> Set WB = ActiveWorkbook '<<========== CHANGE
>> Set SH = WB.Sheets("Sheet1") '<<========== CHANGE
>> Set SH2 = WB.Sheets("Sheet2") '<<========== CHANGE
>>
>> With Application
>> CalcMode = .Calculation
>> .Calculation = xlCalculationManual
>> .ScreenUpdating = False
>> End With
>>
>> Set Rng = SH.Range("A1").CurrentRegion
>> Set Rng2 = Rng.Rows(1)
>>
>> For Each rCell In Rng.Columns(1).Cells
>> If UCase(rCell.Offset(0, 18).Value) = "RELEASED" Then
>> Set Rng2 = Union(rCell, Rng2)
>> End If
>> Next rCell
>>
>> If Not Rng2 Is Nothing Then
>> Rng2.EntireRow.Copy Destination:=SH2.Range("A1")
>> Else
>> 'nothing found, do nothing
>> End If
>>
>> With Application
>> .Calculation = CalcMode
>> .ScreenUpdating = True
>> End With
>>
>> End Sub
>> '<<===================
>>
>>
>> You could also use the Advanced filter feature, invokink th filter from
>> the destination sheet.
>>
>>
>> ---
>> Regards,
>> Norman
>>
>>
>>
>> "Jim May" <[email protected]> wrote in message
>> news:XQG_e.123055$Ep.21820@lakeread02...
>>> My Sheet 1 contains 25 Columns - 30 rows of data, where row1 = The
>>> Header rows;
>>> In Sheet1 - Column 19 is label "STATUS".
>>> How can I MOVE all records (the complete row data) from Sheet1 to Sheet2
>>> (with the Same headers) ONLY
>>> for records (on Sheet1) where STATUS = "RELEASED" (without the quote
>>> marks) ?
>>> Tks in Advance,,
>>> Jim
>>>
>>>
>>>
>>
>>
>
>