Fill down and fill right variable number of rows/columns

[Jeffrey Lebowski]

Hi,


I have a list of data that will be changing. This list can span multiple months/years.


I wanted to produce a "top 10 list" from this data. I can do this manually, through use of large formula but I want vba to be able to do this without referring to specific ranges. I am not too familiar with how to use relative references, and I think that is all there is.


So 2 columns to the right of the block of data, I want top 10 values for each month of data.

 

[Smallman]

Hi jeff


Yep, very possible, very quickly. Can you upload a sample of you data.


Thanks


Smallman

 

[Jeffrey Lebowski]

sorry that does not do a good job of laying out the problem. please see sample workbook

https://www.dropbox.com/s/r7ih11dm4oumuc7/vba%20filldown%20example.xlsm

 

[jeffreyweir]

Note that Pivot Table Filters have a 'Top 10' feature baked right in.

 

[Smallman]

Hi Jeff L


I am now in a place which will allow me to download a file. I can view it this evening if you don’t have an appropriate solution. Have a play around with Jeff W’s pivot table suggestion.


Take care


Smallman

 

[Jeffrey Lebowski]

Thanks for your help guys. Smallman I would appreciate a followup as I still do not know how to reference the changing starting point and ranges when writing vba.

 

[Smallman]

Hi Jeff L


I have had a look at your file and I can’t see why you would want to use vba on a monthly basis to update this file. It seems you have your top 10 feeding through and all you have to do is pull your formula across each month and your top 10 updates.


I would change the formula in Cell K13 to the following, I don’t think you are producing the right results in your green line and this fixes that problem.


=VLOOKUP($J$13,$A$3:B$17,1+COLUMNS($K$13:K13),0)


Sometimes vba can be overkill I would not recommend using it to drag one col across every month. Unless there was something that I missed here? Let the group know.


Take care


Smallman

 

[Jeffrey Lebowski]

Smallman,


The reason I want to use vba here is because this whole sheet is populated by vba, pulling from the data sheet. So it is not like another column gets added to existing data, but rather the sheet is re-populated with this additional column. Hence the non-static starting point for the top 10 table.


I hope that make more sense. It may not be the best method for this, and I am open to alternate suggestions! This is the way I have come up with so that all of this data can be updated and the graphs that pull from here will also reflect the update as it happens.


Thanks for catching that formula error. Let me know if I can clarify the problem better.

 

[Smallman]

Hi Jeff L


I can't invision a situation where a whole report is generated by vb and there is not a template doing some of the grunt work for you. The following is what you were trying to nut out in the back end (i think).


As it is a top 10 I would imagine the start and end point (rows) are locked in (3 and 13).

[pre]

Sub CopyIt()
Range(Cells(3, Columns.Count).End(xlToLeft), Cells(13, Columns.Count).End(xlToLeft)).Copy
Cells(3, Columns.Count).End(xlToLeft).Offset(, 1).PasteSpecial xlPasteFormulas
End Sub

[/pre]
I would make a further recommendation. If you make your date in Row 2 a monthly formula you would be able to use the above starting row 2 and all your data would be ready once this ran.


Take care


Smallman