VBA code Help - Coping data while creating worksheets

Question

Here is some code i found and have adjusted to do my task.

I want to

1. create lots of worksheets from a list  - This part works fine
2. put the name of the worksheet in cell B2 - This also works fine.
3. Copy data from another list (on the same worksheet as where i am getting the names for the new worksheets form), starting with A5 and paste in A2 on the newly created worksheet - This is where i need help please

I have created another MyRange2 and Mycell2 to try and copy the second list but its not working. Any thoughts? Cheers

Sub CreateSheetsFromAList()

    Dim MyCell As Range, MyRange As Range

    Dim MyCell2 As Range, MyRange2 As Range

    Set MyRange = Sheets("Users").Range("D5")

    Set MyRange = Range(MyRange, MyRange.End(xlDown))

    Set MyRange2 = Sheets("Users").Range("A5")

    Set MyRnage2 = Range(MyRange2, MyRange.End(xlDown))

    For Each MyCell In MyRange

        With Sheets.Add(After:=Sheets(Sheets.Count))  'creates a new worksheet

            .Name = MyCell.Value    ' renames the new worksheet

            Sheets("FINANCE03").Range("A1:F16").Copy Destination:=.Range("A1:F16") 'copies all the formatting to the new sheet

            .Range("B2").Value = .Name ' copies the name of the sheet to cell B2

            For Each MyCell2 In MyRange2

                .Range("A2").Value =

            Next MyCell2

        End With

    Next MyCell

End Sub

Answer 1

It is never a good idea to use ACTIVECELL, SELECT or SELECTION in macros. Such code is slow and error prone.

The reason why your last code did not work is that SELECT is a method. If you want to get the value, you have to call the property.

I don't know what you want to accomplish and what I should advise...

Am I right if I assume that the most code is written from somebody else? How experienced are you with VBA?

Andreas.

Answer 2

Thanks for the reply.

I have got it working up till this point (see bold text) i cant it to paste something on the same line and the current cell.

Sub CreateSheetsFromAList()

    Dim MyCell As Range, MyRange As Range

    Set MyRange = Sheets("Users").Range("D3")

    Set MyRange = Range(MyRange, MyRange.End(xlDown))

    For Each MyCell In MyRange

        With Sheets.Add(After:=Sheets(Sheets.Count))  'creates a new worksheet

            .Name = MyCell.Value    ' renames the new worksheet

            Sheets("FINANCE01").Range("A1:F16").Copy Destination:=.Range("A1:F16") 'copies all the formatting to the new sheet

            .Range("B2").Value = .Name ' copies the name of the sheet to cell B2

            .Range("A2").Value = ActiveCell.Offset(0, -2).Select

        End With

    Next MyCell

End Sub

Answer 3

Use

  MyRange2.Copy .Range("A2")

instead of the "For Each MyCell2 In MyRange2" loop.

Andreas.