Solved: extract data from master sheet to separate sheets

[WINFS]

I have a master worksheet contains entries with date of the invoice, invoiced amount, date of payment and amount paid. I want to extract the entries
of the respective months into separate worksheets (please refer to the file enclosed).

I could have manually copy and paste the entries into the separate worksheets. Is there a way to automate it?

Thanks

[OBP]

Yes it can be automated. There are actually quite a few "transfer" data posts on this forum already, if you conduct a quick search I am sure that you can find something that you can modify to do what you want, if not come back on here and we will create something.
If this is just a "one off" transfer you could do the first copy and paste manually with the "Macro Recorder" running to record the VBA code and then just copy and paste the code to modify what data needs transferring.

[WINFS]

I am a novice in vba. I have searched a number of posts but unfortunately I do not know how to modify the codes.



I need to transfer the data as when new entries are entered.

Any help will be highly appreciated. Thanks

[mdmackillop]

Is this a one-off to extract data and create the month sheets, or are you looking only to extract the relevant data on the completion of each month. Please explain clearly how this is to be used.
Should all sheets have totals in the same cell references for analysis purposes?

[WINFS]

This is not a one-off transfer. The user will input data into the "master" worksheet everyday. The user will then run the macro to extract the relevant data to the respective worksheets (as per the heading) to analyse the sales and collection for the month. And yes, all sheets have totals in the same cell references for analysis purposes. I hope this explaination will be useful. Thanks for your assistance.

[OBP]

WINFS, the automated transfer is possible but your layout on the "Month" sheets will make life very difficult.
It would be better if the second set of data was beside the first set of data as in the master sheet.
It would make even more sense to do this in Access of course.
Can you clarify that in the case of customer cccc that his "Invoice" would end up in January's sheet, but his receipt would end up in February's sheet?
Should the title in cell C10 in the Month sheets be "Receipts"?

[mdmackillop]

WINFS, the automated transfer is possible but your layout on the "Month" sheets will make life very difficult.
It would be better if the second set of data was beside the first set of data as in the master sheet.
:yes

[OBP]

md, are you working on this at the moment?
As I am.:content:
It isn't worth both of us doing it and your VBA is better than mine.
I have extracted the worksheet name from the Invoice date and am moving on the the loop part.

[mdmackillop]

Happy to leave this one to you or help out if required.

[OBP]

OK, I will carry on, you can clean up my VBA when I have finished LOL.

[OBP]

WINFS and md, here is the first phase, sorry for the delay, I am playing a Poker Tournament at the same time as coding.
This code transfers the Invocies section. As I basically intend copying it for the receipts section perhaps md can suggest some improvements before I code it.
It might be advisable to have an indicator set as the data is transferred that says the data has been transferred to speed things up a bit in subsequent months.
I also think the "Sum" on the months sheets would be better put in after the transfer as the number of entries is not known.

[OBP]

This version does the transfer exactly as WINFS requested it to be done, it does not clear the area first so subsequent runs will continue down the page.
Until a decision is made about the layout of the "Month" sheets it is not worth doing any more. This demostrates how it can be done though.

[WINFS]

OBP: Should the title in cell C10 in the Month sheets be "Receipts"? Yes. Sorry for the typo error


WINFS, the automated transfer is possible but your layout on the "Month" sheets will make life very difficult.It would be better if the second set of data was beside the first set of data as in the master sheet. I'm open to that idea, as long as the user is able to see how much is invoiced to whom and how much is received from whom for a particular month. But, it seems that your have already successfully written the codes to transfer the data based on my initial
"Month" sheet format.


Can you clarify that in the case of customer cccc that his "Invoice" would end up in January's sheet, but his receipt would end up in February's sheet? Yes, because receipt may be in the following months.


It might be advisable to have an indicator set as the data is transferred that says the data has been transferred to speed things up a bit in subsequent months. Good idea! In fact, i am thinking to include 2 options, i.e. (1) global transfer, transfer the all entries in the master sheet to the respective month sheets (2) monthly transfer, just transfer the entries of a particular month. Hopefully, this is too difficult to do


I also think the "Sum" on the months sheets would be better put in after the transfer as the number of entries is not known. I need the "Sum" on the months sheets as the user is not suppose to change anything on the month sheets. To tackle this issue, I intend to include the "Total" in column c besides the "Sum" and add the following codes to the month sheets which will automatically insert new row

