Need help copying rows please
Closed
wtr2wine
-
May 2, 2012 at 10:22 AM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - May 3, 2012 at 06:54 PM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - May 3, 2012 at 06:54 PM
Related:
- Need help copying rows please
- Protect pdf from copying - Guide
- Copying youtube link - Guide
- Copying cd to computer - Guide
- How to insert multiple rows in microsoft excel - Guide
- How to delete rows and columns in word - Guide
1 response
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
May 2, 2012 at 06:06 PM
May 2, 2012 at 06:06 PM
you would need to use macro as you correctly have said.
What you need to do is
1. create a loop till last row is reached
2. for row, get the account number
3. use "SEARCH" command, to located the account
4. If found, then
4a insert a row
4b copy the row
4c paste the row
5. move to next row and repeat step 2 -5
What you need to do is
1. create a loop till last row is reached
2. for row, get the account number
3. use "SEARCH" command, to located the account
4. If found, then
4a insert a row
4b copy the row
4c paste the row
5. move to next row and repeat step 2 -5
May 3, 2012 at 07:27 AM
May 3, 2012 at 06:54 PM
Here is code which at the minimum should give you a great starting point
Option Explicit Public Sub doCopy() Dim wsSource As Worksheet Dim wsTarget As Worksheet Dim lStartRow As Long Dim lEndRow As Long Dim lMatchRow As Long Dim lRow As Long Dim sID As String Dim sAccount As String Set wsSource = Workbooks("Source.xls").Worksheets("Sheet1") Set wsTarget = Workbooks("Target.xls").Worksheets("Sheet2") lStartRow = 2 lEndRow = getItemLocation("*", wsSource.Cells) For lRow = lStartRow To lEndRow sAccount = wsSource.Cells(lRow, "E") lMatchRow = getItemLocation(sAccount, wsTarget.Range("B:B")) If (lMatchRow > 0) Then lMatchRow = lMatchRow + 1 wsTarget.Rows(lMatchRow).Insert Application.CutCopyMode = False wsSource.Range(wsSource.Cells(lRow, "A"), wsSource.Cells(lRow, "I")).Copy wsTarget.Cells(lMatchRow, "A").PasteSpecial Application.CutCopyMode = False End If Next lRow End Sub Public Function getItemLocation(sLookFor As String, _ rngSearch As Range, _ Optional bFullString As Boolean = True, _ Optional bLastOccurance As Boolean = True, _ Optional bFindRow As Boolean = True) As Long 'find the first/last row/column within a range for a specific string Dim Cell As Range Dim iLookAt As Integer Dim iSearchDir As Integer Dim iSearchOdr As Integer If (bFullString) _ Then iLookAt = xlWhole Else iLookAt = xlPart End If If (bLastOccurance) _ Then iSearchDir = xlPrevious Else iSearchDir = xlNext End If If Not (bFindRow) _ Then iSearchOdr = xlByColumns Else iSearchOdr = xlByRows End If With rngSearch If (bLastOccurance) _ Then Set Cell = .Find(sLookFor, .Cells(1, 1), xlValues, iLookAt, iSearchOdr, iSearchDir) Else Set Cell = .Find(sLookFor, .Cells(.Rows.Count, .Columns.Count), xlValues, iLookAt, iSearchOdr, iSearchDir) End If End With If Cell Is Nothing Then getItemLocation = 0 ElseIf Not (bFindRow) _ Then getItemLocation = Cell.Column Else getItemLocation = Cell.Row End If Set Cell = Nothing End Function