Excel Worksheet to Auto fill using Macro

Joey86

New Member
Joined
Jul 25, 2013
Messages
32
Hi,

I'm fairly new to using VBA in excel, I have a table in worksheet owssvr(1) this is a data dump from the SharePoint connection I put from a custom list and I will be running the macro from a command button on the 'Main Menu' worksheet. I'm just needing the macro to do the following:

1) Refresh the data connection to get the latest data
2) In worksheet 'owssvr(1)' column "BL" auto fill down (so basically row BL2 = 1, BL3 = 2, BL4 = 3) but I only want it to fill the rows that have data because there will be new rows of data everytime the data is refreshed.

Thanks for anyone's help, really appreciate it.
Cheers
 

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,974
Office Version
  1. 365
Platform
  1. Windows
Welcome to the Board!

Regarding question 1, I haven't worked with Sharepoint, but this links looks like it might contain what you need: Dan's SharePoint Blog: How to Link Your SharePoint List to Excel!

Regarding question two, it looks like you are trying to populate column BL with a counter that is one less than the current row number (starts counting from 1 on row 2). Assuming when you say "Autofill", you are looking in column BK to determine how far down to go, this VBA code should do what you want:
Code:
Sub MyColumnFill()


    Dim myLastBKRow As Long
    Dim myLastBLRow As Long
    
'   Find last row in column BK
    myLastBKRow = Cells(Rows.Count, "BK").End(xlUp).Row
    
'   Find last row in column BL
    myLastBLRow = Cells(Rows.Count, "BL").End(xlUp).Row
    
'   Fill missing row in column BL with counter one less than row number
    If myLastBKRow > myLastBLRow Then
        Range(Cells(myLastBLRow + 1, "BL"), Cells(myLastBKRow, "BL")).Formula = "=Row() - 1"
    End If
    
End Sub
Hope that helps!
 

Joey86

New Member
Joined
Jul 25, 2013
Messages
32
Thanks for the quick response Joe, really appreciate it and thanks hopefully joining the forum i'll learn a lot more.

Well basically with the Sharepoint list I can explain it a bit better, its basically a custom list of data, in the options of the list I can export that data and column headers into excel as a table (owssvr(1)) which also has a direct connection to the list id and view guid in the excel data connections. So when the workbook is refreshed it retains the data thats there and pulls down the latest information from the list into the table, and you can use queries in excel like =countifs(Table_owssvr__1[[#All],[Customer]],'Results'!B4.

With the VBA I'm not actually needing it to autofill column 'BK', I'm just needing it to fill the formula down to any empty fields in the table when I refresh and get the latest info as shown below, it will need to be dynamic because there will always be new data coming in. 'RowNum' is BL :)

EDIT: Also am I able to attach the VBA code to a button so that when people hit it to refresh the data, not only does it pull down the latest info into the table but also adds it's row number?

Thanks

995449_10151711760351132_198755517_n.jpg
 
Last edited:

Joey86

New Member
Joined
Jul 25, 2013
Messages
32
I also tried the VBA code although its missing the blank cells and only going to the last row and inserting its formula in BL and then adding a new row below
969350_10151711851366132_1508513928_n.jpg
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,974
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

With the <acronym title="visual basic for applications" style="border-width: 0px 0px 1px; border-bottom-style: dotted; border-bottom-color: rgb(0, 0, 0); cursor: help; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">VBA</acronym> I'm not actually needing it to autofill column 'BK', I'm just needing it to fill the formula down to any empty fields in the table when I refresh and get the latest info as shown below, it will need to be dynamic because there will always be new data coming in. 'RowNum' is BL :)
That's pretty much what my code does!

Let's make sure we are on the same page here. Is this what you want to do?
- "Autofill" column BL with row number (which is starts counting with number 1 in Excel row #2)

If every row is not being populated, then I think that you may have some "gaps" in your data, or some rows in column BL are not actually blank (maybe they have a space in them). WE can get around all that if we just re-number all rows in column BL every single time (shouldn't hurt anything).

It also looks like your column BL is formatted as Text, so all the formulas are being pasted in as Text instead of formulas. We can get around that too. Here is updated code, along with the data refresh line for Sharepoint that they showed you how to do in that link I provided for you in my first post:
Code:
Sub MyColumnFill()

    Dim myLastBKRow As Long
    
'   Refresh Sharepoint data
    ActiveWorkbook.RefreshAll


'   Find last row in column BK
    myLastBKRow = Cells(Rows.Count, "BK").End(xlUp).Row
    
'   Reformat column BL to be numeric
    Columns("BL:BL").NumberFormat = "0"
    
'   Fill missing row in column BL with counter one less than row number
    If myLastBKRow > myLastBLRow Then
        Range(Cells(2, "BL"), Cells(myLastBKRow, "BL")).Formula = "=Row() - 1"
    End If
    
End Sub
 
Last edited:

Joey86

New Member
Joined
Jul 25, 2013
Messages
32
That works perfect, thanks so much Joe, really appreciate your help on this. One last question, on another sheet 'Main Menu' I've added a button which has the refresh option (thanks to the link you posted) to refresh the data from sharepoint, how do I add your VBA to that button so while the data refreshes in (owssvr(1)) worksheet and puts in new rows of information that when its completed it runs your VBA to add the row numbers.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,974
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Just add the following line of code to Select/Activate the sheet you wish to apply it to. Add it just before the "Refresh" line.
Code:
'   Refresh Sharepoint data 
[B]    Sheets("[COLOR=#333333]owssvr(1)").Activate[/COLOR][/B]
    ActiveWorkbook.RefreshAll
 

Joey86

New Member
Joined
Jul 25, 2013
Messages
32
Oh no not the sharepoint refresh part the Sub MyColumnFill() part, I'm needing to add that to the button (Commandbutton1) so that when clicked it refreshes the data and does the column fill part.

Thanks
:)
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,974
Office Version
  1. 365
Platform
  1. Windows
I am afraid I am not sure what you are asking. Do you not know how to assign a macro to a Command Button?
 

Joey86

New Member
Joined
Jul 25, 2013
Messages
32
Well sort of, I've tried a couple of times using (Private Sub Commandbutton1_Click()) but you have (Sub MyColumnFill()) and when I add MyColumnFill() it doesn't seem to work
 

Watch MrExcel Video

Forum statistics

Threads
1,123,295
Messages
5,600,789
Members
414,405
Latest member
Zaurb

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