VBA Macro for creation of sheets from summary sheet

[LMUWE]

Hello all,

Its my first time using VBA macros and I have managed to solve two of the three elements I am looking at but please need your help on the third.

I have a summary sheet which has two columns, one for lamina no. and one for angle of fibres. I have implemented the following which creates a sheet entitled 'Lamina x' for every lamina present and all are set out the same thanks to master sheet '1'.

Sub CreateSheetsFromAList()
        Dim MyCell As Range, MyRange As Range
     
        Set MyRange = Sheets("Input").Range("A4")
        Set MyRange = Range(MyRange, MyRange.End(xlDown))
     
        For Each MyCell In MyRange
            Sheets("1").Copy After:=Sheets(Sheets.Count)
            Sheets(Sheets.Count).Name = MyCell.Value
        Next MyCell
End Sub

However, what I am looking to do is ensure that the associated angle is read by the sheet created for that lamina.

I also have a problem where by when I add in more lamina in the summary sheet and run the macro again I get the '400' error/told that a sheet already exists with that name and the additional sheets are not created.

I'd really appreciate your help with this!

 

[Hui]

Lmuwe

Firstly, Welcome to the Chandoo.org Forums

You can use MyCell.Offset(, 1).Value to extract the value in the adjacent cell

Like below:

Sub CreateSheetsFromAList()
  Dim MyCell As Range, MyRange As Range

  Set MyRange = Sheets("Input").Range("A4")
  Set MyRange = Range(MyRange, MyRange.End(xlDown))

  For Each MyCell In MyRange
    Sheets("1").Copy After:=Sheets(Sheets.Count)
    Sheets(Sheets.Count).Name = MyCell.Value
    'MyVar = MyCell.Offset(, 1).Value
    'MyAction = some action using MyCell.Offset(, 1).Value
  Next MyCell
End Sub

 

[LMUWE]

Hi Hui,

Thank you so much for responding quickly! Sorry for my ignorance but how does that allow excel to work out which formula to add it to in the sheet which it creates? I have an 'angle' cell in the sheet which is subsequently created and I need that to be the one to which the adjacent cell value is attributed.

Many thanks,
Laura

 

[Hui]

Im not really sure what you want, but:
Sheets(MyCell.Value).value = Range("A1).MyCell.Offset(, 1).Value
Will place the Angle in cell A1 in the new sheet
Adjust A1 to suit

Sub CreateSheetsFromAList()
  Dim MyCell As Range, MyRange As Range

  Set MyRange = Sheets("Input").Range("A4")
  Set MyRange = Range(MyRange, MyRange.End(xlDown))

  For Each MyCell In MyRange
    Sheets("1").Copy After:=Sheets(Sheets.Count)
    Sheets(Sheets.Count).Name = MyCell.Value
    Sheets(MyCell.Value).value= Range("A1).MyCell.Offset(, 1).Value   "adjust range to suit
 Next MyCell
End Sub