INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!

Talk With Other Members
Be Notified Of Responses
To Your Posts
Keyword Search
One-Click Access To Your
Favorite Forums
Automated Signatures
On Your Posts
Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

LINK TO THIS FORUM!

Tek-Tips LinkedIn^® Group

Join our
LinkedIn^® Group!

Hook up with past and present colleagues and classmates quickly.

Partner With Us!

"Best Of Breed" Forums Add Stickiness To Your Site

(Download This Button Today!)

Feedback

"...One of the best run forums I have used in years! ...I like the way the site is organized and your no tolerance of flames..."

More...

Geography

Where in the world do Tek-Tips members come from?

Click Here To Find Out!

Tek-Tips Shirts!

Get your
Tek-Tips Forums
SWAG here!

Home > Forums > Programmers > Languages > VBA Visual Basic for Applications (Microsoft) Forum

Data transfer between sheets

Read More Threads Like This One

LouLBI (Programmer)

3 Jul 12 10:20

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 (Programmer)

3 Jul 12 10:42

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: What are LIST & TABLE PRINCIPLES for Spreadsheet Users.

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 (TechnicalUser)

4 Jul 12 0:33

LouLBI,

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

CODE

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 'Or whatever column
    intDataCol = 2 'or whatever column has your data
    
    With MasterSheet
        For F = lngStartRow To lngEndRow Step 24
            strName = Trim(.Cells(F, intNameCol))
            For G = 1 To 23
                vntSomeData = .Cells(F + G, intDataCol)
                'whatever you want to do with the data...
            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.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Back To Forum

Back To VBA Visual Basic for Applications (Microsoft)