Adding Records to Different Worksheet in the work book - based on Date - month wise

[Pramod KLN]

Hi Every one,

I am a new member in to the family of Mr.Excel, and also new to VBA programming. I have created a workbook for Sales Data Entry. Which contains different worksheets, namely "Party Details" and month wise sheets for sales Data as Apr-17, May-Say and so on.

I have created a userform to enter customer details in to the worksheet "Party Details" and it is working fine.

I have created a userform for Sales Data Entry which picks Party Name from "Party Details" , other fields are S.No. Invoice Date Invoice Amount.

Now what I want is Whenever I Enter the Invoice Date, it should open that particular month Sheet, go to last row and update Next S.No, Invoice No, Invoice Date, Invoice Amount etc,,

Is it is possible to write VBA code for this?

I have created different sales sheets month wise only file monthly Sales Returns easily.

The otherway round is keep only worksheet for the sales Data entry and later filter month wise.

Thanking you in advance.

Pramod

 

[ajamess]

I can think of two ways I would go about this. Either I would have hard coded logic like:

dim yoursheet as worksheet

if month(Invoicedate) = 1 then
    set yoursheet =workbooks("Workbook.xlsx").Worksheets("January")
Elseif month(Invoicedate) = 2 then
    set yoursheet =workbooks("Workbook.xlsx").Worksheets("February")
...

Endif

or a more delicate solution, is doing something like

    dim yoursheet as worksheet
    set yoursheet =workbooks("Workbook.xlsx").Worksheets(Monthname(month(InvoiceDate)))
    ..... whatever operations on yoursheet here

If your tabs are just called january etc the above will work, of can combine with other things like

     set yoursheet =workbooks("Workbook.xlsx").Worksheets(Monthname(month(InvoiceDate)) & " Data")

You could also just have a key on a hidden sheet that has the name of each sheet based on each month, and do an index match. There are a lot of options