1

I am quite new to VBA, I am making a macro where it copies data from 'Sample' workbook to 'Etracker' workbook. After copying cell "H11" from 'Sample' to the last row of C column in Etracker, I want C column to autofill down to until where data is in J column. the code works but where it says "C16926" now, it changes every time because I constantly entering data into it. so I want to autofill it from last row in column C all the time. I tried different ways to change "C16926" to vary but it doesn't seem to work. Please help! and thanks in advance.

LastrowC = Etracker.Cells(Rows.Count, 3).End(xlUp).Offset(1, 0).Row
LastrowJ = Etracker.Cells(Rows.Count, 10).End(xlUp).Offset(1, 0).Row

Sample.Activate
Range("H11").Copy
Etracker.Cells(LastrowC, 3).PasteSpecial xlPasteValues
Etracker.Activate
Etracker.Range("C" & LastrowC).Select
Selection.AutoFill Destination:=Range("C16926:C" & Range("J" & Rows.Count).End(xlUp).Row), Type:=xlFillCopy
Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
NorwegianLatte
  • 89
  • 2
  • 9
  • you need to explain what you expect `C16926` to be? We cannot know to what it should change to if you don't tell us. • You might benefit from reading [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). – Pᴇʜ Apr 17 '20 at 08:08
  • I want to change "C16926" to be the last data in column C. in other words, I want this macro to select the last data in C column and autofill to where the last data is in J column. – NorwegianLatte Apr 17 '20 at 08:18

1 Answers1

0

You just need to concatenate your last row into the address like 

"C" & LastrowC & ":C" & Range("J" & Rows.Count).End(xlUp).Row)

Note that you don't need to .Select or .Activate if you specify a worksheet for every Range, Cells, Rows and Columns object. Using .Select or .Activate is very unreliable and makes your code really slow.

So you should end up with something like

Dim LastRowC As Long
LastrowC = Etracker.Cells(Etracker.Rows.Count, "C").End(xlUp).Offset(1, 0).Row

Dim LastrowJ As Long
LastrowJ = Etracker.Cells(Etracker.Rows.Count, "J").End(xlUp).Offset(1, 0).Row

Sample.Range("H11").Copy
Etracker.Cells(LastrowC, "C").PasteSpecial xlPasteValues

Etracker.Range("C" & LastrowC).AutoFill Destination:=Etracker.Range("C" & LastrowC & ":C" & LastrowJ), Type:=xlFillCopy
Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
  • I am using LastrowJ to find where lastrow is at when I copy stuff from 'sample' to J column in 'Etracker' and then after copying J column, I want to autofill column C to where last data is at in J column. I can see how it will work however, when I use "C" & LastrowC & ":C" & Range("J" & Rows.Count).End(xlUp).Row) , for some reason, Range("J" & Rows.Count).End(xlUp).Row grabs ramdom row 23, would you know why it would do that? thanks – NorwegianLatte Apr 17 '20 at 11:14
  • To add: I realized Range("J" & Rows.Count).End(xlUp).Row changes and grabs the last cell from 'sample' worksheet when I am executing to copy a data from that sheet – NorwegianLatte Apr 17 '20 at 11:19
  • Because if you just use `Range` then Excel cannot know in which sheet to look and it guesses. So you need always to specify a worksheet (as I have written in my second sentence of my answer). If you use `Etracker.Range` then Excel know to look in worksheet `Etracker` for that range. Make sure you always tell VBA which sheet the range is in. Otherwise you might get random results. – Pᴇʜ Apr 17 '20 at 11:21
  • I see, now it works very well, Thank you so much for your help. one more thing to ask, when concatenating ranges, not sure when to use " ", Would you be able to clarify that part? – NorwegianLatte Apr 17 '20 at 11:38
  • Not really sure what exactly you mean, but everything that is text/string must be enclosed in `" "` everything that should be considered as a variable/object must be without them. – Pᴇʜ Apr 17 '20 at 11:40