Autofill using VBA

shellp

Board Regular
Joined
Jul 7, 2010
Messages
173
Office Version
  1. 2010
  2. 2007
Platform
  1. Windows
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.

VBA Code:
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
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,963
Office Version
  1. 365
Platform
  1. Windows
Try:
VBA Code:
'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

New Member
Joined
Jan 5, 2020
Messages
23
Office Version
  1. 2010
Platform
  1. Windows
Hi,

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

VBA Code:
'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,
VBA Code:
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

Board Regular
Joined
Jul 7, 2010
Messages
173
Office Version
  1. 2010
  2. 2007
Platform
  1. Windows
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

Board Regular
Joined
Jul 7, 2010
Messages
173
Office Version
  1. 2010
  2. 2007
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Feb 1, 2016
Messages
2,963
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Jul 7, 2010
Messages
173
Office Version
  1. 2010
  2. 2007
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Feb 1, 2016
Messages
2,963
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Jul 7, 2010
Messages
173
Office Version
  1. 2010
  2. 2007
Platform
  1. Windows
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

Well-known Member
Joined
Feb 1, 2016
Messages
2,963
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,132
Messages
5,640,310
Members
417,136
Latest member
reeton3

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
Top