-1

I have one sheet (sheet2) that is connected to a CSV file, which has dynamic data. I copy the data by applying a column filter, and then paste it to sheet1 so it can be formatted/displayed a certain way with a macro. I want to be able to insert the data between two existing rows on sheet1 and have the formulas in those rows automatically update to the dynamic ranges.

So basically...

ExistingRow1

Blank Row <- insert data here, having it expand down 

ExistingRow2 | formula <- formulas should update to include new rows from sheet2
Uwe Keim
  • 39,551
  • 56
  • 175
  • 291
deceptionx
  • 11
  • 1

1 Answers1

0

The code below will do what you describe, and the animated .gif demonstrates it working. Notice that you can see what the formulas being used are in the formula bar, and then a selection on Sheet 2 is copied over to Sheet1 where the rows shift as needed.

enter image description here

Sub insertRows()
Dim sh2 As Worksheet, sh1 As Worksheet
Dim r1 As Range, r2 As Range, i As Integer
Set sh1 = Worksheets("Sheet1")
Set sh2 = Worksheets("Sheet2")
sh2.Activate
Set r2 = Application.InputBox("select rows to copy on Sheet2", , , Type:=8)
sh1.Activate
Set r1 = Application.InputBox("select blank row on Sheet1", , , Type:=8)
For i = 1 To r2.Rows.Count - 1
  r1.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Next i
r2.Copy
r1.Offset(-r2.Rows.Count + 1, 0).Select
sh1.Paste
End Sub

Although this is what you describe, it's a bit of an awkward way to do things and if more is known about where your data will be, etc... then a more elegant approach could be used.

Tony M
  • 1,694
  • 2
  • 17
  • 33
  • This is close to what I want. I need to be able to do it without an input box. It should grab the data from sheet2 and paste it to sheet1 with a macro that runs on file open. The problem I'm having is compensating for the fact that the data I copy on sheet2 will not always be the same size, hence needing the rows to be able to expand. – deceptionx Aug 29 '17 at 05:37
  • I agree that it would be better to do without the input boxes. But in order to expand the rows properly on sheet1, you need to know where are you begin copying from and how many rows are being copied over. If you have a way of knowing that you will be able to avoid the input boxes, and you'll be able to position the range to be copied and also adjust the for-next loop. I hope this is been helpful and that you can pick up the ball from here – Tony M Aug 29 '17 at 05:44
  • I figured out how to define the "r2" for my macro, but if I wanted to define my "r1" as the first blank row in my sheet1, how would I do that instead? I'd still need it to expand with my defined range. Thanks for your help so far! – deceptionx Aug 29 '17 at 07:38
  • Set r = Range("A1").end(xlDown).Offset(1,0) is one way – Tony M Aug 29 '17 at 07:41
  • It's almost working! It's pasting the data into the first blank cell instead of the first entirely blank row.It's also not expanding the rows all the way across, only the columns I'm defining as my range. – deceptionx Aug 29 '17 at 08:08
  • Glad you've been helped. In the future, I suggest (1) providing a clearer definition of your problem. As stated, only a general answer can be given and I believe that is why your question got voted down, (2) focus on defining the range of cells you need to act on. Here is one link that does this https://stackoverflow.com/questions/45288516/transposing-sets-of-columns-on-top-of-each-other-in-excel/45289644#45289644 even though it's quite different from your situation, (3) Use the macro recorder to learn how to act on those ranges, (4) help those who help you by voting on their answers/comments. – Tony M Aug 29 '17 at 15:31