VBA - Look for value in range across multiple sheets, return specific value from relevant sheet containing matching value

studentXcel

New Member
Joined
Dec 11, 2016
Messages
2
Greetings,

I am new to Excel VBA and I've given effort and tried to do this for awhile, however, I am still unable to solve the issues that I have. Fortunately, I came across this site and discovered some leads. Hopefully, someone would be kind enough to point me to a right direction and I would be very grateful.

I've found a post that was somewhat similar to my direction and I tried to follow the code instruction offered by
Fishboy:))thank you) by adding/modifying a few lines but it wasn't working for me as I received errors which I am not sure how to fix at the moment. I have about 50 sheets with existing data and 1 master sheet that contains columns of updated data which could belong to any one of its 50 sheets.

I would like to do the following:
If value from B2:Z2(SheetM){<-master sheet} matches value from B2:H2(Sheets 1/2/3.../50), then the macro would copy & insert all value below its matching relevant cells from (SheetM) to (Sheet 1/2/3.../50).


For Example:
Master Sheet (SheetM)
https://postimg.org/image/6470fqrf1/


Matches value in B2:H2{Sample1-Sample7} in Sheet22
https://postimg.org/image/o5q5djlfx/


Macro copies and inserts all value below of B2:H2(SheetM) to Sheet22 (text in purple). Existing data in B3:H3(Sheet22) would shift down.
https://postimg.org/image/49u5y04el/

I've tried to run it with the following code with no success. I appreciate any help you can provide, thank you.

<code style="margin: 0px; padding: 0px; line-height: 13px; color: rgb(87, 65, 35);">
Sub InsertUpdatedMeasurement()
Dim sRange As Range, Rng As Range, WS As Worksheet, FindString As String


FindString = Sheets("SheetM").Range("B2:Z2").Value
For Each WS In ActiveWorkbook.Worksheets


LastRow = Sheets("SheetM").Range(Rows.Count, "B2:Z2").End(xlUp).Row.Offset(,1)


If WS.Name <> "SheetM" Then


WS.Activate


LastRow2 = ActiveSheet.Range(Range.Count "B2:Z2").End(xlUp).Row.Offset(,1)


Set sRange = ActiveSheet.Range("B2:Z2" & LastRow2)


With sRange
Set Rng = .Find(What:=FindString, _
After:=.Cells(1), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False)



If Not Rng Is Nothing Then


Rng.Copy
ActiveSheet.Rows(Rng).Selection.Insert.Shift:=xlDown


Application.CutCopyMode = False


LastRow1 = LastRow1 + 1


End If
End If
End With
Next Ws


Sheets("SheetM").Activate


Application.ScreenUpdating = True


End Sub
</code>
<code style="margin: 0px; padding: 0px; line-height: 13px; color: rgb(87, 65, 35);">
</code>
 

Some videos you may like

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

hiker95

Well-known Member
Joined
Apr 8, 2009
Messages
17,649
studentXcel,

Welcome to the MrExcel forum.


You are posting jpg images. Anyone trying to help you would have to enter the data manually.


We would like more information. Please see the Forum Use Guidelines in the following link:

http://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html


See reply #2 at the next link, if you want to show small screenshots, of the raw data, and, what the results should look like.

http://www.mrexcel.com/forum/about-board/508133-attachments.html#post2507729


Or, you can post your workbook/worksheets to the following free site (sensitive data changed), mark the workbook for sharing, and, provide us with a link to your workbook:

https://dropbox.com


When posting VBA code, please use Code Tags - like this:

[code=rich]

Paste your code here.

[/code]
 

studentXcel

New Member
Joined
Dec 11, 2016
Messages
2
(Thanks for the heads up hiker95:))

Greetings,


I am new to Excel <acronym title="visual basic for applications" style="border-width: 0px 0px 1px; border-top-style: initial; border-right-style: initial; border-bottom-style: dotted; border-left-style: initial; border-top-color: initial; border-right-color: initial; border-bottom-color: rgb(0, 0, 0); border-left-color: initial; border-image: initial; cursor: help;">VBA</acronym> and I've given effort and tried to do this for awhile, however, I am still unable to solve the issues that I have. Fortunately, I came across this site and discovered some leads. Hopefully, someone would be kind enough to point me to a right direction and I would be very grateful.

