Transferring master wsheet info into multiple worksheets

[CarolG]

I have created 10 worksheets in a Excel 2003 Workbook. The following is what I am trying to achieve and would like some advice or help on how to go about this if possible. I am an average user and do not know Visual Basic. Can anyone help me please.

Worksheets 1 to 10 are named as follows:

1. Name = CUSTOMER which is all the customer details
2. Name = MASTER which is a master list of products to be shipped overseas in Containers.
3. Name = CONTAINER 1 which will be a list of the products from the master list to go into Container 1.
4. Name = CONTAINER 2 which will be a list of the products from the master list to go into Container 2.
5. Name = CONTAINER 3 which will be a list of the products from the master list to go into Container 3.
6. Name = CONTAINER 4 which will be a list of the products from the master list to go into Container 4.
7. Name = PACKLIST 1 and will be the enclosed list of items in Container 1 with Customer Details
8. Name = PACKLIST 2 and will be the enclosed list of items in Container 1 with Customer Details
9. Name = PACKLIST 3 and will be the enclosed list of items in Container 1 with Customer Details
10. Name = PACKLIST 4 and will be the enclosed list of items in Container 1 with Customer Details

Note: The MASTER and CONTAINER worksheets all have the same Headings.

(a) In Column 1 of the MASTER and CONTAINER worksheets is a heading called Container No Allocated. Once the MASTER list has been completed, I allocate a CONTAINER NO. from 1 to 4 to each of the items in the Master List. The numbers will not necessarily be in Ascending Order (Maybe Filter this column to display the list of all products in Container 2????).
(b) I then want all the items in each Container to be listed in the corresponding worksheets CONTAINER 1,2,3,4.
(c) From each of the CONTAINER 1,2,3,4 worksheets, I then want to take two columns of data and put them into 2 columns in the PACKLIST 1,2,3,4 worksheets which is the Quantity and Description headings.
Note: The size of the columns in the PACKLIST worksheets are different to the column sizes in the CONTAINER worksheets.

Thanks Carol

 

[lozzablake]

This is pretty easy to code in VBA. When you said:

"Note: The size of the columns in the PACKLIST worksheets are different to the column sizes in the CONTAINER worksheets"

I assume the number of columns rather than their size. If you give details of which columns you require to be copied I will sned you an example of how it might be done

 

[CarolG]

Thanks for your reply. If you like I can email you the spreadsheet. However in answer to your question…..”I assume the number of columns rather than their size.....

The PACKLIST 1,2,3,4 are a proforma to print the Packing Slips. I require the cells from CONTAINER 1 etc B5:C5 or however many rows there are (e.g. if 10 rows that would equal B5:C14) to be entered into PACKLIST 1 etc worksheets. The cells in the PACKLIST worksheets are B26:C26 or however many rows there are (e.g. if 10 rows that would equal B26:C35). These do not have the same Row or Column number or sizes.

Since I wrote the Manager has requested another Worksheet which is a one line summary of each of the Container Items. I have called this worksheet CONTAINER SUMMARY. The information needs to come from CONTAINER 1 cells A5 to L5 and the same for each e.g. CONTAINER 2,3,4 totalling 4 rows in the CONTAINER SUMMARY worksheet. They have the same number of Columns and Row sizes.

I hope this all makes sense for you to help me. It would most appreciated. Regards Carol.

 

[lozzablake]

OK. I think this is what you require. You will need to test this. If you get a 'subscript out of range' error then this means that there is a worksheet name mis-spelled so check these first. If the macro is not behaving as you expect let me know and I will change where necessary

Sub CopyData()

    Dim Containers(1 To 4) As Range  'this is an array that will hold the ranges for the four different containers
    Dim i As Integer
    Dim intLastCol As Integer
    Dim intLastRow As Integer
    
    With Sheets("Master")
    'loop through all the rows on sheet and allocate to each range
        intLastCol = .Range("IV1").End(xlToLeft).Column
        intLastRow = .Range("a65536").End(xlUp).Row
        For i = 2 To intLastRow
            'determine which container
            .Activate
            Select Case .Cells(i, 1)
                Case Is = 1
                    'check if anything has been assigned to the array yet
                    If Containers(1) Is Nothing Then
                        Set Containers(1) = .Range(Cells(i, 1), Cells(i, intLastCol))
                    Else
                        Set Containers(1) = Application.Union(Containers(1), .Range(Cells(i, 1), Cells(i, intLastCol)))
                    End If
                Case Is = 2
                    If Containers(2) Is Nothing Then
                        Set Containers(2) = .Range(Cells(i, 1), Cells(i, intLastCol))
                    Else
                        Set Containers(2) = Application.Union(Containers(2), .Range(Cells(i, 1), Cells(i, intLastCol)))
                    End If
                Case Is = 3
                    If Containers(3) Is Nothing Then
                        Set Containers(3) = .Range(Cells(i, 1), Cells(i, intLastCol))
                    Else
                        Set Containers(3) = Application.Union(Containers(3), .Range(Cells(i, 1), Cells(i, intLastCol)))
                    End If
                Case Is = 4
                    If Containers(4) Is Nothing Then
                        Set Containers(4) = .Range(Cells(i, 1), Cells(i, intLastCol))
                    Else
                        Set Containers(4) = Application.Union(Containers(4), .Range(Cells(i, 1), Cells(i, intLastCol)))
                    End If
                Case Else
                    MsgBox "Wrong Container number at row " & i
                End Select
        Next i

        'now copy data over. Loop through each member of array and respective sheets
        For i = 1 To 4
            'clear container cells
            Sheets("Container " & i).Cells.Clear
            'copy data
            Containers(i).Copy Destination:=Sheets("Container " & i).Range("A2")
            'clear PackList sheets
            Sheets("PACKLIST " & i).Columns("B:C").Clear
            'copy data
            Sheets("Container " & i).Activate
            Sheets("Container " & i).Range(Cells(2, 2), Cells(Containers(i).Rows.Count + 1, 3)).Copy Destination:=Sheets("PACKLIST " & i).Range("B2")
            'copy data to Container Summary
            Sheets("Container " & i).Range("a5:L5").Copy Destination:=Sheets("CONTAINER SUMMARY").Cells(i, 1)
        
        Next i
    End With

End Sub

 

[CarolG]

Thanks. I copied and ran the macro and got the following message:

Wrong Container No. at Row 2
(it listed rows 3,32,33,34,35,36,37,38,39,40,41)
Run-time error '1004'
Cannot change part of a merged cell (I think this would be the Packlist worksheets as they have different cell nos, sizes and columns. It highlights the line in the code:
"PACKLIST " & I) Columns ("B:C").Clear

The only thing it did do was to wipe out all the details in the CONTAINER 1 worksheet and place a "1" in cell A2.

I wasn't sure if the code at the bottom where it mentions:
Sheets( "Container " & i) .Activate etc.... should be Containers, so I went and changed it and the following error came up:
Runtime error '9'
Subscript out of range
This did not change any data in my worksheets.

Does this all make sense. I await your reply and thank you so much for your help so far. It is very much appreciated.
Regards Carol

 

[lozzablake]

In order:

Wrong Container No. at Row 2. This means that there is not a 1,2,3 or 4 in row 2 column A of the Master list

Cannot change part of a merged cell: I clear columns B:C to ensure you do not leave in old data. I assume the column headers are merged so this needs to be changed

It may be easiest if you send me a copy of the spreadsheet so I can test it on real data. e-mail lozzablake@yahoo.com