We help IT Professionals succeed at work.

Copy data from master list to corresponding fields on various worksheets. Excel Macro

jasocke2
jasocke2 asked
on
Hi,

Please see attached workbook for example & for more info…

What I have is two workbooks, in one workbook there is a master list of data (In attached spreadsheet this is called “Sheet From another workbook”) and in the other workbook are the ‘blank templates’ there are numerous worksheets but the only ones I am interested in are the ones in between the sheets called ‘Start’ and ‘End’

In between ‘Start’ and ‘End’ sheets are uniquely named sheets (in my example, 1000, 1001, 1002) each sheet is identical apart from the name of the Sheet. Each sheet is numbered in the first column 1-68, this is the unique number on each sheet, and the second column is called ‘Total’ these values can be found in the ‘master list’

In short these templates (1000, 1001, 1002) have to be populated with the data from the other workbook (the data from “Sheet From another workbook”)

In the master list of data is the sheet name in the first column, the ‘unique number’ in the forth column and the ‘total’ in the 5th column.

I need a macro which will (from the ‘master list’ workbook) open the blank templates workbook and for each worksheet in-between the start and end sheet lookup the corresponding data in the master list, so for example:

Master List:

1000 | xx | xx | 1 | 678
(the value 678 would go in the total field next to the unique number 1 on sheet 1000)
1000 | xx | xx | 2 | 900
(the value 900 would go in the total field next to the unique number 2 on sheet 1000)
1000 | xx | xx | 5 | 264
(the value 264 would go in the total field next to the unique number 5 on sheet 1000)
1001 | xx | xx | 1 | 264
(the value 852 would go in the total field next to the unique number 1 on sheet 1001)

How the blank templates sheet would look like.
Unique Number      Total
1                            678
2                            900
3      
4      
5                             264

I know this is a big ask but any help would be most appreciated!

Thanks,

Example1.xls
Comment
Watch Question

try something like this....
Sub Process()

Dim wsMaster As Worksheet
Dim rngData As Range
Dim r As Integer

Dim wbTarget As Workbook
Dim TargetRow As Long
Dim TargetWS As String

Set wsMaster = Worksheets("MasterSheet") ' if this is in a different workbook, you'll need to change this
Set rngData = wsMaster.Range("A1") ' if the data starts in a different cell, change this.

Set wbTarget = ActiveWorkbook  ' if the target workbook is different, you'll need to change this.

r = 0
Do While rngData.Offset(r, 0) <> ""
    TargetWS = Trim(Str(rngData.Offset(r, 0)))
    TargetRow = rngData.Offset(r, 3)
    wbTarget.Worksheets(TargetWS).Cells(TargetRow, 2) = rngData.Offset(r, 4)
    r = r + 1
Loop

End Sub

Open in new window

Top Expert 2010

Commented:
PatOBrien said:
>>Dim r As Integer

Bit of friendly advice: never, ever, EVER use Integer to refer to a row number.  The moment you get to Row
32768, you will get an overflow error.

Memory is no longer dear, so there is nothing to gain from using Integer instead of Long.  Indeed, in some
instances code runs *slower* with Integer because VBA is having to constantly do implicit roundtrip
conversions from Integer to Long and back to Integer.

Author

Commented:
Thanks for the response...

It does work to a degree but it copies the values into B1 where it needs to copy it into B13.

Also ideally it needs to do a check on the 'Uniquie number' as sometimes the numbers are not in sequence and I think the code is relying on the row numbers.

Thanks again
ok, I added a search for the "Unique Value" in column A of the template WS's.

Let me know if this doesn't work for you.

Pat

p.s. and I took Mathewspatrick's advice to heart.  Thanks.
Sub Process()

Dim wsMaster As Worksheet
Dim rngData As Range
Dim r As Long

Dim wbTarget As Workbook
Dim TargetValue As Long
Dim TargetRow As Long
Dim TargetWS As String
Dim Target As Range

Set wsMaster = Worksheets("MasterSheet") ' if this is in a different workbook, you'll need to change this
Set rngData = wsMaster.Range("A1") ' if the data starts in a different cell, change this.

Set wbTarget = ActiveWorkbook  ' if the target workbook is different, you'll need to change this.

r = 0
Do While rngData.Offset(r, 0) <> ""
    TargetWS = Trim(Str(rngData.Offset(r, 0)))
    TargetValue = rngData.Offset(r, 3)
    With wbTarget.Worksheets(TargetWS)
        With .Range("A:A")
            Set Target = .Find(TargetValue, LookIn:=xlValues)
            If Not Target Is Nothing Then
                TargetRow = Target.Row
            Else
                MsgBox "Unique value " & TargetValue & " not found in worksheet " & .Parent.Name
            End If
        End With
        .Cells(TargetRow, 2) = rngData.Offset(r, 4)
    End With
    r = r + 1
Loop

End Sub

Open in new window

Author

Commented:
Hi,

That's great - Just what I needed to get going.

Thanks