VBA to insert rows based on cells column in another sheet (same workbook)

[Bond00]

So what I need is to detect if there is anything in Price Work Sheet in E10 to E113
Keep in mind there could be some blank cells mixed in. So it could look like this
E10-E17 in this example:

I need to be able to scan all the cells in this "Price Work Sheet" for any items in this column and on the 2nd Sheet "Finaloutput" Starting below B17 I need it to auto insert rows and copy the text from the other sheet into this sheet starting with B18 (in the B column) as it creates rows for each of them. (I want it to copy the empty space rows as well)

 

[Bond00]

So i came across this, and it does add the new row but cant figure out how to apply it to check another sheet or also bring over the text from the given cell like i describe above.
Can someone with more knowledge expand on this to fit my needs above?

Option Explicit

Sub InsertBlankRows()

'insert blank rows when value in column "A" changes

Dim Col As Long
'Dim Col As Variant
Dim LastRow As Long
Dim StartRow As Long
Dim i As Long

Col = 1 'which is column "A"
'Col = "A" 'if declared as variant e.g "A"

StartRow = 17
LastRow = Cells(Rows.Count, Col).End(xlUp).Row

Application.ScreenUpdating = False

For i = LastRow To StartRow + 1 Step -1
If Cells(i, Col) <> "" Then
Cells(i, Col).EntireRow.Insert Shift:=xlDown '1 row
'Range(Cells(i, Col).EntireRow, Cells(i + 1, Col).EntireRow).Insert Shift:=xlDown '2 rows
End If
Cells(i, Col) = "Y"
Next i

Application.ScreenUpdating = True

End Sub

 

[Bond00]

Ok here is the demo file im using. Nevermind, not sure how to upload the doc in here so i'll post pics.

right now its not really doing what i need. On sheet2 aka Finaloutput I scan the column A starting at A18 and it inserts new row after each cell with something in it. Then adds a row and text from sheet1 but not sure how to make it shift down, it always just repeats E10

Price Work Sheet (Sheet1)_________________________________Finaloutput (Sheet2)

Option Explicit

Sub InsertBlankRows()

'insert blank rows when value in column "A" changes

Dim Col As Long
'Dim Col As Variant
Dim LastRow As Long
Dim StartRow As Long
Dim i As Long

' on 1 = A for testing
Col = 1 ' 5 which is column "E"
'Col = "E" 'if declared as variant e.g "E"

StartRow = 18
LastRow = Cells(Rows.Count, Col).End(xlUp).Row

Application.ScreenUpdating = False

For i = LastRow To StartRow + 1 Step -1
If Cells(i, Col) <> "" Then
Cells(i, Col).EntireRow.Insert Shift:=xlDown '1 row
'Range(Cells(i, Col).EntireRow, Cells(i + 1, Col).EntireRow).Insert Shift:=xlDown '2 rows
End If
Cells(i, Col) = ['Price Work Sheet'!E10]
Next i

Application.ScreenUpdating = True

End Sub

 

[Joe4]

Bond00,

I still do not fully follow how you want this to work (and it may be why you have not gotten any replies yet).

Can you please walk us through an example?
The best way to do that is to do the following:
1. Show us what your two sheets look like initially
2. Show us what your expected output should look like AFTER it is done
3. If this is an ongoing process (not a one time fix), then proceed to show us another change to your original data, and how that should affect your final result.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Bond00]

Ok this is what the sheets look like and the 3rd pic is what sheet 2 should look like when its done.

I just want to take the data on sheet 1 from E10:E113 only the cells with something in them (and the empty ones between for spacing)
and then put that data into sheet 2 on B17 on down and insert new rows for each cell brought over.

thats pretty much it.

 

[Joe4]

Sorry, I was feeling under the weather and was off-line most of the day yesterday.

A few questions:

1. On your "Finaloutput what I want todo" sheet, is there anything underneath what you show there? I am just wondering because if there isn't, there is really no need to insert blank lines before copying the information over. You would only need to insert blank lines first if something already exists below the range you are copying to that you do not want to lose.

2. Is this just a one-time thing that you are running an existing data, or will you be adding data to this sheet and have to run the macro multiple times (every time you enter data)?

3. If this is something that you will need to run multiple times, how is the data "Price Work Sheet" being added? Is information manually being entered into column E? If so, we can probably make the VBA code run automatically upon entry of new data.

4. Should the data on the "Price Work Sheet" and your "Finaloutput" always match, or might the data on your "Finaloutput" sheet be updated/changed after it is copied, so the two sheets are no longer in synch anymore?

 

[Joe4]

Also note that you may be able to do all of this without any VBA.
If your goal is to just keep these two worksheets "synched" up, you can do that with a simple formula.
Just place this formula in cell B17 on your "Finaloutput" sheet, and copy the formula all the way down to cell B120:

=IF('Price Work Sheet'!E10="","",'Price Work Sheet'!E10)

 

[Bond00]

This is the doc, it might be easier to just take a look and work within it to see what its doing. https://cdn.discordapp.com/attachme...98e6fbcafa2b0d5c7f59046bdf2b29226a3583616884&

1. yeah the test in that box stays below, so it needs to be inserted rows to keep the spaces for it.
2. yeah its possible a change or add may be made so i plan to push a button to basically generate that output. but its more likely i just do it at the very end 1 time only. If not i can just delete all the rows manually if needed because the amount of times i would have to run it more than once is probably low. Otherwise the code would need to first scan how many lines there is before that given output on the cells below and delete them all first i would guess?
3. yeah its all added manually on "Price Work Sheet"
4. this is possible something is added manually, but unlikely/would be rare.

If your goal is to just keep these two worksheets "synched" up, you can do that with a simple formula.
Just place this formula in cell B17 on your "Finaloutput" sheet, and copy the formula all the way down to cell B120:

This could work but then i'd still have to manually add all the rows each time. so vba would probably be needed for that part still.

This is showing a bit more at the bottom on sheet 3 to show what i want the final to be.

 

[Joe4]

I cannot download files from my current location, so I will need to wait until later today or tomorrow when I am at a computer where I can.
But just to confirm, which sheet are you actually needing to insert new rows on? The "Price Work Sheet" or "Finaloutput" sheet?

On the "Price Work Sheet", are you just typing values in, or do you need to insert a row before typing values in?

On the "Finaloutput" sheet, initially where does the text that already resides that should be below all the stuff you are inserting reside? What row does that stuff start on?

 

[Bond00]

Finaloutput is the sheet needing rows inserted on.
The price work sheet is always static how it is and i just type values in. But its random which rows will have values in them (can be spaces in-between) so the loop needs to cycle through and see which row cells have anything typed in them. No i take that back, it only needs to find the last bottom cell that has anything typed in it in "Price Work Sheet" and then copy over and insert a new row for each of those in "Finaloutput" sheet.
So lets say E35 is the last cell that has any date then just copy over E10:E35 and insert 26 rows. well 25 since the top row in Finaloutput will always be there starting out.
more or less i just need to copy over that data and insert rows on the other sheet as its copied.