Automatic Row Generation/Insertion
Thank you for your help!
What if I wanted to insert a row in Sheet1 AFTER the first occurrance of NewYork? Sheet1: Total Breakdown by City NEW YORK NEWYORK 12 40 93 83 00 29 48 NEWYORK 20 89 85 01 92 30 93 BOSTON BOSTON 20 102 94 29 10 48 29 BOSTON 50 152 54 39 15 43 59 etc. Thanks! |
Automatic Row Generation/Insertion
This seems to work ok:
Option Explicit Sub testme01() Dim FoundCell As Range Dim FirstAddress As String Dim fromWks As Worksheet Dim toWks As Worksheet Dim findWhat As String Dim destCell As Range findWhat = "newyork" Set fromWks = Worksheets("sheet2") Set toWks = Worksheets("sheet1") With toWks Set destCell = .Cells.Find(what:=findWhat, _ after:=.Cells(.Cells.Count), _ LookIn:=xlValues, lookat:=xlWhole, _ searchorder:=xlByRows, searchdirection:=xlNext, _ MatchCase:=False) If destCell Is Nothing Then MsgBox findWhat & " wasn't found on " & toWks.Name Exit Sub End If End With With fromWks Set FoundCell = .Cells.Find(what:=findWhat, _ after:=.Cells(.Cells.Count), _ LookIn:=xlValues, lookat:=xlWhole, _ searchorder:=xlByRows, searchdirection:=xlNext, _ MatchCase:=False) If FoundCell Is Nothing Then MsgBox "not found on " & fromWks.Name Exit Sub End If FirstAddress = FoundCell.Address Do destCell.Offset(1, 0).EntireRow.Insert FoundCell.EntireRow.Copy _ Destination:=destCell.Offset(1, 0) Set FoundCell = .Cells.FindNext(FoundCell) Loop While Not FoundCell Is Nothing _ And FoundCell.Address < FirstAddress End With End Sub But a suggestion. Don't use that extra line in your data. It make look very pretty, but it may screw up things later. If you ever want to apply subtotals to get a count per city, you'll have to do things differently. It may make pivottables and charts more difficult. I'd just apply Data|filter|autofilter to my range and then I could show the city and all its data by using that filter. Kyle80 wrote: Thank you for your help! What if I wanted to insert a row in Sheet1 AFTER the first occurrance of NewYork? Sheet1: Total Breakdown by City NEW YORK NEWYORK 12 40 93 83 00 29 48 NEWYORK 20 89 85 01 92 30 93 BOSTON BOSTON 20 102 94 29 10 48 29 BOSTON 50 152 54 39 15 43 59 etc. Thanks! -- Dave Peterson |
All times are GMT +1. The time now is 09:18 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com