Append to last row AND fill down

GeeWhiz7

Board Regular
Joined
Nov 22, 2021
Messages
214
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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.

VBA Code:
     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
ABCD
1RunDateNameTotal
2B202/2/2022Stainless765.34
32/3/2022Stainless245.67
42/4/2022Stainless494.33
52/5/2022Stainless230.02
62/6/2022Stainless323.23
72/7/2022Stainless563.31
Sheet1


Desired Result:
Book5
ABCD
1RunDateNameTotal
2B202/2/2022Stainless765.34
3B202/3/2022Stainless245.67
4B202/4/2022Stainless494.33
5B202/5/2022Stainless230.02
6B202/6/2022Stainless323.23
7B202/7/2022Stainless563.31
Sheet1
 
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
Excel Formula:
Sheet1.Range(Cells(addRow + 1,1), Cells(lrow, 1)).FillDown

would need to be
Excel Formula:
Sheet1.Range(sheet1.Cells(addRow + 1,1), sheet1.Cells(lrow, 1)).FillDown
 
Upvote 0

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
sounds like a referencing issue in your code. make sure you have fullyqualified the references
so this bit of code
Excel Formula:
Sheet1.Range(Cells(addRow + 1,1), Cells(lrow, 1)).FillDown

would need to be
Excel Formula:
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
 
Upvote 0

Forum statistics

Threads
1,223,231
Messages
6,170,884
Members
452,364
Latest member
springate

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top