Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Move Records from Sheet1 to Sheet2
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" 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Move Records from Sheet1 to Sheet2
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" wrote in message ... 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" 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Move Records from Sheet1 to Sheet2
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" 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" wrote in message ... 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" 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copy data from sheet2 to sheet1 when sheet2 has variable # of rows | Excel Discussion (Misc queries) | |||
how can i move from from sheet1 to sheet2 in the xl without mouse | Excel Worksheet Functions | |||
how do copy "sheet1!A1+1 in sheet2 to sheet 3 and get "sheet2!A1+ | Excel Discussion (Misc queries) | |||
[=Sheet1!A1] - if i use Column A1 ('Sheet1') / A2 ('Sheet2') | Excel Worksheet Functions | |||
Looking for comparable data records between Sheet1 and Sheet2 | Excel Discussion (Misc queries) |