Data transfer between sheets

[LouLBI]

I have two spreadsheets in one workbook.  One is a "Master" work sheet with about 800 names in Column A separated by 24 rows.  For example rows 6, 30, 44.... Up to 16302.  In the areas between the names is data in columns B through L twenty deep, for example B9:L28,  B33:L52 etc up to B16305:L16324.

 The other spreadsheet called "Update" has 120 names in column A.  These names are in groups of eight separated by 24 rows.  For example A6,  A30 up to A174.  This is repeated with a different set of 8 names starting with A214, A422, A630 so that there is a total of 15 sets of 8.

What I would like to do is fill the area in  the "Update" with the data of the corresponding name in the "Master".  For example, the first name in the "Update" is in "A6".  The corresponding name in the "Master" happens to be in "A14190".  I would like to copy the data from the block B14193:L14212 or the data in the block which is shifted one column and three rows from the name and Is 11 wide by 20 deep, and paste it into the corresponding area in "Update".  In other words into B9:L28.  This needs to be done for all 120 names in "Update".

Many thanks in advance.
Lou

 

[SkipVought]

hi,

The data in your workbooks is not structured in a way that Excel's features are designed to manipulate data.

Excel works on TABLES and tables have certain properties like faq68-5184.

The major shortcoming of your data is that you have " names in Column A separated by 24 rows" rather than the NAME IN EACH ROW. THAT is what Excel expects in order to function as designed.

Perhaps you could explain in more detail the nature of your Master data. What are the HEADINGS for each of the A:L columns. I assume that there is no data to the right of column L. Why does the data start in row 6? What is in rows 1-5? Is this structure cast in stone? Can it be changed?

If you modify your data structure properly, the solution could be achieved ON THE SHEET with formulas, using the COUNTIF, MATCH, OFFSET, INDEX functions.

If you don't, then you need to use VBA code, which requires much more skill and effort. You will be expected to produce code that will be required, with help from member contributors. But as a programmer, you ought to be able to handle this with some level of comfort.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[JTBorton]

LouLBI,

Not really sure what you are trying to do, but maybe this will give you some ideas.

Option Explicit
Option Base 1

Public Sub UpdateNames()
    Dim lngEndRow   As Long
    Dim intNameCol  As Integer
    Dim intDataCol  As Integer
    Dim lngStartRow As Long
    Dim F           As Long
    Dim G           As Long
    Dim vntSomeData As Variant
    Dim strName     As String
    
    lngStartRow = 6
    lngEndRow = LastRow(MasterSheet)
    intNameCol = 1 [COLOR=darkgreen]'Or whatever column[/color]
    intDataCol = 2 [COLOR=darkgreen]'or whatever column has your data[/color]
    
    With MasterSheet
        For F = lngStartRow To lngEndRow Step 24
            strName = Trim(.Cells(F, intNameCol))
            For G = 1 To 23
                vntSomeData = .Cells(F + G, intDataCol)
                [COLOR=darkgreen]'whatever you want to do with the data...[/color]
            Next G
        Next F
    End With
    
End Sub

Public Function LastRow(ByRef wksSheet As Worksheet) As Long
    On Error Resume Next
    LastRow = wksSheet.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    On Error GoTo 0
End Function

By the way, I hope the LBI in your name doesn't stand for LyondellBasell. I'm just saying.

-Joshua
If it's not broken, it doesn't have enough parts yet.