copy data from main sheet to respective sub sheets

[sshanku1985]

Hi...I am looking for someone to help me on this problem of mine...I am running a small business of timber and plywood and I usually record my daily sales in a workbook which has a main sheet for recording daily sales [having multiple columns] and some other sheets for particular customer credit a/cs....I want the customers credit info from the daily sales sheet to get entered in the customer's respective worksheet automatically....I have tried to use advance filter but that does not add the data automatically everytime I update the daily sales sheet..My daily sales worksheet look a bit like this

Date cash/credit CustomerName Item Qnty[in cft] Rate[per cft] Amount
03/10/2011 credit xyz 4x1.5 2.0355 100 203.55
03/10/2011 cash cash 5x1.25 1.0000 200 200.00
03/10/2011 credit abc 3x1.5 3.0000 300 900.00

and so on.......and my cust a/c worksheet have columns like this...
Date Item Qnty[in cft] Rate[per cft] Amount


Here xyz and abc are customers name with whose names i have a worksheets and i want their respective entries to get copied in their particular worksheets whereas i don't want the cash sales to get entered anywhere else...

Can anyone help me with this please.....????

[MarvinP]

Hi sshanku1985 and welcome to the forum.
We need a sample xls file with your data to understand it a bit better.
You can attach a sample workbook by clicking on "Go Advanced" under the message area and then on the Paper Clip Icon above the message. This will open a dialog box to attach your sample file. You problem doesn't seem that hard but typing in the data is a pain.

[sshanku1985]

HEY thanks Marvin for giving a look at my issue...I am attaching a sample file here for consideration...!!!

[JBeaucaire]

This macro will do it "on demand". Place into a normal code module in the VBEditor (Insert Module) and attach to a button on DAILY SALES if you wish.

It will clear the current report on each secondary sheet then paste in all the rows that exactly match that sheetname in column A.

Option Explicit

Sub UpdateSheets()
Dim wsDest As Worksheet
Dim wsData As Worksheet:    Set wsData = ThisWorkbook.Sheets("DAILY SALES")
Dim LR As Long

Application.ScreenUpdating = False

With wsData
    .Columns("A:A").SpecialCells(xlCellTypeBlanks) = 1
    .AutoFilterMode = False
    .Rows(2).AutoFilter
    
    For Each wsDest In ThisWorkbook.Worksheets
        If wsDest.Name <> wsData.Name Then
            .Rows(2).AutoFilter 1, wsDest.Name
            LR = .Range("A" & .Rows.Count).End(xlUp).Row
            If LR > 2 Then
                wsDest.Range("A3:Y" & wsDest.Rows.Count).ClearContents
                .Range("B3:Z" & LR).Copy wsDest.Range("A3")
            End If
        End If
    Next wsDest
    
    .AutoFilterMode = False
    .Columns("A:A").SpecialCells(xlConstants, xlNumbers) = ""
End With

Application.ScreenUpdating = True
End Sub

[sshanku1985]

Hey thank u so much Jerry...u have been a gr8 help....i want to know one more thing from you....can we solve this problem with the help of some formulas as well...if we can and you know how to do that [which i m sure u do] then please share that with me...i ll be obliged....!!!

[JBeaucaire]

That's such an odd request for a question that is clearly posted in the programming forum.

[sshanku1985]

i know thats an odd thing jerry...but i thought may be u can help me wid that as i have already posted the same issue of mine in a different forum as well but didnt got any reply...thats why when you gave me a solution I saw a hope....anyways if u dont mind please let me know a solution I asked u abt...that will so nice of you.....

[MarvinP]

Hi,

Try this. Name the Range of all your Daily Sales with something like DataTable. Then create an Advanced Filter Criteria on each tab as shown in the attached. Go to the tabbed page and perform an Advanced Filter and for the range, type in DataTable, use the criteria on the top of that sheet and set the output to A4 on that sheet.

You won't even need formulas if you do this method. You will have to name the range of your data on the Daily Sales sheet and perform Advanced Filters.

See the attached.

[MarvinP]

Hi,
After posting the last attached, I decided to make it better. You don't really need separate tabs for each customer. You simply need to change the customer name in cell A2 and recalculate the advanced filter. I recorded a macro to do this and put a button on the worksheet to perform the filter. Another improvement is a Dynamic Named Range on the data. I created a DynamicDataTable name that will expand the size of the table depending on how many lines are in your data.

Find the attached.

[sshanku1985]

Hey Marvin thanks for all your help dude.....I want to know one more thing.....can there be a way that data from the daily sales automatically get added in the respective sheets automatically without doing an advance filter.....may be we can use some formulas as well to do this....???

[MarvinP]

Very few things in this world are done "automatically". Formulas are what you mean but Microsoft invented Database functions, Auto Filters, Advanced Filters and finally Pivot Tables because SumProduct and Array Functions just can't do everything.

The real hard part is to understand the easiest way and follow it.

[sshanku1985]

I think you are right Marvin....You have told me the easiest way and i m surely going to follow that....m8 u have been a gr8 help....looking forward for your help in future as well.....

[sshanku1985]

I am quite new to VBA...so can u let me know from where i can get lessons on internet to get a bit more familiar with VBA and macros.....??

[JBeaucaire]

I wouldn't do what you've described with formulas.

[JBeaucaire]

I'm sure you can Google many fine resources. I get most of my learning from interacting here on the forums with people regarding real-world needs.

When you have a real-world need, try turning on the macro-record and letting it record you doing something you can do manually. Then try the resulting code and see if it works for you in the same situation on a different data set.

Then post that macro here on the forum with your sample workbook and let people here revise it for you. What happens here is now you have two macros to compare. The one you created you fully understand because it's a macro of what you did, so when you compare that to a better written macro you will understand the new things you see much more quickly.