my code is not offsetting when its pasting the code to the destination it just overwrites what was there before this is my whole code for copying and pasting
Please Login or Register to view this content.
my code is not offsetting when its pasting the code to the destination it just overwrites what was there before this is my whole code for copying and pasting
Please Login or Register to view this content.
I can't see where you use "nextrow" to set the DstRng.
Regards, TMS
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
thats the whole codePlease Login or Register to view this content.
I'm confused by your code. At the beginning, you set DstRng based on column 1
Then, later, you determine nextrow based on column 18Please Login or Register to view this content.
Please Login or Register to view this content.
and then you copy and paste the data (to the original DstRng)
Please Login or Register to view this content.
I'm probably missing something, but I still don't see how your're using nextrow.
here is my file for review
https://www.dropbox.com/s/z9qkgxe2x7...orum.xlsm?dl=0
this is the original code that works on another workbook but just the sheet numbers have changed but i dont know why i can get this to work on my bookPlease Login or Register to view this content.
here it isPlease Login or Register to view this content.
Could someone help please
im still at a lose, its just over writing and not filling down when new invoice is added to the database
Last edited by Learning ExL; 04-19-2015 at 12:29 PM.
OK
This line:does this ...Please Login or Register to view this content.
With Sheet16 (which is Invoice Database) go up from cell A1048576 (the last row) and then offset from there 18 rows and 1 column. So, A1048576 up gives you cell A1. Cell A1, offset 18 rows and 1 column, gives you cell B19 ... every time. I still do not understand what you are doing with nextrow; it seems to have no purpose. If you want the next cell in column B as your destination range, you should be usingPlease Login or Register to view this content.
Regards, TMS
and it still overrights that last entries and not continue down creating a list
and it still overrights that last entries and not continue down creating a list
above equals col BPlease Login or Register to view this content.
above equal B19Please Login or Register to view this content.
but why does it not find the last row and enter the detail in the next empty row
ok i used this and it entered the datails in row B19 then entered an new invoice and it over rote it
Please Login or Register to view this content.
I really do not know if this helps you but I use it for pasting data after a specific row.
'change the rango for yr needs
Please Login or Register to view this content.
Regards, John55
If you have issues with Code I've provided, I appreciate your feedback.
In the event Code provided resolves your issue, please mark your Thread as SOLVED.
If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.
...enjoy -funny parrots-
Last attempt ...
This:
Needs to be this:Please Login or Register to view this content.
Please Login or Register to view this content.
This:
Please Login or Register to view this content.
is the last row in column 1 ... column A.
I cannot say this a different way.
This part of the code is supposed to do the work
Well it does on another work book but on this book it does not 😠Please Login or Register to view this content.
Different sheet, different column. Otherwise, perfect.
I cannot upload the workbook, it is too big. But here is the amended code:
Please Login or Register to view this content.
And here is the output:
B C D E F 10 Totals OutStandings £ 1,748.40 Statement 11 Customer Payee 12 Nampak10 Removed for security 13 Removed for security 14 Jenna Way Removed for security 15 NEWPORT PAGENLL Removed for security 16 MK16 9QJ Removed for security 17 Removed for security 18 Date Agency Agency ID InvNo Total 19 19/04/15 xxx 9 £ 126.90 20 19/04/15 xxx #N/A 9 £ 149.46 21 19/04/15 xxx #N/A 9 £ 149.46 22 19/04/15 xxx #N/A 9 £ 149.46 23 19/04/15 xxx #N/A 9 £ 149.46 24 19/04/15 xxx #N/A 9 £ 149.46 25 19/04/15 yyyy 10 £ 126.90 26 19/04/15 yyyy #N/A 10 £ 149.46 27 19/04/15 yyyy #N/A 10 £ 149.46 28 19/04/15 yyyy #N/A 10 £ 149.46 29 19/04/15 yyyy #N/A 10 £ 149.46 30 19/04/15 yyyy #N/A 10 £ 149.46 31
Regards, TMS
Last edited by TMS; 04-19-2015 at 04:49 PM.
Thank you sir did you by any chance have a play with the add credit as well I know it was not part of this post
Thanks for the rep.
Unless you want to test column 5 (column E), which would be the Invoice Number column, then I would guess this:
Please Login or Register to view this content.
should, again, be this:
Please Login or Register to view this content.
Regards, TMS
Mmm, if you change the code as above (at least, for consistency), you also need:
Please Login or Register to view this content.
Regards, TMS
If you are satisfied with the solution(s) provided, please mark your thread as Solved.
New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
Or you can use this way:
How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save
You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks