Autofill using VBA

[shellp]

Hello

I am using Excel 2010 and I have a program from another site that I tweaked to import a .csv file and add it to the next available row. This works great.

However, columns M to R have formulae that I would like to copy down for all rows of data. The code that I have (the last line) works if I am in the worksheet of "rawdata" but not if I run the macro from elsewhere. I am sure this is something stupid I'm not seeing but would appreciate any and all assistance. Thanks.

Sub append_csv_file()
Dim csvfilename As Variant
Dim destcell As Range

Set destcell = Worksheets("RawData").Cells(Rows.Count, "A").End(xlUp).Offset(1)

csvfilename = Application.GetOpenFilename(FileFilter:="CSV Files (*.csv),*.csv", Title:="Select a CSV File", MultiSelect:=False)
If csvfilename = False Then Exit Sub

With destcell.Parent.QueryTables.Add(Connection:="Text;" & csvfilename, Destination:=destcell)
.TextFileStartRow = 2
.TextFileParseType = xlDelimited
.TextFileCommaDelimiter = True
.TextFileColumnDataTypes = Array(5, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False

End With

destcell.Parent.QueryTables(1).Delete

'this is correct but only if in rawdata worksheet - how to use running the macro from another worksheet?
Range("M2:R2").AutoFill Destination:=Range("M2:R" & Cells(Rows.Count, "L").End(xlUp).Row)

destcell.Parent.QueryTables(1).Delete
End Sub

 

[Akuini]

Try:

'this is correct but only if in rawdata worksheet - how to use running the macro from another worksheet?
With Worksheets("RawData")
.Range("M2:R2").AutoFill Destination:=.Range("M2:R" & .Cells(.Rows.count, "L").End(xlUp).Row)
End With

 

[BNR]

Hi,

I could be wrong here but I think the following line,

'this is correct but only if in rawdata worksheet - how to use running the macro from another worksheet?
Range("M2:R2").AutoFill Destination:=Range("M2:R" & Cells(Rows.Count, "L").End(xlUp).Row)

The code mentions 'Range("M2:R2")...' which is a relative address as opposed to something like,

Thisworkbook.Sheets("Sheet1").Range("M2:R2")....

which is a specific range on a specific sheet...

Akuini's response also shows a specific range address (including worksheet name).

 

[shellp]

Thank you both for your responses...but still not working. Both suggestions resulted with a run-time error 1004: "AutoFill method of range class failed".

I was thinking that it was related to citing the actual worksheet and tried a few things before posting here...this is baffling. But I really appreciate your assistance, thanks.

 

[shellp]

I thought I would write a macro just for the autofill to see if I can figure this out and I wrote it like above with distinct setting of the worksheet. When I run from the other worksheet ("stats"), I don't get an error message but nothing happens. Only when I run it from the rawdata worksheet does it work correctly. I'm sure there is an easy solution but I'm missing it.

 

[Akuini]

Only when I run it from the rawdata worksheet does it work correctly.

What do you mean by "run it from the rawdata worksheet"? Do you mean you put the code in
rawdata worksheet code module (not in the regular module)?

 

[shellp]

No I mean that it is a macro and when on the worksheet "rawdata" I select macro, run and it does as expected i.e. imports the csv file and then autofills the range. If I assign the macro to a button on the worksheet called "Stats" and run it by pushing the button on the Stats worksheet, the data imports but the autofill portion doesn't work.

The same with the original, I can leave that piece of code commented out and if I'm on the rawdata worksheet and select the macro to run, all works well.

 

[Akuini]

It doesn't clear to me, where is the macro located? in regular module?
If yes, then you need to amend the code to work on the activesheet.
Change this part:
Set destcell = Worksheets("RawData").Cells(Rows.Count, "A").End(xlUp).Offset(1)
to this:
Set destcell = ActiveSheet.Cells(Rows.count, "A").End(xlUp).Offset(1)

and keep this part:
'this is correct but only if in rawdata worksheet - how to use running the macro from another worksheet? Range("M2:R2").AutoFill Destination:=Range("M2:R" & Cells(Rows.Count, "L").End(xlUp).Row)

If it still doesn't work then please upload your workbook (without sensitive data) to a free site (such as dropbox.com or google drive) then put the link here.

 

[shellp]

Hello

Thanks for keeping with me on this.

The code I provided is in a module. The code is for importing .csv file to a worksheet called rawdata. I have added 5 formula columns on the rawdata worksheet and I need those cells to be copied down as new data is added.

I put a button on a worksheet called "Stats" and assigned the above macro to it. When I select the button, the csv file successfully imports but the 5 columns do not autofill and I get no error messages. However, if I am in the RawData worksheet and select the macro via the developer tab (i.e. not using a button with the macro assigned) I get the csv successfully imported AND the columns autofill as designed.

I do not want the activesheet to be used because that would be the stats worksheet where the button resides and I want the activesheet to be RawData. Unless I am misinterpreting your suggestion?

 

[Akuini]

In that case your original code in post 1 should work.
When you run it from sheet Stats, then this line: Range("M2:R2").AutoFill Destination:=Range("M2:R" & Cells(Rows.Count, "L").End(xlUp).Row)
refers to sheet Stats. So I don't understand why it doesn't work.