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>
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>