Copy value from Master based to all sheets on tab name

[Mel2016]

I have a workbook (250+named sheets) with an this button on an master sheet. I am attempting to pull the value from B2 if the tab name matches and paste the description into each sheet in C5.

The following code works but is only copying the first field value to the individual sheets instead of looping through finding the name of the sheet and grabbing the value that matches from the master sheet.


Any help is greatly appreciated!!



Example of master

JobName JobDescription
ProgramA Run first
ProgramB Run second

Works but copying only the first row instead of looping through each
 
Private Sub CommandButton1_Click()
Custom$ = Left(Range("A2"), 5)
Range("B2").Copy  'currently copying the first value instead of looping through each
 
 
Dim WS_Count As Integer
    Dim I As Integer
   
    'Set WS_COunt equal to the number of worksheets in teh active workbook
    WS_Count = ActiveWorkbook.Worksheets.Count
   
    'Begin loop
    For I = 1 To WS_Count
        If Left$(ActiveWorkbook.Worksheets(I).Name, 5) = Custom$ Then
        Worksheets(I).Activate
    End If
   
' Find the first empty row in the worksheet
'erow = Activate.Cells(Rows.Count, 1).End(x1Up).Offset(1#).Row
ActiveSheet.Paste Destination:=Worksheets(I).Range("C5")
Next I
 
ActiveWorkbook.Close
 
   
    
 
End Sub

 

[James006]

Hi,

Quite difficult to guess the exact range of your source data ...

However ... you will probably find it easy to adapt following code to your situation:

Private Sub CommandButton1_Click()
Custom$ = Left(Range("A2"), 5)
Dim WS_Count As Integer
Dim I As Integer
   
    'Set WS_Count equal to the number of worksheets in active workbook
    WS_Count = ActiveWorkbook.Worksheets.Count
   
    'Begin loop ... with assumption Source range is B2:B252
    For I = 1 To WS_Count
        If Left$(ActiveWorkbook.Worksheets(I).Name, 5) = Custom$ Then
          Range("B" & I + 1).Copy Destination:=Worksheets(I).Range("C5")
        End If
    Next I
'ActiveWorkbook.Close
End Sub

HTH

 

[Mel2016]

Thanks but unfortunately its not working. I believe the error is that the code isn't matching the he tab name in order to populate the sheet with the value. any other thoughts? I am still learning vb.

 

[James006]

Could you kindly explicit a couple of things ...

1. Do you have a list of all you tab names ... if yes, where are they located ?

2. Do you have a list of data to be copied (what you call B2) ... if yes, where are they located ?

 

[Mel2016]

Yes, all tab names are in a sheet named "Employee Sheet" Program ID in column A and Program Description in Column B. There are 250 rows
Data to be copied is in Column B in the sheet "Employee Sheet".

Example
Column A Column B
WD002-Emp Lookup Online screen to lookup Employee info

Each sheet is named from column A so they should all match. And populate the value from B into the named sheet.

 

[James006]

Thanks for your explanations ... :wink:

Below is the code to be tested ...

Private Sub CommandButton1_Click()
Dim Sh As Worksheet
Dim x As Variant
    For Each Sh In ActiveWorkbook.Worksheets
        If Sh.Name <> ActiveSheet.Name Then
            x = Application.Match(Sh.Name, ActiveSheet.Range("A1:A300"), 0)
            If IsError(x) = False Then
              ActiveSheet.Range("B" & x).Copy Destination:=Sh.Range("C5")
            Else
              ' do nothing
            End If
        End If
    Next Sh
End Sub

Hope this will help

 

[Mel2016]

This works perfectly!!! Thank you so much.

 

[James006]

Glad you could fix your problem ...:wink:

 

[Mel2016]

Yes, this will be very helpful for many things. One question - if I wanted to exclude another tab name, how would I add that. If Sh.Name <> ActiveSheet.Name AND <> "TabName" ?

 

[James006]

Yes ... Just modify the condition with the actual name in-between quotes ... i.e.

If Sh.Name <> ActiveSheet.Name And Sh.Name <> "TheNameToBeExcluded" Then

Hope this will help