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.
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