Excel Autofill VBO

[PaulWiatroski]

Hi, Has anyone created an Excel Autofill VBO?  I've been playing around trying to create one, but not having much luck getting it to work.  If someone has done this and can share the code, that would be great.  Thanks, Paul

[PaulWiatroski]

I was able to get my Autofill VBO to work. SourceRange and FillRange are passed in to the VBO.  See code below: Dim wb, ws, source_range, fill_range As Object Dim excel, sheet, range As Object Try wb = GetWorkbook(Handle, Workbook) ws = GetWorksheet(Handle, Workbook, Worksheet) wb.Activate() ws.Activate() excel = ws.Application sheet = excel.ActiveSheet   source_range = sheet.Range(SourceRange) fill_range = sheet.Range(FillRange) source_range.AutoFill(Destination:=fill_range) Success = True Catch e As Exception  Success = False  Message = e.Message Finally  wb = Nothing  ws = Nothing  excel = Nothing  sheet = Nothing  range = Nothing End Try

[nadiim]

This code is going to work only if the SourceRange is selected on the Excel sheet, otherwise it will give an error.
Add the 1 line code in bold to get it working and make sure there are values in the excel sheet.

source_range = sheet.Range(SourceRange)    
source_range.Select()
fill_range = sheet.Range(FillRange)

------------------------------
Nadiim Peerbocus
Assistant Manager
EY
Africa/Dar_es_Salaam
------------------------------

[AndreyKudinov]

Paste this as new action (tab) in Excel VBO:
https://pastebin.com/x0gg5dK9

Doesn't need anything to be selected etc, just give it an instance, workbook, worksheet, source range, optionally destination (uses CurrentRegion when blank), optionally xlAutoFillType

Edit to clarify: Not using original code, my own solution.
My point is that you don't need to activate anything, this should work just fine:

wb = GetWorkbook(Handle, Workbook)
ws = GetWorksheet(Handle, Workbook, Worksheet)
ws.Range(SourceRange).AutoFill(Destination:=ws.Range(FillRange))

My version has extra options:
- worksheet index instead of name (optional, either will do)
- ActiveSheet is used, when worksheet name and index are not set.
- blank dstRange, then it tries to guess it with CurrentRegion, which is not exactly what Excel does internally, but close and good enough for most cases
- xlAutoFillType, because sometimes xlFillDefault is not what you want.

------------------------------
Andrey Kudinov
Project Manager
MobileTelesystems PJSC
Europe/Moscow
------------------------------

[TomasKlimas1]

Thanks Andrey, I will try your solution

[ritansh.jatwani]

Hi Paul,

Please refer the below video for achieving the functionality:

https://www.youtube.com/watch?v=b_H-Zy_TJ4M

Hope it works for you.

------------------------------
Ritansh Jatwani
Consultant
EY
Asia/Kolkata
------------------------------

[AndreyKudinov]

@ritansh.jatwani, Excel Autofill does more than just repeating same cell value until the end of the table, look: https://www.excel-easy.com/examples/autofill.html

------------------------------
Andrey Kudinov
Project Manager
MobileTelesystems PJSC
Europe/Moscow
------------------------------

[ritansh.jatwani]

@AndreyKudinov- True, if we want to achieve the functionality as mentioned in  https://www.excel-easy.com/examples/autofill.html, then your code will work perfectly fine.

------------------------------
Ritansh Jatwani
Consultant
EY
Asia/Kolkata
------------------------------