Autofill using VBA

shellp

Board Regular
Joined
Jul 7, 2010
Messages
194
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
 
Hi,

Perhaps define your target worksheet as a specific variable,


VBA Code:
     Dim sWS as worksheet
     set sWS = ThisWorkBook.Sheets("rawdata") ' better make sure this sheet exists first otherwise it will error
     sWS.Range("M2:R2").AutoFill Destination:=sWS.Range("M2:R" & Cells(Rows.Count, "L").End(xlUp).Row

Is this what you're after?
regards,
BenR
 
Upvote 0

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

Forum statistics

Threads
1,213,494
Messages
6,113,972
Members
448,537
Latest member
Et_Cetera

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