Hello all,
I have been working on this macro all day and no matter how many different ways I write it I can't seem to get it to finish correctly without giving me an error message. The error messages are different depending on how I write the macro, so what the message says is not important.
I have attached a sample of my work which contains the macro, but I'll post the code below as well. There are two tabs in the workbook. One contains the data("download") and the other is the template("Blank") per say. I need the macro to create a new worksheet using the template for each line of the data ("download")until it gets to the "finalrow." It names the worksheets the value of D3 currently, but I'd like to figure out how to name the worksheets by C3. c3 contains the names of customers, and is duplicated for each product they have purchased. I'd just like to attach a number after the name if it is a duplicate. i.e. Adam, Adam1, Adam2, Adam3 for all "Adam"s.
Look into the code and see if that makes sense. if not, feel free to ask questions. I appreciate the time you take to help me resolve this issue!
thanks
Sub automate_linesheets()
'
' automate_linesheets Macro
' Macro recorded 10/8/2009 by Kelly.Householder
'
Dim finalrow As Long
Dim finalcolumn As Long
Sheets("Download").Select
ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Select
finalrow = ActiveCell.Row
Debug.Print finalrow
finalcolumn = ActiveCell.Column
Debug.Print finalcolumn
Sheets("Download").Select
Range("C3").Select
Range(Cells(3, 1), Cells(finalrow, finalcolumn)).Sort Key1:=Range("C3"), Order1:=xlAscending, Key2:= _
Range("D3"), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal
'Range(Cells(3, 1), Cells(finalrow, 30))
Dim I As Long
For I = 3 To finalrow
Sheets("Download").Select
Cells(I, 2).Select
If Cells(I, 11).Value >= 0 And Cells(I, 12).Value >= 0 Then
Range(Cells(I, 1), Cells(I, 34)).Select
'Rows(I).EntireRow.Select
Selection.Copy
Sheets("Blank").Select
Range("A3").Select
ActiveSheet.Paste
Sheets("Blank").Select
Sheets("Blank").Copy After:=Sheets(2)
ActiveSheet.Name = Range("D3")
'On Error Resume Next
End If
Next I
End Sub
Bookmarks