VBA to Create over 200 Tabs & a Scatter Graph

[VBA learner ITG]

Thank you for taking the time to read my post.

Im sure everyone has been asked to create multiple tabs at some point in their life while working with Excel, However I have been asked to create several hundred and i don't really want to spend my weekend loosing my marbles.

Please can I get your advice on how to best achieve this.

As part of requirement i have been asked to create a Scatter Graph based on the data in column A & B.

I guess i have 2 requests.

1. Create Multiple Tabs based on the number of URN e.g 1 > 200 in Column C.
2. Create a scatter graph with the data in column A & B of the worksheet.


Any Advice or suggestion would be greatly appreciated.

 

[Fennek]

Hi,
I'd have a look, if you provide a sample file including a chart with all specified layout, ie. flexible title.

There is no promise at all and I reject all public filehoster. Would you use
Excel Help
with mentioning the crossposting.

regards

 

[VBA learner ITG]

Hi Fennek,

Thank you for taking the time to have a look.

https://we.tl/wabC4aUtsx

Any advice you can give would be greatly appreciated.

 

[Fennek]

Hi,

did I made a comment about filehoster????

So, I generated a dataset and a series of scatterchart. A Makro for copying the formats of the first to all others is prepared, but not yet done.

Sub many_Charts()

    lr = Cells(Rows.Count, 1).End(xlUp).Row
    ls = Cells(1, Columns.Count).End(xlToLeft).Column
For c = 2 To ls
    Union(Range(Cells(1, 1), Cells(lr, 1)), Range(Cells(1, c), Cells(lr, c))).Select
    Cells(1, c).Activate
    Col = "Tabelle1!$" & Chr(64 + c) & "1:$" & Chr(64 + c) & "$" & lr
    ActiveSheet.Shapes.AddChart2(240, xlXYScatterSmooth).Select
    ActiveChart.SetSourceData Source:=Range( _
        "Tabelle1!$A$1:$A$11," & Col)
    ActiveChart.Location Where:=xlLocationAsNewSheet
    ActiveSheet.Name = ActiveChart.ChartTitle.Text
    Sheets("Tabelle1").Select
Next c
End Sub

Sub Format_Copy()
'
    ActiveChart.ChartArea.Select
    ActiveChart.ChartArea.Copy
    Sheets("Y2").Select
    ActiveChart.PasteSpecial Format:=2
End Sub

Sub Create_DataSet()
Range("A1") = "x"
For i = 1 To 5
    Cells(1, i + 1) = "Y" & i
Next i
Range("A2:A11").Formula = "=row()-1"
Range("B2:F11").Formula = "=RANDBETWEEN(10,100)"
with activesheet.usedrange 'not testet
.value = .value
end with
End Sub

regards