Macro to create Worksheets from a main file

[Closed]
Report
Posts
1
Registration date
Tuesday December 3, 2013
Status
Member
Last seen
December 3, 2013
-
Hey guys,

Nice work going on here,have been a regular follower.

I seem to have come across a problem with my MACRO for excel.

I have a Excel file containing 2 work sheets
1) Sheet called "Master"
2)Sheet called " List"

What i want the macro to do is to Duplicate the "Master Sheet" and rename worksheets as per the column one in "List" sheet.

I have a macro that can do that, till the duplication and the renaming part.

Now i need the macro to take other details from adjacent columns from the same row of the "List" sheet, and enter it into the now duplicated sheet.

I cannot get that into a loop.

here's the macro, i've shamelessly copied and edited according to the problem

Sub CreateSheetsFromAList()

Dim ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet
Dim i As Integer
i = 2

Dim rng As Range, cell As Range
Dim DRA As String
Set ws1 = ThisWorkbook.Worksheets("Master")
Set ws2 = ThisWorkbook.Worksheets("List")

Dim MyCell As Range, MyRange As Range

Set MyRange = Sheets("List").Range("A2")
Set MyRange = Range(MyRange, MyRange.End(xlDown))

Set rng = Sheets("List").Range("B2")
Set rng = Range(rng, rng.End(xlDown))

For Each MyCell In MyRange
ws1.Copy ThisWorkbook.Sheets(Sheets.Count)
ThisWorkbook.Worksheets("Master (2)").Name = MyCell.Value
Set ws3 = ThisWorkbook.Worksheets(MyCell.Value)


DRA = "B" & "i"
'DRA(2) = Concatenate("C", i) <- cant get this to work
'DRA(3) = concatenate("D", i)
'DRA(4) = concatenate("E", i)
'For Each cell In rng
ws2.Range("B2").Copy ws3.Range("D6:E6")
'ws2.Range(DRA(2)).Copy ws3.Range("D7:E7")
'ws2.Range(DRA(3)).Copy ws3.Range("M6:AD6")
'ws2.Range(DRA(4)).Copy ws3.Range("M7:AD7")
i = i + 1
'Next cell
Next MyCell


End Sub
Hope i've been able to provide sufficient information,if possible i can attach the excel sheet also.


This is basically repetetive work so i was thinking if i were to create another column in the List Sheet, can i get excel to creat different excel files( with multiple tabs) per file like the one being created in the first part. supposedly i call the systems S1, s2 etc.


Thanks i