I have about 50 sheets with existing data and 1 master sheet that contains columns of updated data which could belong to any one of its 50 sheets. I would like to do the following: If a value from B2:Z2(SheetM){<-master sheet} matches value from B2:H2(Sheets 1/2/3.../50), then copy & insert all value below its matching relevant cells from (SheetM) to (Sheet 1/2/3.../50).

I've found a post that was somewhat similar to my direction and I tried to follow the code instruction offered by
Fishboy(:)thank you)by adding/modifying a few lines but it wasn't working for me as I received errors which I am not sure how to fix at the moment. I appreciate any help you can provide, thank you.

Rich (BB code):
Sub InsertUpdatedMeasurement()
Dim sRange As Range, Rng As Range, WS As Worksheet, FindString As String


FindString = Sheets("SheetM").Range("B2:Z2").Value
For Each WS In ActiveWorkbook.Worksheets


LastRow = Sheets("SheetM").Range(Rows.Count, "B2:Z2").End(xlUp).Row.Offset(,1)


If WS.Name <> "SheetM" Then


WS.Activate


LastRow2 = ActiveSheet.Range(Range.Count "B2:Z2").End(xlUp).Row.Offset(,1)


Set sRange = ActiveSheet.Range("B2:Z2" & LastRow2)


With sRange
Set Rng = .Find(What:=FindString, _ 
After:=.Cells(1), _
LookIn:=xlValues, _ 
LookAt:=xlWhole, _ 
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _ 
MatchCase:=False) 



If Not Rng Is Nothing Then


Rng.Copy
ActiveSheet.Rows(Rng).Selection.Insert.Shift:=xlDown


Application.CutCopyMode = False


LastRow1 = LastRow1 + 1


End If
End If
End With
Next Ws


Sheets("SheetM").Activate


Application.ScreenUpdating = True


End Sub


For Example:
Master Sheet (SheetM) B2:M2

ABCDEFGHIJKLM
1Measurement Update
2LabelSample1Sample2Sample3Sample4Sample5Sample6Sample7Sample8Sample9Sample10Sample11Sample12
37.2442.553.38.0912.622.432.0423.642.23.921.5543.2
47.2142.253.268.0412.82.42223.75423.8721.4542.5
57.0242.053.27.7712.722.391.9523.642.053.8821.443.65
66.9242.13.227.7112.442.391.9123.7541.93.8821.3543.9
76.8941.63.257.8112.382.41.8924.0543.53.9221.5544
86.8840.93.227.7212.92.381.8524.243.83.9421.643.45
96.94423.347.8312.662.361.8723.143.653.7821.543.4

<tbody>
</tbody>
SheetM




Matches same value in B2:H2{Sample1-Sample7} in Sheet22

ABCDEFGH
1Grams of water
2LabelSample1Sample2Sample3Sample4Sample5Sample6Sample7
37.5447.953.7710.3212.562.542.11
47.4146.653.799.9912.162.552.07
57.3646.43.89.9312.182.542.09
67.3545.553.7710.1812.12.522.07
77.3645.33.8510.2412.162.52.03
87.4344.553.8210.4212.242.522.08
97.2143.13.8210.1611.922.512.05

<tbody>
</tbody>
Sheet22



Copies and inserts all values below of B2:H2(SheetM) to Sheet22 (text in red). Existing data in B3:H3(Sheet22) would shift down.

ABCDEFGH
1Grams of water
2LabelSample1Sample2Sample3Sample4Sample5Sample6Sample7
37.2442.553.38.0912.622.432.04
47.2142.253.268.0412.82.422
57.0242.053.27.7712.722.391.95
66.9242.13.227.7112.442.391.91
76.8941.63.257.8112.382.41.89
86.8840.93.227.7212.92.381.85
96.94423.347.8312.662.361.87
107.5447.953.7710.3212.562.542.11
117.4146.653.799.9912.162.552.07
127.3646.43.89.9312.182.542.09
137.3545.553.7710.1812.12.522.07
147.3645.33.8510.2412.162.52.03
157.4344.553.8210.4212.242.522.08
167.2143.13.8210.1611.922.512.05
17

<tbody>
</tbody>
Sheet22
 

Watch MrExcel Video

Forum statistics

Threads
1,123,125
Messages
5,599,850
Members
414,342
Latest member
K Darrell Smith

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top