Hi
I have 2 spreadsheets – one has hundreds of rows of data with the cells ranging from A2:AK. The workbook is called “Sales 2014” and the tab is called “Sales”.
I want to export each row into their own Template. I have set up a Template – Spreadheet called “Template” and Tab called “Summary” however it is not working. Each template should be entitled “Sales_ CUSTOMER NAME” (Customer name will be in Cell D15 on the template”.
The master sheet and the template are in the same location within a folder, however there is a sub folder named “Completed Sales” within this folder for the exports
Can any help – please see my code below
Sub CreateAndNameWorksheetsSales2014()
Dim c As Range, rng As Range
Dim nSh As Worksheet
Application.ScreenUpdating = False
With Sheets("Sales")
Set rng = .Range("A2:AK" & .Cells(.Rows.Count, "A").End(xlUp).Row)
End With
For Each c In rng
Sheets("Summary").Copy After:=Sheets(Sheets.Count)
Set nSh = Sheets(Sheets.Count)
With c
nSh.Name = "Sales_" & .Value
.Parent.Hyperlinks.Add Anchor:=c, Address:="", SubAddress:= _
"'" & nSh.Name & "'!A1", TextToDisplay:=.Text
nSh.Range("D15").Value = .Value
nSh.Range("D5").Value = .Offset(, 1).Value
nSh.Range("D7").Value = .Offset(, 2).Value
nSh.Range("D9").Value = .Offset(, 3).Value
nSh.Range("D11").Value = .Offset(, 4).Value
nSh.Range("D13").Value = .Offset(, 5).Value
nSh.Range("D15").Value = .Offset(, 6).Value
nSh.Range("C18").Value = .Offset(, 7).Value
nSh.Range("C19").Value = .Offset(, 8).Value
nSh.Range("C20").Value = .Offset(, 9).Value
nSh.Range("C21").Value = .Offset(, 10).Value
nSh.Range("C22").Value = .Offset(, 11).Value
nSh.Range("C23").Value = .Offset(, 12).Value
nSh.Range("D18").Value = .Offset(, 13).Value
nSh.Range("D19").Value = .Offset(, 14).Value
nSh.Range("D20").Value = .Offset(, 15).Value
nSh.Range("D21").Value = .Offset(, 16).Value
nSh.Range("D22").Value = .Offset(, 17).Value
nSh.Range("D23").Value = .Offset(, 18).Value
nSh.Range("D25").Value = .Offset(, 19).Value
nSh.Range("D27").Value = .Offset(, 20).Value
nSh.Range("D28").Value = .Offset(, 21).Value
nSh.Range("D29").Value = .Offset(, 22).Value
nSh.Range("D31").Value = .Offset(, 23).Value
nSh.Range("D33").Value = .Offset(, 24).Value
nSh.Range("D35").Value = .Offset(, 25).Value
nSh.Range("D37").Value = .Offset(, 26).Value
nSh.Range("D39").Value = .Offset(, 27).Value
nSh.Range("D41").Value = .Offset(, 28).Value
nSh.Range("D43").Value = .Offset(, 29).Value
nSh.Range("D45").Value = .Offset(, 30).Value
nSh.Range("D47").Value = .Offset(, 31).Value
nSh.Range("D49").Value = .Offset(, 32).Value
nSh.Range("D51").Value = .Offset(, 33).Value
nSh.Range("D53").Value = .Offset(, 34).Value
nSh.Range("D55").Value = .Offset(, 35).Value
nSh.Range("D58").Value = .Offset(, 36).Value
nSh.Range("D60").Value = .Offset(, 37).Value
nSh.Range("D62").Value = .Offset(, 38).Value
End With
Next c
Application.ScreenUpdating = True
End Sub
I have 2 spreadsheets – one has hundreds of rows of data with the cells ranging from A2:AK. The workbook is called “Sales 2014” and the tab is called “Sales”.
I want to export each row into their own Template. I have set up a Template – Spreadheet called “Template” and Tab called “Summary” however it is not working. Each template should be entitled “Sales_ CUSTOMER NAME” (Customer name will be in Cell D15 on the template”.
The master sheet and the template are in the same location within a folder, however there is a sub folder named “Completed Sales” within this folder for the exports
Can any help – please see my code below
Sub CreateAndNameWorksheetsSales2014()
Dim c As Range, rng As Range
Dim nSh As Worksheet
Application.ScreenUpdating = False
With Sheets("Sales")
Set rng = .Range("A2:AK" & .Cells(.Rows.Count, "A").End(xlUp).Row)
End With
For Each c In rng
Sheets("Summary").Copy After:=Sheets(Sheets.Count)
Set nSh = Sheets(Sheets.Count)
With c
nSh.Name = "Sales_" & .Value
.Parent.Hyperlinks.Add Anchor:=c, Address:="", SubAddress:= _
"'" & nSh.Name & "'!A1", TextToDisplay:=.Text
nSh.Range("D15").Value = .Value
nSh.Range("D5").Value = .Offset(, 1).Value
nSh.Range("D7").Value = .Offset(, 2).Value
nSh.Range("D9").Value = .Offset(, 3).Value
nSh.Range("D11").Value = .Offset(, 4).Value
nSh.Range("D13").Value = .Offset(, 5).Value
nSh.Range("D15").Value = .Offset(, 6).Value
nSh.Range("C18").Value = .Offset(, 7).Value
nSh.Range("C19").Value = .Offset(, 8).Value
nSh.Range("C20").Value = .Offset(, 9).Value
nSh.Range("C21").Value = .Offset(, 10).Value
nSh.Range("C22").Value = .Offset(, 11).Value
nSh.Range("C23").Value = .Offset(, 12).Value
nSh.Range("D18").Value = .Offset(, 13).Value
nSh.Range("D19").Value = .Offset(, 14).Value
nSh.Range("D20").Value = .Offset(, 15).Value
nSh.Range("D21").Value = .Offset(, 16).Value
nSh.Range("D22").Value = .Offset(, 17).Value
nSh.Range("D23").Value = .Offset(, 18).Value
nSh.Range("D25").Value = .Offset(, 19).Value
nSh.Range("D27").Value = .Offset(, 20).Value
nSh.Range("D28").Value = .Offset(, 21).Value
nSh.Range("D29").Value = .Offset(, 22).Value
nSh.Range("D31").Value = .Offset(, 23).Value
nSh.Range("D33").Value = .Offset(, 24).Value
nSh.Range("D35").Value = .Offset(, 25).Value
nSh.Range("D37").Value = .Offset(, 26).Value
nSh.Range("D39").Value = .Offset(, 27).Value
nSh.Range("D41").Value = .Offset(, 28).Value
nSh.Range("D43").Value = .Offset(, 29).Value
nSh.Range("D45").Value = .Offset(, 30).Value
nSh.Range("D47").Value = .Offset(, 31).Value
nSh.Range("D49").Value = .Offset(, 32).Value
nSh.Range("D51").Value = .Offset(, 33).Value
nSh.Range("D53").Value = .Offset(, 34).Value
nSh.Range("D55").Value = .Offset(, 35).Value
nSh.Range("D58").Value = .Offset(, 36).Value
nSh.Range("D60").Value = .Offset(, 37).Value
nSh.Range("D62").Value = .Offset(, 38).Value
End With
Next c
Application.ScreenUpdating = True
End Sub