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
 

BNR

New Member
Joined
Jan 5, 2020
Messages
23
Office Version
  1. 2010
Platform
  1. Windows
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
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Watch MrExcel Video

Forum statistics

Threads
1,129,479
Messages
5,636,574
Members
416,925
Latest member
malamutus

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