+ Reply to Thread
Results 1 to 3 of 3

VBA / MACRO to insert new row

Hybrid View

ipandabear VBA / MACRO to insert new row 06-24-2014, 11:08 AM
rcm Re: VBA / MACRO to insert new... 06-24-2014, 11:33 AM
GeneralDisarray Re: VBA / MACRO to insert new... 06-24-2014, 11:42 AM
  1. #1
    Registered User
    Join Date
    06-12-2014
    Posts
    4

    VBA / MACRO to insert new row

    I have 2 worksheets, if B2 in worksheet 1 matches with anything in column A in worksheet 2, I want to insert that row underneath the row in worksheet 1. and so on.
    I have insert a demo with 3 worksheets.

    Thanks
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    11-28-2012
    Location
    Guatemala
    MS-Off Ver
    Excel 2010
    Posts
    2,394

    Re: VBA / MACRO to insert new row

    Sub total()
    r = 2
    Sheets(3).Range("a:d").ClearContents
    While Sheets(1).Cells(r, 1) <> ""
    For j = 1 To 3
      Sheets(3).Cells(r, j) = Sheets(1).Cells(r, j)
    Next j
    r = r + 1
    Wend
    
    r2 = 2
    While Sheets(2).Cells(r2, 1) <> ""
    Set c = Sheets(3).Range("a:a").Find(Sheets(2).Cells(r2, 1), LookIn:=xlValues)
    If c Is Nothing Then
    rx = r
    r = r + 1
    Else
    c3 = c.Row
    Sheets(3).Range(c.Row + 1 & ":" & c.Row + 1).Insert Shift:=xlShiftDown
    rx = c.Row + 1
    End If
    For j = 1 To 3
      Sheets(3).Cells(rx, j) = Sheets(2).Cells(r2, j)
    Next j
    
    r2 = r2 + 1
    Wend
    
    
    
    End Sub

  3. #3
    Forum Expert GeneralDisarray's Avatar
    Join Date
    09-15-2011
    Location
    Pittsburgh, PA, USA
    MS-Off Ver
    Windows Excel 2016
    Posts
    1,416

    Re: VBA / MACRO to insert new row

    Try this, you would need to alter the "targetSheet"

    Edit: By the way, this would append to the target... not clear it out first.

    Option Explicit
    
    Sub copy_example()
    'big ups to the general!'
    Dim targetSheet As Worksheet
    
    Set targetSheet = ThisWorkbook.Worksheets("generalDisarray")
    
    Call copySingleSource(ThisWorkbook.Worksheets("test1"), targetSheet, 1, 1, 3)
    Call copySingleSource(ThisWorkbook.Worksheets("test2"), targetSheet, 1, 1, 3)
    
    targetSheet.UsedRange.Sort Key1:=targetSheet.Cells(2, 1).Resize(targetSheet.Cells(2, 1).End(xlDown).Row - 1, 3), Order1:=xlAscending, Header:=xlYes
    
    End Sub
    
    Sub copySingleSource(ByRef sourceS As Worksheet, ByRef targetS As Worksheet, firstRow As Long, firstColumn As Long, tableWidth As Long)
    targetS.Cells(Rows.Count, firstColumn).End(xlUp).Offset(1, 0).Resize( _
        sourceS.Cells(Rows.Count, firstColumn).End(xlUp).Row - 1, tableWidth).Value = _
        sourceS.Cells(firstRow, firstColumn).Offset(1, 0).Resize( _
        sourceS.Cells(Rows.Count, firstColumn).End(xlUp).Row - 1, tableWidth).Value
    
    End Sub
    Attached Files Attached Files
    Remember, saying thanks only takes a second or two. Click the star icon(*) below the post you liked, to give some Rep if you think an answer deserves it.

    Please,mark your thread [SOLVED] if you received your answer.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Macro to insert row if cell(i,1) notequal cell(i,1)+1 and insert certain values in row.
    By mareike in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-16-2013, 03:29 AM
  2. [SOLVED] Macro to copy row, insert below, invert amt, and insert blank row between each new tx
    By raquel1227 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 03-07-2013, 12:56 PM
  3. Insert blank row, sum and insert page break macro
    By kim5012 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 11-06-2012, 01:46 AM
  4. Macro: Copy Row and Insert it ; Insert Odd Year
    By buto87 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 04-25-2012, 08:55 AM
  5. [SOLVED] Macro to insert copy and insert formulas only to next blank row
    By bob in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-30-2006, 07:10 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1