How to develop a macros to auto copy and save data on series basis to another sheet

Hi Friends,
I have made one sheet for retails for billing in which I have made one button which will print the sheet and will also save it automatically but there are two more commands that I wanna develop in it & that is the names & the numbers of the customer that are taken during the billing should also move to the different sheet and should get saved in front of respective bill no & on the same sheet where the cell that contains bill no. a new bill no. should appears from the series.

For more clarity I have attached an excel file and its snap shotProblem Sheet.png.

Please help..

Thanks in advance.

Originally Posted by RudiS

Hi,

Try this attached version...

Hi Rudis
Thats Fantabulous, Great job in such a less time you solved my 90% problem, Rudis I am pasting your coding here that you recorded on this sheet, could you let me know from where should I edit if I want to exceed the series eg from 2 to 10000 & as previously mentioned I want that bill no should also get changed and new bill no. from the series should automatically appear in the field appearing in the "bill" sheet.
AND
I request you, if possible, could you please let me know how you created such a sophisticated macros.

PSB.

Private Sub CommandButton1_Click()
Application.ScreenUpdating = False
Worksheets("Bill").Range("B3").Copy
Worksheets("Customer Records").Range("C" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
Worksheets("Bill").Range("B4").Copy
Worksheets("Customer Records").Range("D" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
Range("A1:J21").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, IgnorePrintAreas:=False
Worksheets("Bill").Range("B3:B4").ClearContents
Worksheets("Customer Records").Range("C" & Rows.Count).End(xlUp).Offset(1, -1).Copy
Worksheets("Bill").Range("B2").PasteSpecial xlPasteValues
Application.CutCopyMode = False
Application.ScreenUpdating = True
ActiveWorkbook.Save
End Sub

Once again very thanks

Originally Posted by RudiS

Quote: could you let me know from where should I edit if I want to exceed the series eg from 2 to 10000
The code will work fine no matter how long your list becomes on the Customer Records sheet. It will always put the customer name and number at the next blank row at the bottom of your list.

The only area of the macro to modify (if necessary) will be the fixed range from your original macro:
Range("A1:J21").Select
I don't know if the structure of your printout is going to change? Ideally it should not as the macro is referencing range B2:B4 on the Bill sheet.

Quote: could you please let me know how you created such a sophisticated macros
The lines of code I added to your print macro was not recorded. I had to write them into your macro so that they work dynamically with the growing size of the Customer Records worksheet. If you work with macros everyday, you get to learn the code syntax and writing them becomes easier and more flexible than recording them.

Hi Rudis,
Thank you once again, I would just like to mention here that the file attached was a rough specimen of the original file, In main file there might be some columns shifted ahead eg. headings appearing in B column might will be in E column & A column will be in F, if I need to adjust it in original file will I be able to do so, as I am not well versed in macros so need some more direction like what areas will be more to be taken into the consideration while shifting the codes into original file so that the code should be running successfully in the original file.
Sorry for this repetitive queries as I am not well conversant with writing macros so need more clarity..

Regards,

Hey Rudis,
I am very grateful to you, you really resolved my problem in such a less than my expectation, surely I will follow your given direction & if will have any problem I will surly get back to you.
Due to people like you this forum is successful among the excel maniacs.

Still Thanks for time and efforts.
Have a great day ahead !!

Regards,

Originally Posted by RudiS

The macro is fairly straight forward to understand...even without much VBA knowledge. It reads like english (At least I think).
I would say...give it a shot. Place the macro in your actual file and when you carefully read the macro, simply change the cell references in the macro to point to the correct cells...for example:
This:
Worksheets("Bill").Range("B3").Copy
Could become this:
Worksheets("NewBill").Range("E3").Copy

You only really need to change the "fixed" cell references or sheet names as in the above example...the rest of the code can be left as is!

If you have tried and things don't work out well, you can create another post here...and I'm sure I or someone else will be happy to help.