Hello,
I was hoping that someone could point me in the right direction. I am decent with macros, but creating them from scratch is where I struggle. I enjoy learning how to adapt code that I find on this forum and making then work for my needs.
This time, I have a situation where I have two needs and cannot get the two solutions to work together.
I have an excel file that lists report data based on regions. I would like to:
1. Create new sheets based on the region name
2. Move the row data to the correct new sheet based on "region"
I figured that I could move the list of regions to a new sheet and create unique values and make sure they are de-duplicated, then create new sheets based on the list.
I then found information on how to move data to a new sheet based on cell information here: https://www.mrexcel.com/forum/excel-questions/673106-move-row-into-new-sheet-based-cell-value.html.
But it seems to work with one sheet?
Sub Cheezy()
'move rows from sheet 1 to sheet 2 if column E has a Yes in it.
'for http://www.mrexcel.com/forum/excel-q...ell-value.htmlDim Check As Range
Lastrow = Worksheets("Sheet1").UsedRange.Rows.Count
lastrow2 = Worksheets("Sheet2").UsedRange.Rows.Count
If lastrow2 = 1 Then
lastrow2 = 0
Else
End If
Do While Application.WorksheetFunction.CountIf(Range("E:E"), "Yes") > 0
Set Check = Range("E1:E" & Lastrow)
For Each Cell In Check
If Cell = "Yes" Then
Cell.EntireRow.Copy Destination:=Worksheets("Sheet2").Range("A" & lastrow2 + 1)
Cell.EntireRow.Delete
lastrow2 = lastrow2 + 1
Else:
End If
Next
Loop
End Sub
I can provide more information as needed.
Here is sample data:
<colgroup><col width="141" style="width: 106pt;"><col width="88" span="2" style="width: 66pt;"><col width="117" style="width: 88pt;"></colgroup><tbody>
</tbody>
Any help would be appreciated!
I was hoping that someone could point me in the right direction. I am decent with macros, but creating them from scratch is where I struggle. I enjoy learning how to adapt code that I find on this forum and making then work for my needs.
This time, I have a situation where I have two needs and cannot get the two solutions to work together.
I have an excel file that lists report data based on regions. I would like to:
1. Create new sheets based on the region name
2. Move the row data to the correct new sheet based on "region"
I figured that I could move the list of regions to a new sheet and create unique values and make sure they are de-duplicated, then create new sheets based on the list.
I then found information on how to move data to a new sheet based on cell information here: https://www.mrexcel.com/forum/excel-questions/673106-move-row-into-new-sheet-based-cell-value.html.
But it seems to work with one sheet?
Sub Cheezy()
'move rows from sheet 1 to sheet 2 if column E has a Yes in it.
'for http://www.mrexcel.com/forum/excel-q...ell-value.htmlDim Check As Range
Lastrow = Worksheets("Sheet1").UsedRange.Rows.Count
lastrow2 = Worksheets("Sheet2").UsedRange.Rows.Count
If lastrow2 = 1 Then
lastrow2 = 0
Else
End If
Do While Application.WorksheetFunction.CountIf(Range("E:E"), "Yes") > 0
Set Check = Range("E1:E" & Lastrow)
For Each Cell In Check
If Cell = "Yes" Then
Cell.EntireRow.Copy Destination:=Worksheets("Sheet2").Range("A" & lastrow2 + 1)
Cell.EntireRow.Delete
lastrow2 = lastrow2 + 1
Else:
End If
Next
Loop
End Sub
I can provide more information as needed.
Here is sample data:
First Name | Last Name | Division | Region |
Catherine | Smith | West | Texas |
Cathy | Armstrong | West | Texas |
Chris | Spencer | West | Texas |
Darin | Klaehn | West | Texas |
David | Himel | West | Texas |
Greg | Stowe | West | Texas |
Jessica | Jackson | West | Texas |
Pete | Rodriguez | West | Texas |
Stephen | Fine | West | Texas |
Steve | Hisamoto | West | Texas |
<colgroup><col width="141" style="width: 106pt;"><col width="88" span="2" style="width: 66pt;"><col width="117" style="width: 88pt;"></colgroup><tbody>
</tbody>
Any help would be appreciated!