Microsoft Excell 97

  Neil-205596 13:39 08 Mar 2004
Locked

Can you please help me I am creating an automated invoice system for my wife and ahve created all the vertical look up tables etc and the only item i cannot solve is copying the invoice to store for future reference ie to recreate an invoice or to work out cost for the year etc

I have created the macro to copy the info from the invoice and copy it it the chosen area in the spreadsheet which is named Old Invoices and have used end down then end down again followed by end up to bring the cell to the bottom row however when i request the cell to move 1 down ie immediately below the previous invoice it copies over the previous invoice therefore not getting a full history of invoices produced.

I cannot get excell to record the last down request

Does anybody know how to get round this

Many Thanks

  anon1 14:19 08 Mar 2004

I can't help you with your request but if you go to the microsft.public newsgoups you will find a group specifically for excell and some very knowledgable mvp's to help.

  VoG II 14:46 08 Mar 2004

This will find the last used row in Column A

LastRow = Cells(Cells.Rows.Count, 1).End(xlUp).Row

  VoG II 17:26 08 Mar 2004

Via e-mail

Thanks for your email however you are saying this will find last cell used however how do i get the next cell down ie the first blank cell so that i can build up a history to avoid overwriting previous entries Thanks again

===========================================

Please respond to this thread, not by e-mail which is for private correspondence between members.

The first blank cell in column A is given by

BlankRow = Cells(Cells.Rows.Count, 1).End(xlUp).Row


I presume that you have been using the macro recorder. If you look at the code generated (ALT+F11 to open the Visual Basic Editor) you will find that it has translated your keystrokes into absolute cell addresses. If you want to paste the relevant lines of code here, we can show you how to change the macro to achieve what you want.

  VoG II 17:27 08 Mar 2004

BlankRow = Cells(Cells.Rows.Count, 1).End(xlUp).Row + 1

  Neil-205596 14:10 09 Mar 2004

I am new to this macro building
Where do i record this string also how do i tell the macro to go to the "blank Row" in the named area and paste therethe contents.

Also the sting i assume is as stated i dont have to specift which cell or rows etc do i

Sorry about email before

Neil

  VoG II 16:37 09 Mar 2004

ALT+F11 to open the Visual Basic Editor. In the left hand pane, expand as necessary to show the module(s) that you have created. Double click to show the contents of your module (the latest one).

Select all of the code, press CTRL+C to copy, then use CTRL+V to paste the code here. We can then show you how to modify it to select a blank row each time.

  Neil-205596 16:43 09 Mar 2004

Application.Goto Reference:="INVOICE"
ActiveSheet.PageSetup.PrintArea = "$A$1:$E$47"
ActiveWindow.SelectedSheets.PrintOut Copies:=2, Collate:=True
ActiveWorkbook.Save
Range("F14:J14").Select
Selection.Copy
Application.Goto Reference:="Old_Invoice_Location"
lastRow = Cells(Cells.Rows.Count, 1).End(xlUp).Row + 1
Application.CommandBars("Exit Design Mode").Visible = False
ActiveWindow.SmallScroll Down:=-2
Range("L3").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Sheets("Invoice").Select
ActiveWindow.SmallScroll ToRight:=-4
Range("E5").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("A14:A42").Select
Selection.ClearContents
ActiveWindow.ScrollRow = 1
Range("C14:C42").Select
Selection.ClearContents
Range("F1").Select
Selection.Copy
Range("I1").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
End Sub

  VoG II 16:57 09 Mar 2004

I'm assuming that the first time it should paste into L3, next time L4 etc. Here is the modified code to do this:

Sub test()

Application.Goto Reference:="INVOICE"

ActiveSheet.PageSetup.PrintArea = "$A$1:$E$47"

ActiveWindow.SelectedSheets.PrintOut Copies:=2, Collate:=True

ActiveWorkbook.Save

Range("F14:J14").Select

Selection.Copy

Application.Goto eference:="Old_Invoice_Location"

Application.CommandBars("Exit Design Mode").Visible = False

ActiveWindow.SmallScroll Down:=-2

'changes are here

lastrow = Cells(Cells.Rows.Count, 12).End(xlUp).Row + 1

Range("L" & lastrow).Select

'end of changes

Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

Sheets("Invoice").Select

ActiveWindow.SmallScroll ToRight:=-4

Range("E5").Select

Application.CutCopyMode = False

Selection.ClearContents

Range("A14:A42").Select

Selection.ClearContents

ActiveWindow.ScrollRow = 1

Range("C14:C42").Select

Selection.ClearContents

Range("F1").Select

Selection.Copy

Range("I1").Select

Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

End Sub

  Neil-205596 09:10 10 Mar 2004

Thank you Very Much Indeed

Neil

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

AMD Radeon Adrenalin release date, new features, compatible graphics cards

Turn a photo into 16-bit pixel art

iMac Pro release date, UK price & specs

Football : comment regarder la Ligue 1 en direct ?