+ Reply to Thread
Results 1 to 8 of 8

inserting rows with data from one worksheet to another worksheet

  1. #1
    Registered User
    Join Date
    03-11-2020
    Location
    Bloomington
    MS-Off Ver
    2018
    Posts
    22

    Question inserting rows with data from one worksheet to another worksheet

    Hi All!

    I'm not the best at explaining things so I'll try my best and include pictures.

    I have a question regarding VBA. I'm trying to add rows based on my "Domain Count" in column J with the data from columns D, E, & G in worksheet (Sheet1) into another worksheet (Sheet2).

    Excel Image 1.png
    Excel Image 2.png

    Even a macro to show me how to either:
    1) insert the number of rows based on 'Domain Count' under each header,
    2) copy and paste data under each specified header based on 'Control Objective'

    Thank you for your help!
    Attached Files Attached Files
    Last edited by excelhelp31120; 03-12-2020 at 10:13 AM.

  2. #2
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: inserting rows with data from one worksheet to another worksheet

    Administrative note

    Welcome to the forum

    in your haste to solve your problem, you probably missed the yellow banner advising how to get answers faster by posting a sheet ?

    Please take a moment to read it and attach a sheet accordingly.

    Thanks you for helping us help you

  3. #3
    Registered User
    Join Date
    03-11-2020
    Location
    Bloomington
    MS-Off Ver
    2018
    Posts
    22

    Re: inserting rows with data from one worksheet to another worksheet

    Thanks I was just having trouble adding an attachment.

  4. #4
    Registered User
    Join Date
    03-12-2020
    Location
    Louisiana
    MS-Off Ver
    Excel 2016
    Posts
    2

    Re: inserting rows with data from one worksheet to another worksheet

    Hope this helps!
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    03-12-2020
    Location
    Louisiana
    MS-Off Ver
    Excel 2016
    Posts
    2

    Re: inserting rows with data from one worksheet to another worksheet

    Whoops! I accidentally put one with a small bug. Did a quick fix and it works again!
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    03-11-2020
    Location
    Bloomington
    MS-Off Ver
    2018
    Posts
    22

    Re: inserting rows with data from one worksheet to another worksheet

    Hi ExcelHunterPaul!

    I keep getting an error when I put the VBA code into my original Excel file. I tried naming the tables in Sheet1, but I keep getting an error. Could you walk me through what you did if it's not too much to ask?

    Thank you!!
    Last edited by excelhelp31120; 03-12-2020 at 05:52 PM.

  7. #7
    Registered User
    Join Date
    03-13-2020
    Location
    Slovakia
    MS-Off Ver
    Office 365 ProPlus Version 1912
    Posts
    1

    Re: inserting rows with data from one worksheet to another worksheet

    Hello,

    in the module M_from_reddit, you can find a working solution. I also added Sheet1_backup and Sheet2_backup with the original data, just to be sure.

    I hope I managed to attach the file, but if not, here is the code:


    Sub add_domain_rows()

    Dim row_i As Long
    Dim row_j As Long
    Dim no_of_rows_to_get As Integer
    Dim additional_rows As Integer
    Dim rolling_start As Integer

    row_i = 3
    While ThisWorkbook.Sheets("Sheet2").Cells(row_i, "B").Value <> vbNullString

    'Check if there is a need to copy the data
    If Split(ThisWorkbook.Sheets("Sheet2").Cells(row_i, "B").Value, " ")(0) = "Domain" Then

    'Find how many rows need to be added
    row_j = 2
    rolling_start = 1
    While ThisWorkbook.Sheets("Sheet1").Cells(row_j, "F").Value <> vbNullString

    If ThisWorkbook.Sheets("Sheet1").Cells(row_j, "F").Value = Int(Split(ThisWorkbook.Sheets("Sheet2").Cells(row_i, "B").Value, " ")(1)) Then

    no_of_rows_to_get = ThisWorkbook.Sheets("Sheet1").Cells(row_j, "G").Value
    GoTo Exit_While_Because_I_found_the_number:

    End If

    'get the number of relevant row from top of the list
    'to know where to copy data from
    rolling_start = rolling_start + ThisWorkbook.Sheets("Sheet1").Cells(row_j, "G").Value

    row_j = row_j + 1
    Wend

    Exit_While_Because_I_found_the_number:

    'add the relevant number of blank rows
    For additional_rows = 1 To no_of_rows_to_get - 1

    ThisWorkbook.Sheets("Sheet2").Range("B" & (row_i + 1) & ":D" & (row_i + 1)).Insert shift:=xlDown

    Next additional_rows

    'copy/paste the relevant range
    ThisWorkbook.Sheets("Sheet1").Range("B" & (rolling_start + 1) & ":D" & (rolling_start + no_of_rows_to_get)).Copy (ThisWorkbook.Sheets("Sheet2").Range("B" & (row_i + 1)))



    End If

    'Jump to the next row
    row_i = row_i + 1 + no_of_rows_to_get

    Wend


    End Sub
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    03-11-2020
    Location
    Bloomington
    MS-Off Ver
    2018
    Posts
    22

    Re: inserting rows with data from one worksheet to another worksheet

    Thank you so much for all of your help Kerametal - It works!!!!

+ 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] Copy rows 1-10 from one worksheet into another worksheet inserting above a particular row
    By meldev in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-21-2014, 06:16 AM
  2. How to protect worksheet against inserting new rows
    By Candaloo in forum Excel General
    Replies: 3
    Last Post: 08-27-2014, 01:44 PM
  3. Help with inserting mulitple rows in worksheet
    By Nadia June in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-12-2013, 11:59 AM
  4. [SOLVED] INSERTING ROWS IN PROTECTED WORKSHEET
    By TLAngelo in forum Excel General
    Replies: 0
    Last Post: 07-18-2006, 01:20 PM
  5. Inserting rows, updating linked worksheet
    By Patti in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-20-2006, 05:30 PM
  6. [SOLVED] Inserting fillable Rows within a Protected Worksheet
    By dtmd in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 11-18-2005, 02:40 PM
  7. [SOLVED] Inserting Rows into a worksheet
    By Floyd Elkins in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-24-2005, 08:09 PM

Tags for this Thread

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