Copy invoice dates to ledger

Closed
aneela1 Posts 2 Registration date Monday November 23, 2015 Status Member Last seen November 27, 2015 - Nov 23, 2015 at 03:02 PM
vcoolio Posts 1404 Registration date Thursday July 24, 2014 Status Moderator Last seen September 15, 2023 - Nov 28, 2015 at 05:53 AM
@vcoolio
Read your reply in an earlier post. copied it to my use, but this did not work. please help.
I am copying a list of cell entries from invoice to ledger using vba. All the copied entries must have the same date as copied from the invoice sheet to the ledger sheet(per use of button).
The next time the button is clicked, it should copy to the next empty rows on ledger sheet. Please help me with this. I cannot seem to get the date which is cell I5, to be copied against all the entries in Column A.

Also for the ledger, I want it to print statement for a user defined period, say 01 Jan 2014 to 01 Jan 2015 or randomly such as 12 April 2015 to 12 May 2015. Please help on that also.

Code:

Sub Button1_click()

Set wb1 = ThisWorkbook
Set ws1 = wb1.Sheets("Invoice")
Dim v As String
Dim wb As String
Const sPath As String = "F:\Software\"

v = ws1.Range("A5").Value
wb = v & ".xlsx"
Workbooks.Open sPath & v & ".xlsx"
nxtRw = Sheets("Ledger").Range("B" & Rows.Count).End(xlUp).Row + 1

ws1.Range("B10:B24").Copy
Workbooks(wb).Sheets("Ledger").Range("D" & nxtRw).PasteSpecial Paste:=xlValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False

ws1.Range("G10:G24").Copy
Workbooks(wb).Sheets("Ledger").Range("C" & nxtRw).PasteSpecial Paste:=xlValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False

ws1.Range("H10:H24").Copy
Workbooks(wb).Sheets("Ledger").Range("I" & nxtRw).PasteSpecial Paste:=xlValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False

ws1.Range("I10:I24").Copy
Workbooks(wb).Sheets("Ledger").Range("J" & nxtRw).PasteSpecial Paste:=xlValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False

Dim rngCell As Range
Dim lrow As Long
Dim rng As Range

lrow = ws1.Cells(Rows.Count, 2).End(xlUp).Row
For Each rng In ws1.Range("B10:B" & lrow)
If Len(rng) > 0 Then
ws1.Range("I5").Copy
Workbooks(wb).Sheets("Ledger").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
End If
Next
End Sub

_____

It copies 23 entries from the ws1 workbook, whereas I want to copy only B10:B24, also it does not omit empty cells.
Related:

2 responses

vcoolio Posts 1404 Registration date Thursday July 24, 2014 Status Moderator Last seen September 15, 2023 259
Nov 24, 2015 at 12:24 AM
Hello Aneela,

Could you please upload a sample of your work book so we can see exactly what you are trying to do. Change any sensitive data to dummy data.

You can upload a sample using a free file sharing site such as DropBox, ge.tt or SpeedyShare. Just post back with the link to the file.

Cheerio,
vcoolio.
0
aneela1 Posts 2 Registration date Monday November 23, 2015 Status Member Last seen November 27, 2015
Nov 27, 2015 at 01:23 PM
There has been some help on the original problem. Please read the issues as below:

1. invoice.xlsm has 15 rows where user can enter the data to be copied to ledger.xlsx, but if there are less than 15 items on the invoice, say 10 or 2, it still copies the data for 15 entries onto the ledger.Please help with that,i.e it should copy data for the cells that are not empty and leave the empty ones.

2. The number of rows in Column D for ledger.xlsx is 32(for A4 printing). Please could you:
a. ask the user to print the current worksheet(when the current one is full)
b. add another worksheet to the workbook ledger for new copying and rename the current sheet.

3. Filter the ledger sheets as per the dates specified by user, say 1 April 2015 to 14 June 2015 or 10 June 1999 to 09 Dec 2010. Any random dates, but it should give the data within the specified dates and print with the headers(just as on a single sheet of ledger).

The files are available at:
https://www.dropbox.com/s/qhsh6x7e2g2fld2/invoice.xlsm?dl=0
https://www.dropbox.com/s/2pzhdbodsbur227/Ledger.xlsx?dl=0


This has not been cross-posted at https://www.excelguru.ca/forums/showthread.php?5193-Excel-Vba-copy-list-of-entries


Thank you

Truly,
Aneela
0
vcoolio Posts 1404 Registration date Thursday July 24, 2014 Status Moderator Last seen September 15, 2023 259
Nov 28, 2015 at 05:53 AM
Hello Aneela,

Firstly, as you have received help from someone else on another forum, then, to be fair to that person, you'll have to go back to that forum and complete your query there. This thread will hence be marked as Solved/Closed.

Secondly, I have quickly put something together for you which you may take back to the other forum. Its not tested but I have attached a link to a copy of your work book (Invoice) for you to see what can be done. The sample work book contains both Invoice and Ledger sheets ( I don't think that you need a work book for both). However, if you insist on two work books then, as you know how to access the second work book with the filepath code, you should be able to take it from there.

The sample work book copies the Invoice data to the next empty row in the Ledger sheet. It then clears the contents of the invoice form ready for any new entries. Thus, it will basically be a template which you can use over and over again. There is no need to create new sheets for this.

I hope that this at least leads you in the right direction. Following is the link:-

https://www.dropbox.com/s/yvikqhmk0ypgyix/Aneela.xlsm?dl=0

The code is in Module 1.

Good luck with it all.

Cheerio,
vcoolio.
0