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]

does the value in the run column remain constant or change with each new loop

 

[GeeWhiz7]

does the value in the run column remain constant or change with each new loop

It changes with each new loop. Sometimes it can be repeated after a few dozen iterations, but it is generally different each loop i to i+1.

 

[gordsky]

It changes with each new loop. Sometimes it can be repeated after a few dozen iterations, but it is generally different each loop i to i+1.

could you not redifine your last row in column B for example and then fill to there before your next loop? Something like

lrow = sheet1.Cells(Rows.count, 2).End(xlUp).Row
Sheet1.Range(Cells(addRow + 1,1), Cells(lrow, 1)).FillDown

 

[GeeWhiz7]

could you not redifine your last row in column B for example and then fill to there before your next loop?

That's a good idea. I'm away from excel, but maybe

     addRow = Sheet1.Cells(Rows.Count, "A").End(xlUp).rows
     
     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

'Add new last row variable
     newLastRow = Sheet1.Cells(Rows.Count, "B").End(xlUp).rows
     
     Sheet4.Range("C2").Copy 'Run
     Sheet1.Range("A" & addRow + 1).PasteSpecial Paste:=xlPasteValues
     Range("A" & addRow).Select
     
'**** ITS THIS PART I don't know how to get right syntax a I get an error 
     Selection.AutoFill Destination:=Range("A" & addRow : "A" & newLastRow)

 

[gordsky]

the code i posted above should work. Leave your existing code as it was and then insert the two lines I gave you above after where you copy H to D

 

[GeeWhiz7]

the code i posted above should work. Leave your existing code as it was and then insert the two lines I gave you above after where you copy H to D

sorry, I didn't see the code earlier on my phone. Let me try inserting when I am back at my desk and will revert tomorrow. Thanks you for helping me

 

[GeeWhiz7]

sorry, I didn't see the code earlier on my phone. Let me try inserting when I am back at my desk and will revert tomorrow. Thanks you for helping me

Thank you gordsky, this works nicely. So if I understand correctly

'***This counts back to the last filled row in column 2 and assigns to lrow
lrow = sheet1.Cells(Rows.count, 2).End(xlUp).Row

'***This takes the range from row addRow+1 and column 1 and fills the values down to row lrow, column 1.
'***I'm not quite sure I understand the syntax of this one, particularly the second Cells instance.  
'***Lets say I wanted to fill down the second column, would it be Sheet1.Range(Cells(addRow + 1,2), Cells(lrow, 2)).FillDown ?
Sheet1.Range(Cells(addRow + 1,1), Cells(lrow, 1)).FillDown

 

[gordsky]

yes it would. When cells is used its cells(row, column) but you have to combine ti wth range to make an actual range. So in the second example its effectively saying Range("A"&addrow+1 &":"& "A" & lrow).

so in this example
Sheet1.Range(Cells(addRow + 1,1), Cells(lrow, 1)).FillDown

Cells(addrow+1,1) would be range("a" & addrow+1)
Cells(lrow, 1) would be range("A"&lrow)

your then just combining them to make your range

 

[GeeWhiz7]

yes it would. When cells is used its cells(row, column) but you have to combine ti wth range to make an actual range. So in the second example its effectively saying Range("A"&addrow+1 &":"& "A" & lrow).

so in this example
Sheet1.Range(Cells(addRow + 1,1), Cells(lrow, 1)).FillDown

Cells(addrow+1,1) would be range("a" & addrow+1)
Cells(lrow, 1) would be range("A"&lrow)

your then just combining them to make your range

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