Excel Worksheet to Auto fill using Macro

[Joey86]

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

 

[Joey86]

Actually reading that back I'll be a bit clearer, sorry about that Joe.
1) Ok so the sharepoint table on 'owssvr(1)', I can't have users see that so that will be hidden away
2) All users will be hitting the worksheet 'Main Menu' first and there is only 1 button (commandbutton1) on that page that will pretty much do the below:
- It currently will refresh the sharepoint data and reset my pivot slicers and set fields to the current year/month.
3) Once those have been done, I'm needing your VBA macro which will be on the 'Main Menu' page to point to 'owssvr(1)' and do its stuff

Hope that helps

 

[Joe4]

You can either post the code between the "Sub MyColumnFill()" and "End Sub" lines within your "Private Sub Commandbutton1_Click()" procedure, or you can simply leave the other one as its own macro all call it, i.e.

[COLOR=#333333]Private Sub Commandbutton1_Click()
   Call MyColumnFill
End Sub[/COLOR]

 

[Joey86]

That worked great thanks Joe, really appreciate your help.