Hoping you can help with the below, I've no idea why this isn't working and I can't seem to figure it out. After some Googling, I can't even find another example of this issue.
Essentially the code should take data on one page, place HLOOKUPS in another page to sort everything into the right columns (all working fine). Then once that's done, it should auto fill down using the row count.
The problem I have, is that it is auto filling on the wrong sheet (it might be worth calling out that the sheet it fills is the same one the code is in and where the rowcnt is.
I tried to explicitly call out the sheet I want to use as such: Range("A2:V2").AutoFill Destination:=Sheets(5).Range("A3:V" & rowcnt), Type:=xlFillDefault but this then throws an Application-defined or object-defined error on the fill line of code.
Public Sub FormatData()
rowcnt = Application.WorksheetFunction.CountA(Sheet4.Range("B:B")) + 1
With Sheets("Final Datasets")
.Cells(2, "A").FormulaR1C1 = _
"=HLOOKUP(""oOrder_date"",'Teradata Downloads'!R1:R1048576,ROW('Final Datasets'!RC),0)"
[snip] load more of the same as above [/snip]
'FILL
Range("A2:V2").AutoFill Destination:=Range("A3:V" & rowcnt), Type:=xlFillDefault
End With
End Sub
I'm properly puzzled here, so any help you can give as to how to fix this (and more importantly, why it's happening) would be greatly appreciated.
if you have the AutoFill in the With Statement, try to use the "." before the Range.
Also, the destination must include the source range.
.Range("A2:V2").AutoFill Destination:=.Range("A2:V" & rowcnt)