Append to last row AND fill down

[GeeWhiz7]

Struggling with the following...

This snippet of code consecutively pastes values from Sheet4 after the last row of data in Sheet1 as I loop through things on Sheet4.

     addRow = Sheet1.Cells(Rows.Count, "A").End(xlUp).row
     
     Sheet4.Range("C2").Copy 'Run
     Sheet1.Range("A" & addRow + 1).PasteSpecial Paste:=xlPasteValues
     
     Sheet4.Range("C18:C23").Copy 'Date
     Sheet1.Range("B" & addRow + 1).PasteSpecial Paste:=xlPasteValues
     
     Sheet4.Range("B2:B7").Copy
     Sheet1.Range("C" & addRow + 1).PasteSpecial Paste:=xlPasteValues 'name
     
     Sheet4.Range("H196:H201").Copy
     Sheet1.Range("D" & addRow + 1).PasteSpecial Paste:=xlPasteValues  'Total

In order to feed a pivot table nicely, I would like to prearrange some of my data.

• I'm trying to figure out how to "fill down" certain values the rest of the way after I append the first ones using the snippet above.
• I have figured out how to make it fill down if there is only 1 run, but since I'm running a loop beforehand (=Run) on sheet 4, I have to use the addRow variable to make sure to append new runs to the bottom on this sheet and I can't find the syntax that works.

Example: This is what I do with the above snippet. Only the "Run" Column has one value and I want to fill down to the bottom. The bottom gets defined already by the other columns which bring in a range of data.

Book5
	A	B	C	D
1	Run	Date	Name	Total
2	B20	2/2/2022	Stainless	765.34
3		2/3/2022	Stainless	245.67
4		2/4/2022	Stainless	494.33
5		2/5/2022	Stainless	230.02
6		2/6/2022	Stainless	323.23
7		2/7/2022	Stainless	563.31
Sheet1

Desired Result:

Book5
	A	B	C	D
1	Run	Date	Name	Total
2	B20	2/2/2022	Stainless	765.34
3	B20	2/3/2022	Stainless	245.67
4	B20	2/4/2022	Stainless	494.33
5	B20	2/5/2022	Stainless	230.02
6	B20	2/6/2022	Stainless	323.23
7	B20	2/7/2022	Stainless	563.31
Sheet1

 

[gordsky]

Perfect, that helps me a lot.
The module is working by itself getting the data and filling down correctly, what is odd is that when I Call it from another macro, the rows don't fill down. I've added a few DoEvents prior to see if it was a system thing, but at least the part you've helped me with is working. Thank you

sounds like a referencing issue in your code. make sure you have fullyqualified the references
so this bit of code

Sheet1.Range(Cells(addRow + 1,1), Cells(lrow, 1)).FillDown

would need to be

Sheet1.Range(sheet1.Cells(addRow + 1,1), sheet1.Cells(lrow, 1)).FillDown

 

[GeeWhiz7]

sounds like a referencing issue in your code. make sure you have fullyqualified the references
so this bit of code

Sheet1.Range(Cells(addRow + 1,1), Cells(lrow, 1)).FillDown

would need to be

Sheet1.Range(sheet1.Cells(addRow + 1,1), sheet1.Cells(lrow, 1)).FillDown

wow that fixed it and at the same time may explain why elsewhere in my code I sometimes have loop results that aren't properly captured out. I guess I need to check those areas.
thank you again