Copy data from master list to corresponding fields on various worksheets. Excel Macro
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
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
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
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.
>>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.
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
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.
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
Premium Content
You need an Expert Office subscription to comment.Start Free Trial