Discussion Re: M.S.EXCEL VBA in Excel
https://techcommunity.microsoft.com/t5/excel/m-s-excel-vba/m-p/3356037#M145968
Respected Mr. Hans Vogelaar<BR />Many Many Thanks to You for Your Valuable TIPS for me. Again Thank You Sir.<BR />I shall be highly Obliged to You For help me.Wed, 11 May 2022 08:36:05 GMTTARUNKANTI19642022-05-11T08:36:05ZM.S.EXCEL VBA
https://techcommunity.microsoft.com/t5/excel/m-s-excel-vba/m-p/3342338#M145709
<P>Hi, I am completely beginner for EXCEL VBA, here I have created an Excel work book having Three worksheets, where I would like to enter data in cells range "D15:D39" as 'ITEM NAME' and in cells range "E15:E39" as 'ITEM QUANTITY' in the “INVOICE” Sheet along with "SAVE" Button. When I press “SAVE” button on “INVOICE” Sheet, and then that data range from (D15:D39) as 'ITEM NAME' , (E15:E39) as 'ITEM QUANTITY' and cell value of "H45" as 'TOTAL INVOICE AMOUNT' from "INVOICE SHEET" would be transferred to Column cells "D2:AY2" of "INVOICE LIST" sheet, to Cell No. "D2","F2","H2","J2","L2","N2","P2","R2","T2","V2","X2","Z2","AB2","AD2","AF2","AH2","AJ2",”AL2”,”AN2”,”AP2”,”AR2”,”AT2””AV2”,”AX2” as 'ITEM NAME' </P><P>and to Cell no</P><P>"E2","G2","I2","K2","M2","O2","Q2","S2","U2","W2","Y2","AA2","AC2","AE2","AG2","AI2","AK2",”AM2”,”AO2”,”AQ2”,”AS2”,”AU2”,”AW2”,”AY2”,as 'ITEM QUANTITY' and to cell no "AZ2" as 'TOTAL INVOICE AMOUNT' and contains of schedule cell of “INVOICE” Sheet would be clear except FORMULA. Then if I enter again data to Cell range "D15:D39" and Cell range "E15:E39" in my "INVOICE" Sheet for create new invoice and press the SAVE button on "INVOICE" Sheet I would need the contains of "INVOICE" Sheet would be transfer except FORMULA to "INVOICE LIST" Sheet to the next available blank row "D3:AY3", and “AZ3”, and every time when I press the "SAVE Button" the event would be continuing to happen as above frequently. but it not works. May I get help to correct the same I have Code which i wrote as below</P><P> </P><P>Sub SaveData()</P><P> Dim ws As Worksheet</P><P> Dim wt As Worksheet</P><P> Dim r As Long</P><P> Application.ScreenUpdating = False</P><P> Set ws = Worksheets("INVOICE")</P><P> Set wt = Worksheets("INVOICELIST")</P><P> ' Find first empty row in columns D:AL on sheet INVOICELIST</P><P> r = wt.Range("D:AY").Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1</P><P> wt.Range("D" & r).Resize(1, 25).Value = Application.Transpose(ws.Range("D15:D39"))</P><P> r = wt.Range("D:AY").Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1</P><P> wt.Range("E" & r).Resize(1, 25).Value = Application.Transpose(ws.Range("E15:E39"))</P><P> wt.Range("AZ" & r).Value = ws.Range("H45").Value</P><P> </P><P> 'Copy inv no</P><P>Sheets("INVOICELIST").Range("B" & r).Value = Sheets("INVOICE").Range("G1").Value</P><P>'Copy Date</P><P>Sheets("INVOICELIST").Range("A" & r).Value = Sheets("INVOICE").Range("G2").Value</P><P>'Copy Company Name</P><P>Sheets("INVOICELIST").Range("C" & r).Value = Sheets("INVOICE").Range("D3").Value</P><P>r = r + 1</P><P> Application.ScreenUpdating = True</P><P>End Sub</P>Mon, 09 May 2022 09:21:45 GMThttps://techcommunity.microsoft.com/t5/excel/m-s-excel-vba/m-p/3342338#M145709TARUNKANTI19642022-05-09T09:21:45ZRe: M.S.EXCEL VBA
https://techcommunity.microsoft.com/t5/excel/m-s-excel-vba/m-p/3342444#M145710
<P><LI-USER uid="1383603"></LI-USER> </P>
<P>Not bad for a beginner, you were almost there!</P>
<P>Try this:</P>
<LI-CODE lang="visual-basic">Sub SaveData()
Dim ws As Worksheet
Dim wt As Worksheet
Dim s As Long
Dim t As Long
Dim r As Long
Application.ScreenUpdating = False
Set ws = Worksheets("INVOICE")
Set wt = Worksheets("INVOICELIST")
' Find first empty row in columns D:AL on sheet INVOICELIST
t = wt.Range("D:AY").Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
' Copy Item Name and Item Quantity
For s = 1 To 25
wt.Cells(t, 2 * s + 2).Value = ws.Range("D" & s + 14).Value
wt.Cells(t, 2 * s + 3).Value = ws.Range("E" & s + 14).Value
Next s
wt.Range("AZ" & t).Value = ws.Range("H45").Value
'Copy inv no
wt.Range("B" & t).Value = ws.Range("G1").Value
'Copy Date
wt.Range("A" & t).Value = ws.Range("G2").Value
'Copy Company Name
wt.Range("C" & t).Value = ws.Range("D3").Value
' Clear Item Name and Item Quantity
ws.Range("D15:E39").ClearContents
' Optional: clear Invoice Number, Date and Company Name
ws.Range("D3,G1:G2").ClearContents
Application.ScreenUpdating = True
End Sub</LI-CODE>Mon, 09 May 2022 09:48:00 GMThttps://techcommunity.microsoft.com/t5/excel/m-s-excel-vba/m-p/3342444#M145710Hans Vogelaar2022-05-09T09:48:00ZRe: M.S.EXCEL VBA
https://techcommunity.microsoft.com/t5/excel/m-s-excel-vba/m-p/3342554#M145712
Respected Mr. Hans Vogelaar<BR />Excellent! i have no language , what to say thank You. I am trying to use Your Tips, and I think it will work for me. Again I thank You.<BR />shall be Your most obidient.<BR />Mon, 09 May 2022 10:05:22 GMThttps://techcommunity.microsoft.com/t5/excel/m-s-excel-vba/m-p/3342554#M145712TARUNKANTI19642022-05-09T10:05:22ZRe: M.S.EXCEL VBA
https://techcommunity.microsoft.com/t5/excel/m-s-excel-vba/m-p/3342833#M145714
<P><LI-USER uid="1383603"></LI-USER> </P><P> </P><P>Respected Mr. Hans Vogelaar</P><P>a while ago i got Your tips and i used them in my excel project, but that is not working properly. That's why i reposted that matter for getting help from You. if there is any mistake by me please forgive me.</P><P>VBA CODE:</P><P>Sub SaveData()</P><P> Dim ws As Worksheet</P><P> Dim wt As Worksheet</P><P> Dim s As Long</P><P> Dim t As Long</P><P> Dim r As Long</P><P> Application.ScreenUpdating = False</P><P> Set ws = Worksheets("INVOICE")</P><P> Set wt = Worksheets("INVOICELIST")</P><P> ' Find first empty row in columns D:AL on sheet INVOICELIST</P><P> t = wt.Range("D:AY").Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1</P><P> ' Copy Item Name and Item Quantity</P><P> For s = 1 To 25</P><P> wt.Cells(t, 2 * s + 2).Value = ws.Range("D" & s + 14).Value</P><P> wt.Cells(t, 2 * s + 3).Value = ws.Range("E" & s + 14).Value</P><P> Next s</P><P> wt.Range("AZ" & t).Value = ws.Range("H45").Value</P><P> 'Copy inv no</P><P> wt.Range("B" & t).Value = ws.Range("G1").Value</P><P> 'Copy Date</P><P> wt.Range("A" & t).Value = ws.Range("G2").Value</P><P> 'Copy Company Name</P><P> wt.Range("C" & t).Value = ws.Range("D3").Value</P><P> ' Clear Item Name and Item Quantity</P><P> ws.Range("D15:E39").ClearContents</P><P> ' Optional: clear Invoice Number, Date and Company Name</P><P> ws.Range("D3,G1:G2").ClearContents</P><P> Application.ScreenUpdating = True</P><P>End Sub</P><P>THIS IS INVOICE Sheet</P><P> </P><TABLE width="691"><TBODY><TR><TD width="72"> </TD><TD width="40"> </TD><TD width="76"> </TD><TD width="247">INVOICE</TD><TD width="91">Invoice #:</TD><TD width="81">1</TD><TD width="84"> </TD></TR><TR><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD>Invoice Date</TD><TD>09-05-2022</TD><TD> </TD></TR><TR><TD> </TD><TD>CUSTOMER:</TD><TD> </TD><TD>MAYNA</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD> </TD><TD>Sr.No.</TD><TD>HSN CODE</TD><TD>ITEM NAME</TD><TD>Qty</TD><TD>Unit Price</TD><TD>Total Value</TD><TD> </TD></TR><TR><TD> </TD><TD>1</TD><TD> </TD><TD>ABCD</TD><TD>10</TD><TD>11</TD><TD>110</TD><TD> </TD></TR><TR><TD> </TD><TD>2</TD><TD> </TD><TD>ABCD</TD><TD>55</TD><TD>11</TD><TD>605</TD><TD> </TD></TR><TR><TD> </TD><TD>3</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD> </TD><TD>4</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD> </TD><TD>5</TD><TD> </TD><TD>EFGH</TD><TD>17</TD><TD>9</TD><TD>153</TD><TD> </TD></TR><TR><TD> </TD><TD>6</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD> </TD><TD>7</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD> </TD><TD>8</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD> </TD><TD>9</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD> </TD><TD>10</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD> </TD><TD>11</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD> </TD><TD>12</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD> </TD><TD>13</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD> </TD><TD>14</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD> </TD><TD>15</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD> </TD><TD>16</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD> </TD><TD>17</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD> </TD><TD>18</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD> </TD><TD>19</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD> </TD><TD>20</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD> </TD><TD>21</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD> </TD><TD>22</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD> </TD><TD>23</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD> </TD><TD>24</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD> </TD><TD>25</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD>Sub Total</TD><TD> </TD><TD> </TD></TR><TR><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD>Tax</TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD>Discount</TD><TD> </TD><TD> </TD></TR><TR><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD>Total</TD><TD> </TD><TD> </TD><TD>868</TD></TR></TBODY></TABLE><P> </P><P>This is INVOICELIST Sheet</P><P> </P><TABLE width="2646"><TBODY><TR><TD width="72">INVOICE NO </TD><TD width="72">DATE</TD><TD width="84">CONSUMER NAME</TD><TD width="49">Item NAME</TD><TD width="42">QTY</TD><TD width="49">Item NAME</TD><TD width="42">QTY</TD><TD width="49">Item NAME</TD><TD width="42">QTY</TD><TD width="49">Item NAME</TD><TD width="42">QTY</TD><TD width="49">Item NAME</TD><TD width="48">QTY</TD><TD width="48">Item NAME</TD><TD width="48">QTY</TD><TD width="48">Item NAME</TD><TD width="48">QTY</TD><TD width="48">Item NAME</TD><TD width="48">QTY</TD><TD width="48">Item NAME</TD><TD width="45">QTY</TD><TD width="49">Item NAME</TD><TD width="45">QTY</TD><TD width="49">Item NAME</TD><TD width="45">QTY</TD><TD width="49">Item NAME</TD><TD width="45">QTY</TD><TD width="49">Item NAME</TD><TD width="45">QTY</TD><TD width="49">Item NAME</TD><TD width="45">QTY</TD><TD width="49">Item NAME</TD><TD width="45">QTY</TD><TD width="49">Item NAME</TD><TD width="45">QTY</TD><TD width="49">Item NAME</TD><TD width="45">QTY</TD><TD width="49">Item NAME</TD><TD width="45">QTY</TD><TD width="49">Item NAME</TD><TD width="45">QTY</TD><TD width="49">Item NAME</TD><TD width="45">QTY</TD><TD width="49">Item NAME</TD><TD width="45">QTY</TD><TD width="49">Item NAME</TD><TD width="45">QTY</TD><TD width="49">Item NAME</TD><TD width="45">QTY</TD><TD width="49">Item NAME</TD><TD width="45">QTY</TD><TD width="49">Item NAME</TD><TD width="45">QTY</TD><TD width="72">VALUE</TD></TR></TBODY></TABLE>Mon, 09 May 2022 11:13:18 GMThttps://techcommunity.microsoft.com/t5/excel/m-s-excel-vba/m-p/3342833#M145714TARUNKANTI19642022-05-09T11:13:18ZRe: M.S.EXCEL VBA
https://techcommunity.microsoft.com/t5/excel/m-s-excel-vba/m-p/3344291#M145738
<P><LI-USER uid="1383603"></LI-USER> </P>
<P>Is the row with INVOICE and Invoice # row 1?</P>Mon, 09 May 2022 14:41:59 GMThttps://techcommunity.microsoft.com/t5/excel/m-s-excel-vba/m-p/3344291#M145738Hans Vogelaar2022-05-09T14:41:59ZRe: M.S.EXCEL VBA
https://techcommunity.microsoft.com/t5/excel/m-s-excel-vba/m-p/3348594#M145818
YESTue, 10 May 2022 05:18:44 GMThttps://techcommunity.microsoft.com/t5/excel/m-s-excel-vba/m-p/3348594#M145818TARUNKANTI19642022-05-10T05:18:44ZRe: M.S.EXCEL VBA
https://techcommunity.microsoft.com/t5/excel/m-s-excel-vba/m-p/3349568#M145832
<P><LI-USER uid="1383603"></LI-USER> </P>
<P>In your first post, the data on the INVOICE sheet were in rows 15 to 39. Now they are in rows 12 to 36.</P>
<P>The total on the INVOICE sheet is in H44 instead of H45, and on the INVOICELIST sheet in column BB instead of AZ.</P>
<P>The invoice number on the INVOICE sheet is in F1 instead of in G1, and on the INVOICELIST in column A instead of in column B.</P>
<P>The invoice date has moved too.</P>
<P>So the code must be adjusted.</P>
<LI-CODE lang="visual-basic">Sub SaveData()
Dim ws As Worksheet
Dim wt As Worksheet
Dim s As Long
Dim t As Long
Dim r As Long
Application.ScreenUpdating = False
Set ws = Worksheets("INVOICE")
Set wt = Worksheets("INVOICELIST")
' Find first empty row in columns D:AL on sheet INVOICELIST
t = wt.Range("D:AY").Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
' Copy Item Name and Item Quantity
For s = 1 To 25
wt.Cells(t, 2 * s + 2).Value = ws.Range("D" & s + 11).Value
wt.Cells(t, 2 * s + 3).Value = ws.Range("E" & s + 11).Value
Next s
wt.Range("BB" & t).Value = ws.Range("H44").Value
'Copy inv no
wt.Range("A" & t).Value = ws.Range("F1").Value
'Copy Date
wt.Range("B" & t).Value = ws.Range("G2").Value
'Copy Company Name
wt.Range("C" & t).Value = ws.Range("D3").Value
' Clear Item Name and Item Quantity
ws.Range("D12:E36").ClearContents
' Optional: clear Invoice Number, Date and Company Name
ws.Range("D3,F1,G2").ClearContents
Application.ScreenUpdating = True
End Sub</LI-CODE>Tue, 10 May 2022 09:35:21 GMThttps://techcommunity.microsoft.com/t5/excel/m-s-excel-vba/m-p/3349568#M145832Hans Vogelaar2022-05-10T09:35:21ZRe: M.S.EXCEL VBA
https://techcommunity.microsoft.com/t5/excel/m-s-excel-vba/m-p/3356037#M145968
Respected Mr. Hans Vogelaar<BR />Many Many Thanks to You for Your Valuable TIPS for me. Again Thank You Sir.<BR />I shall be highly Obliged to You For help me.Wed, 11 May 2022 08:36:05 GMThttps://techcommunity.microsoft.com/t5/excel/m-s-excel-vba/m-p/3356037#M145968TARUNKANTI19642022-05-11T08:36:05Z