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
 

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

NeonRedSharpie

Well-known Member
Joined
Jul 14, 2014
Messages
1,678
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
 

EKelley0311

New Member
Joined
Oct 3, 2014
Messages
3
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.
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,337
Office Version
  1. 2010
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,109,540
Messages
5,529,434
Members
409,876
Latest member
Akash Yadav
Top