Update this copy and transpose macro?

[Coyotex3]

Hey guys, So I have data in this format

Order Template.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	City	Order Recipient	Primary Address	Secondary Address	City, State, Zip Code	Primary Order	Secondary Order	Third Order	Additional Order	Amount(1)	Amount(2)	Amount(3)	Birthday	Account Type
2	New York	John Doe 1	123 Make it Happen	150 We Did It	Manhattan, NY, 10001	12545	54568	75854	63210	100	7546	1000	1/10/1950	Premier
3	New York	John Doe 2	124 Make it Happen	151 We Did It	Manhattan, NY, 10002	12546	54569	75854	63211	1000	7547	1000	1/10/1950	Preferred
4	New York	John Doe 3	125 Make it Happen	152 We Did It	Manhattan, NY, 10003	12547	54570	75854	63212	150	7548	1000	1/10/1950	Preferred
5	New York	John Doe 4	126 Make it Happen		Manhattan, NY, 10004	12548	54571	75854	63213	2000	7549	1000	1/10/1950	Preferred
6	New York	John Doe 5	127 Make it Happen		Manhattan, NY, 10005	12549	54572	75854		5000	7550	1000	1/10/1950	Preferred
7	New York	John Doe 6	128 Make it Happen		Manhattan, NY, 10006	12550	54573	75854		5000	7551	1000	1/10/1950	Preferred
8	New York	John Doe 7	129 Make it Happen		Manhattan, NY, 10007	12551	54574	75854	54655	6000	7552	1000	1/10/1950	Preferred
9	New York	John Doe 8	130 Make it Happen		Manhattan, NY, 10008	12552	54575	75854	646546	7000	7553	1000	1/10/1950	Preferred
10	New York	John Doe 9	131 Make it Happen		Manhattan, NY, 10009	12553	54576	75854	65465	7000	7554	1000	1/10/1950	Preferred
11
12
13
Sheet1

Ideally I would like to copy paste transpose some data on a loop in order to create a new sheet and autofill the information like this.

Order Template.xlsx
	A	B	C	D	E	F	G
1	Order Form
2	TODAY'S DATE:	1/10/2021
3	DATE NEEDED:	1/11/2021
4	Order Type
5	Point of Contact:	John Doe 1
6	Total Due	1,000
7	Order Recipient:
8	Primary Address	123 Make it Happen
9	Secondary Address	150 We Did It
10	City, State, Zip Code	Manhattan, NY, 10001
11	Additional Information
12	Seller:
13	Primary Order	12545	Amount(1)	$100.00
14	Secondary Order	54568	Amount(2)	$7,546.00
15	Third Order	75854	Amount(3)	$1,000.00
16	Additional Order	63210	Amount(4)
17	Description:
18	Signature:
19	Manager Print:
20	Signature:
21	Date:
22
23	Reason
24
25
26
27
28
29
30
31
32
33
34
Sheet2

The macro that I have(obtained from here but altered) was this

Sub CopyPasteTranspose()

    Dim i As Long
    With Sheets("Sheet1")
        For i = 2 To .Range("A" & Rows.Count).End(3).Row
            Sheets("sheet2").Copy After:=Sheets(Sheets.Count)
            ActiveSheet.Name = "Order" & i - 5
            ActiveSheet.Range("C9").Resize(12).Value = Application.Transpose(.Range("B" & i).Resize(, 12).Value)
        Next
    End With
End Sub

That code was for a totally different format and I do not know how to alter it further. Ideally I would also like for cell "B2" to = Today's date and "C2" Date + 1.

Thank you guys!!

 

[DanteAmor]

For the format you have in sheet2, try the following:

Sub CopyAndTranspose()
  Dim sh1 As Worksheet, sh3 As Worksheet
  Dim i As Long
  
  Set sh1 = Sheets("Sheet1")
  For i = 2 To sh1.Range("A" & Rows.Count).End(3).Row
    Sheets("Sheet2").Copy after:=Sheets(Sheets.Count)
    Set sh3 = ActiveSheet
    sh3.Name = "Order " & sh1.Range("F" & i).Value
    sh3.Range("B2").Value = Date
    sh3.Range("B3").Value = Date + 1
    'Order Form
    sh3.Range("B4").Value = ""                        'order type
    sh3.Range("B5").Value = sh1.Range("B" & i).Value  'contact
    sh3.Range("B6").Value = ""                        'total due
    sh3.Range("B7").Value = sh1.Range("B" & i).Value  'recipient
    sh3.Range("B8").Value = sh1.Range("C" & i).Value  'prim add
    sh3.Range("B9").Value = sh1.Range("D" & i).Value  'sec add
    sh3.Range("B10").Value = sh1.Range("E" & i).Value 'city
    'Additional info
    sh3.Range("B13").Value = sh1.Range("F" & i).Value 'pri order
    sh3.Range("B14").Value = sh1.Range("G" & i).Value 'sec order
    sh3.Range("B15").Value = sh1.Range("H" & i).Value 'thi order
    sh3.Range("B16").Value = sh1.Range("I" & i).Value 'add order
    sh3.Range("D13").Value = sh1.Range("J" & i).Value 'amount 1
    sh3.Range("D14").Value = sh1.Range("K" & i).Value 'amount 2
    sh3.Range("D15").Value = sh1.Range("L" & i).Value 'amount 3
    sh3.Range("D16").Value = sh1.Range("M" & i).Value 'amount 4
  Next
End Sub

 

[Coyotex3]

For the format you have in sheet2, try the following:

Sub CopyAndTranspose()
  Dim sh1 As Worksheet, sh3 As Worksheet
  Dim i As Long
 
  Set sh1 = Sheets("Sheet1")
  For i = 2 To sh1.Range("A" & Rows.Count).End(3).Row
    Sheets("Sheet2").Copy after:=Sheets(Sheets.Count)
    Set sh3 = ActiveSheet
    sh3.Name = "Order " & sh1.Range("F" & i).Value
    sh3.Range("B2").Value = Date
    sh3.Range("B3").Value = Date + 1
    'Order Form
    sh3.Range("B4").Value = ""                        'order type
    sh3.Range("B5").Value = sh1.Range("B" & i).Value  'contact
    sh3.Range("B6").Value = ""                        'total due
    sh3.Range("B7").Value = sh1.Range("B" & i).Value  'recipient
    sh3.Range("B8").Value = sh1.Range("C" & i).Value  'prim add
    sh3.Range("B9").Value = sh1.Range("D" & i).Value  'sec add
    sh3.Range("B10").Value = sh1.Range("E" & i).Value 'city
    'Additional info
    sh3.Range("B13").Value = sh1.Range("F" & i).Value 'pri order
    sh3.Range("B14").Value = sh1.Range("G" & i).Value 'sec order
    sh3.Range("B15").Value = sh1.Range("H" & i).Value 'thi order
    sh3.Range("B16").Value = sh1.Range("I" & i).Value 'add order
    sh3.Range("D13").Value = sh1.Range("J" & i).Value 'amount 1
    sh3.Range("D14").Value = sh1.Range("K" & i).Value 'amount 2
    sh3.Range("D15").Value = sh1.Range("L" & i).Value 'amount 3
    sh3.Range("D16").Value = sh1.Range("M" & i).Value 'amount 4
  Next
End Sub

You are an absolute god amongst men!!!

Tu eres mi heroe!

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.
Aprecio tus comentarios