Private Sub Worksheet_Change(ByVal Target As Range)
r = Target.Row
c = Target.Column
If c <> 3 Then Exit Sub
Application.EnableEvents = False
NextLineValue = Cells(r + 2, c)
If NextLineValue = "Total" Then
Rows(r + 1).Insert
End If
Application.EnableEvents = True

End Sub [credit goes to 1h1 of www.ozgrid.com (http://www.ozgrid.com) for the above codes]

[OBP]

How many rows of data do you think that you will get each month?
mdmackillop has a faster and more compact routine than mine.:bow:
So if it is a lot of rows then you should use his routine with a bit of modification.
His is more Visual Basic whereas mine is decidedly BASIC, but at least it does work:ohwell:.

[mdmackillop]

Hi OBP,
I was doing exactly the same as you a short time ago, and the more you work at it, the simpler it becomes.

[mdmackillop]

Here's a filter solution, capable of dealing with large numbers of rows. Probably a bit overkill here, but shows the methodology. This will overwrite the data in the Month Sheets each time the code is run.

Option Explicit
Dim shMaster As Worksheet
Sub Macro1()
Dim LRw As Long
Dim sh As Worksheet
Dim Chk As Range
Dim i As Long

Application.ScreenUpdating = False
'Get Master Sheet
Set shMaster = Sheets("Master")
'Determine last row of data
LRw = Cells(Rows.Count, 1).End(xlUp).Row
'Add month number to spare column as filter field
Range(Cells(4, 8), Cells(LRw, 8)).FormulaR1C1 = "=MONTH(RC[-6])"
'Loop through months
For i = 1 To 12
'Filter for values
Range("A3:H" & LRw).AutoFilter Field:=8, Criteria1:=i
'Exit if no data returned
If Range("A4:G" & LRw).SpecialCells(xlCellTypeVisible).Count = 0 Then
Exit For
End If
'Check for existence of sheet; add if required
On Error Resume Next
Set sh = Sheets(MonthName((i), True) & " 06")
If Err.Number <> 0 Then AddSheet i
Err.Clear
'Copy data to Month sheet
Range("A4:G" & LRw).Copy Sheets(MonthName(i, True) & " 06").Range("A4")
Next
'Tidy up
Range("A3:H" & LRw).AutoFilter
Columns(8).ClearContents
Set shMaster = Nothing
Application.ScreenUpdating = True
End Sub
Sub AddSheet(i As Long)
Sheets.Add after:=Sheets(Sheets.Count)
ActiveSheet.Name = MonthName((i), True) & " 06"
shMaster.Range("A1:G3").Copy Range("A1")
shMaster.Activate
End Sub

[WINFS]

Hi mdmackillop's, thank you very much for the codes. However, the filter have misplaced the receipt entries as it was not transfered based on the receipt date. For example customer cccc his "Invoice" would end up in January's sheet, but his receipt would end up in February's sheet based.

I will try to modify OBP and mdmackillop's codes and hopefully it will works. Thanks.

[mdmackillop]

I was aware of that. My code would require to be run twice; once on the Sales, once on the Collection. The addresses are easily changed to suit.

[WINFS]

Hi I have tried to modify OBP's vba codes into the actual worksheet. But the data failed to transfer properly to the Month sheets. Only record 1, 4, 5 and 8 are transferred. Could you please help me to looking into the codes? thanks.

[OBP]

The first point is that it is transferring all of the records, what it is doing is overwriting the same cell each time with the new data.
This is being caused by the Blank Cells at B8 and B29, which are not present on the Worksheets that I posted. If you put something in those cells you will that it works OK. Like the attachment.

[WINFS]

Thank you very much for the suggestion. Why the blank cells caused such strange behavior? Is there any other alternative solution?

[nandhamnk]

Hi,

Please provide me link for Download

Thanks
MNK


I have a master worksheet contains entries with date of the invoice, invoiced amount, date of payment and amount paid. I want to extract the entries
of the respective months into separate worksheets (please refer to the file enclosed).

I could have manually copy and paste the entries into the separate worksheets. Is there a way to automate it?

Thanks