Sub MoveIt() Application.ScreenUpdating = False Dim cArr As Variant, pArr As Variant, X As Long Dim ws As Worksheet Dim ws1 As Worksheet Dim ws2 As Worksheet Dim lr As Long Set ws = Worksheets("Invoices") Set ws1 = Worksheets("Sales Details") Set ws2 = Worksheets("Helper") ws2.Visible = False ws2.UsedRange.ClearContents cArr = Array("J12", "M12", "C12", "C16", "F15", "B23", "B28", "B33", "B38", "B43", "B48", "C24", "C29", "C34", "C39", "C44", "C49", _ "C25", "C30", "C35", "C40", "C45", "C50", "C26", "C31", "C36", "C41", "C46", "C51", "E23", "E28", "E33", "E38", "E43", "E48", "F23", "F28", _ "F33", "F38", "F43", "F48", "I23", "I28", "I33", "I38", "I43", "I48") pArr = Array("A2", "B2", "C2", "D2", "E2", "F2", "F3", "F4", "F5", "F6", "F7", "G2", "G3", "G4", "G5", "G6", "G7", "H2", "H3", "H4", "H5", _ "H6", "H7", "I2", "I3", "I4", "I5", "I6", "I7", "J2", "J3", "J4", "J5", "J6", "J7", "K2", "K3", "K4", "K5", "K6", "K7", _ "M2", "M3", "M4", "M5", "M6", "M7") For X = LBound(cArr) To UBound(cArr) ws.Range(cArr(X)).Copy ws2.Range(pArr(X)).PasteSpecial xlPasteValues Next lr = ws2.Range("K" & Rows.Count).End(xlUp).Row ws2.Range("A3:A" & lr) = "=A2" ws2.Range("B3:B" & lr) = "=B2" ws2.Range("C3:C" & lr) = "=C2" ws2.Range("D3:D" & lr) = "=D2" ws2.Range("E3:E" & lr) = "=E2" ws2.Range("L2:L" & lr) = "=K2*J2" ws2.Range("N2:N" & lr) = "=IF(E2=""y"",(L2*(M2*50%)),""0"")" ws2.Range("O2:O" & lr) = "=N2" ws2.Range("P2:P" & lr) = "=IF(E2=""n"",(L2*M2),""0"")" ws2.Range("Q2:Q" & lr) = "=SUM(L2)+SUM(N2:P2)" ws2.UsedRange.Copy ws1.Range("A" & Rows.Count).End(3)(2).PasteSpecial xlValues ws1.Columns.AutoFit ws1.Select ClearItMergeIt Application.CutCopyMode = False Application.ScreenUpdating = True End Sub Sub ClearItMergeIt() Dim ws As Worksheet Set ws = Worksheets("Invoices") On Error Resume Next ws.[F15].ClearContents ws.[J12].ClearContents ws.[E23:E51].UnMerge: ws.[E23:E51].ClearContents ws.[I23:I51].UnMerge: ws.[I23:I51].ClearContents ws.[C16:F16].UnMerge: ws.[C16:F16].ClearContents: ws.[C16:F16].Merge ws.[C11:F14].UnMerge: ws.[C11:F14].ClearContents ws.[B23:D23].UnMerge: ws.[B23:D23].SpecialCells(xlCellTypeConstants, 23).ClearContents: ws.[B23:D23].Merge ws.[C24:D26].UnMerge: ws.[C24:D26].SpecialCells(xlCellTypeConstants, 23).ClearContents ws.[B28:D28].UnMerge: ws.[B28:D28].SpecialCells(xlCellTypeConstants, 23).ClearContents: ws.[B28:D28].Merge ws.[C29:D31].UnMerge: ws.[C29:D31].SpecialCells(xlCellTypeConstants, 23).ClearContents ws.[B33:D33].UnMerge: ws.[B33:D33].SpecialCells(xlCellTypeConstants, 23).ClearContents: ws.[B33:D33].Merge ws.[C34:D36].UnMerge: ws.[C34:D36].SpecialCells(xlCellTypeConstants, 23).ClearContents ws.[B38:D38].UnMerge: ws.[B38:D38].SpecialCells(xlCellTypeConstants, 23).ClearContents: ws.[B38:D38].Merge ws.[C39:D41].UnMerge: ws.[C39:D41].SpecialCells(xlCellTypeConstants, 23).ClearContents ws.[B43:D43].UnMerge: ws.[B43:D43].SpecialCells(xlCellTypeConstants, 23).ClearContents: ws.[B43:D43].Merge ws.[C44:D46].UnMerge: ws.[C44:D46].SpecialCells(xlCellTypeConstants, 23).ClearContents ws.[B48:D48].UnMerge: ws.[B48:D48].SpecialCells(xlCellTypeConstants, 23).ClearContents: ws.[B48:D48].Merge ws.[C49:D51].UnMerge: ws.[C49:D51].SpecialCells(xlCellTypeConstants, 23).ClearContents ws.[C24:D24].Merge: ws.[C25:D25].Merge: ws.[C26:D26].Merge ws.[C29:D29].Merge: ws.[C30:D30].Merge: ws.[C31:D31].Merge ws.[C34:D34].Merge: ws.[C35:D35].Merge: ws.[C36:D36].Merge ws.[C39:D39].Merge: ws.[C40:D40].Merge: ws.[C41:D41].Merge ws.[C44:D44].Merge: ws.[C45:D45].Merge: ws.[C46:D46].Merge ws.[C49:D49].Merge: ws.[C50:D50].Merge: ws.[C51:D51].Merge ws.[E23:E26].Merge: ws.[E28:E31].Merge: ws.[E33:E36].Merge: ws.[E38:E41].Merge: ws.[E43:E46].Merge: ws.[E48:E51].Merge ws.[F23:F26].Merge: ws.[F28:F31].Merge: ws.[F33:F36].Merge: ws.[F38:F41].Merge: ws.[F43:F46].Merge: ws.[F48:F51].Merge ws.[G23:G26].Merge: ws.[G28:G31].Merge: ws.[G33:G36].Merge: ws.[G38:G41].Merge: ws.[G43:G46].Merge: ws.[G48:G51].Merge ws.[H23:H26].Merge: ws.[H28:H31].Merge: ws.[H33:H36].Merge: ws.[H38:H41].Merge: ws.[H43:H46].Merge: ws.[H48:H51].Merge ws.[I23:I26].Merge: ws.[I28:I31].Merge: ws.[I33:I36].Merge: ws.[I38:I41].Merge: ws.[I43:I46].Merge: ws.[I48:I51].Merge ws.[J23:J26].Merge: ws.[J28:J31].Merge: ws.[J33:J36].Merge: ws.[J38:J41].Merge: ws.[J43:J46].Merge: ws.[J48:J51].Merge ws.[K23:K26].Merge: ws.[K28:K31].Merge: ws.[K33:K36].Merge: ws.[K38:K41].Merge: ws.[K43:K46].Merge: ws.[K48:K51].Merge ws.[L23:L26].Merge: ws.[L28:L31].Merge: ws.[L33:L36].Merge: ws.[L38:L41].Merge: ws.[L43:L46].Merge: ws.[L48:L51].Merge ws.[M23:M26].Merge: ws.[M28:M31].Merge: ws.[M33:M36].Merge: ws.[M38:M41].Merge: ws.[M43:M46].Merge: ws.[M48:M51].Merge ws.[C11:F11].Merge: ws.[C12:F12].Merge: ws.[C13:F13].Merge: ws.[C14:F14].Merge End Sub
I am saving this link as a proof of concept, so others can use the model!
DON'T MISS
No. I meant I wanted to create one new sheet. From the the calculation and invoice sheet named 'Invoices', I want data to be populated in say 'Sales Details' , where in there is a serial invoice wise entry along the values from fields I had mentioned of all the invoices I created using the 'Invoices' sheet
Regards,
Rodney