Selecting Ranges Marked with an "X" on one sheet and moving them to another sheet in VBA

EKelley0311

New Member
Joined
Oct 3, 2014
Messages
3
I'm currently working on a project that needs to move some rows marked with an "x" in one sheet to another sheet within the workbook. The code works except for the fact that it's not moving the selected rows to the other sheet. Instead, it's just replicating the rows in the same first sheet. I have a feeling that it's something simple that I'm just overlooking. Wanted to see if it stands out to anyone else. Thanks for any help! Here's the code....

Option Explicit
Sub CommandButton1_Click()
Dim i As Long
Dim y As Long
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim Ary1 As Range
Dim Ary2 As Range

Set ws1 = ActiveWorkbook.Sheets("SSAT")
Set ws2 = ActiveWorkbook.Sheets("Accrual Entry Sheet")

y = 15

For i = 2 To 100:
If ws1.Cells(i, 20) = "x" Then
ws1.Activate
Set Ary1 = Range(Cells(i, 1), Cells(i, 17))
ws2.Activate
Set Ary2 = Range(Cells(y, 1), Cells(y, 17))
Ary2.Value = Ary1.Value
y = y + 1
End If
Next i

MsgBox ("Done")
End Sub
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
You don't need to activate the sheets:

Code:
Sub CommandButton1_Click()    Dim i As Long
    Dim y As Long
    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Dim Ary1 As Range
    Dim Ary2 As Range
    
    Set ws1 = ActiveWorkbook.Sheets("SSAT")
    Set ws2 = ActiveWorkbook.Sheets("Accrual Entry Sheet")
    
    y = 15
    
    For i = 2 To 100:
        If ws1.Cells(i, 20) = "x" Then
            
            ws2.Range(ws2.Cells(y, 1), ws2.Cells(y, 17)).Value = _
                ws1.Range(ws1.Cells(1, 1), ws1.cell(i, 17)).Value
                
            y = y + 1


        End If
    Next i
    
    MsgBox ("Done")
End Sub

Try that. I can't remember if you need to do ws1.range(ws1.cells()) or not because I usually do the entire row.

ws1.cells(i, 1).entireRow
 
Upvote 0
Bingo! I had actually written a code similar to this earlier but missed some of the syntax when I was specifying the ranges. Makes sense now since I was missing focus on the worksheets! Thanks so much for the help. This was my first time to this forum, but I'm sure I will be back in the future now! Great resource! Always nice to be able to get ideas from others when it comes to VBA code.
 
Upvote 0
IF Column T contains constants (that is, no formulas), then here is another procedure you can consider (I think it might be faster than the code that NeonRedShapie posted)...
Code:
Sub CommandButton1_Click()
  With ActiveWorkbook.Sheets("SSAT")
    .Columns("T").Replace "x", "#N/A", xlWhole
    Intersect(.Columns("A:Q"), .Columns("T").SpecialCells(xlConstants, xlErrors). _
                      EntireRow).Copy Sheets("Accrual Entry Sheet").Cells(15, 1)
    .Columns("T").Replace "#N/A", "x", xlWhole
  End With
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,974
Members
448,537
Latest member
Et_Cetera

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top