update data have already created sheets



I have  this  code   it  creates  sheets   based on  column   b ,      but  the  problem   if   I change  data in  main  sheet   like  add   data  or  delete    it nothing  happens   in  sheets are  already created    , so     I want   update   data  in all sheets  when  changes   in  sheet1 

any  suggestion   to    mod   the  code  or  alternitave  

Sub new_sheetx()

Const cl& = 2
Const ss As String = "sheet1"

Dim a As Variant, x As Worksheet, sh As Worksheet
Dim rws&, cls&, p&, i&, ri&, j&
Dim b As Boolean

rws = Cells.Find("*", , , , xlByRows, xlPrevious).Row
cls = Cells.Find("*", , , , xlByColumns, xlPrevious).Column

Set x = Sheets.Add(After:=Sheets(ss))
Sheets(ss).Cells(3, 1).Resize(rws - 2, cls).Copy x.Cells(1)
Set a = x.Cells(1).Resize(rws, cls)
a.Sort a(1, cl), 2, Header:=xlNo
a = a.Resize(rws + 1)
p = 1

For i = p To rws + 1
    If a(i, cl) <> a(p, cl) Then
        b = False
        For Each sh In Worksheets
            If sh.Name = a(p, cl) Then b = True: Exit For
        If Not b Then
            Sheets.Add.Name = a(p, cl)
            With Sheets(a(p, cl))
                x.Cells(1).Resize(, cls).Copy .Cells(3, 1)
                ri = i - p
                x.Cells(p, 1).Resize(ri, cls).Cut .Cells(3, 1)
                Sheets(ss).Cells(1).Resize(2, cls).Copy .Cells(1)
            End With
        End If
        p = i
    End If
Next i

Application.DisplayAlerts = False
Application.DisplayAlerts = True

End Sub

thanks  in advance 



Selected Answer

Hi again Leopard

Your code adds new sheets successfully (in a one off action). 

I haven't changed that but if you want to change the individual sheets when someting in sheet1 is changed, you can do that with the Worksheet_Change event.

In the file attached, I've added a new macro (see code below) which does this:

  1. Sees if a change was made in columns A:D in sheet1 and if so:
    1. Checks if there is already a sheet with the same name as that in  column B (and if so it copies the changed row to that sheet, row 3)
      • Note that your sheet1 has two rowd with "cvb-12-34" so there are two rows on that sheet
    2. If a new row is added, it does nothing until a name is entered in column B when it does the task above and if it's a new name, it created a new sheet of that name, adding it as a red tab next to sheet1.

You'll get a message if the macro does something.

This will need some work e.g. in sheet1 you might add data validation in column B so that cells are unique- see the tutorial Require a Unique List of Numbers in a Range in Excel). You'll also need to think what happens if rows are deleted.

Hope this helps

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("A:D")) Is Nothing Then
    Dim x As Worksheet

    For Each x In Sheets
        If x.Name = Cells(Target.Row, 2) Then
            'Sheet exists
            Range("A" & Target.Row & ":D" & Target.Row).Copy x.Range("A3:D3")
            MsgBox "Copied changed data to row 3 in sheet " & x.Name
            '<do other things>>
            Exit Sub
        End If
    Next x

    'sheet does not exist
    If Cells(Target.Row, 2) <> "" Then
      Set x = Sheets.Add(After:=sheet1)
      x.Name = Cells(Target.Row, 2) ' name sheet and make it red
      x.Tab.Color = 255
      'copy header and new row data
      Range("A2:D2").Copy x.Range("A2:D2")
      Range("A" & Target.Row & ":D" & Target.Row).Copy x.Range("A3:D3")
      MsgBox "Please move/recolor newly-created sheet " & x.Name
    End If

End If

End Sub

REVISION 1: Further to the discussion below, I've now added this code to your macro (after the DIM statements): 

Application.DisplayAlerts = False

    For Each sh In Sheets
     If Not sh.Name = ss Then
     End If
    Next sh 

Application.DisplayAlerts = True
That deletes ALL sheets (apart from your main sheet1) before your macro re-creates them with the data in sheet1.

As the file leaves me, there is only sheet1. Change a row in that (or add one with a name in column B) and a new sheet will appear- that's my Worksheet_Change macro working.

If you then run your macro (in my file), all sheets will be created.

If you then change a cell (say a quantity in D) in sheet1, my macro will work again (you should get a message)-  go to the matching sheet and the quantity in row 3 should match that on sheet1.

If you only want to do the "bulk replace" (and not have other sheets added or changed as you alter sheet1) then just delele the Worksheet_Change macro.



excellent  !  I  like  your  code   it works  very  well  but,  I was  wanting to amende  my  code  , you  know  why ?  if  I have  data   in sheet1    so  many     it's  not  practical  way   to  use  that  in worksheet change  event    because  I  have   to  press in column   b every  brand   it  create    the  sheets each of  them  alone  and  then it  takes  more  time  from  me, it 's    not  doing  that  togheter  in  one  time        about      your  code I  agree     when  change    in  sheet1  automatically  change  in  multiple sheets 
I  no  know   if  there  is    way   do  that   create  all of  sheets   in  one  time in worksheet change   event   also   if  I  delete  some  row  for  some  sheets   it's  nothing  happened in   them  
despite   for  all this     I  consider  you  answered  my  post  
thanks   so  much    for   assistance    whether  in this  time   or  earlier or  future 
best  regard,
leopard (rep: 40) Jan 12, '21 at 3:45 am
Thanks for selecting my Answer. Sorry but I'm not clear what you want to do now... 
I thought you wanted a two stage process:
    1. your macro creates the sheets in bulk, all at one time (though I don't know what you will use each shhet for)
    2. if later something changes on sheet1 (say a value changes or a new item is added), you want the other sheets to be changed too- my macro does that (apart from the delete bit)

If not, you could modify your macro so it first of all deletes ALL sheets (apart from your main sheet1) then re-creates them with the data in sheet1 using your existing code. To do that, add the code in bold below after your DIM statements (it assumes sh is already defined as a Worksheet):
Application.DisplayAlerts = False
    For Each sh In Sheets
     If Not sh.Name = ss Then
     End If
    Next sh
Application.DisplayAlerts = True


Have fun!
John_Ru (rep: 502) Jan 12, '21 at 4:53 am
actually  i  think   there  is  somthing  wrong    your macro doesn't  create the sheets in bulk  and  a value changes or a new item don't add    
 the  macro   works   in  first  time  then  when  i add a new data or  change  data have  already  exised    nothing  change   please  test   the  code   again  and  inform  me  what  happens  maybe  the  problem is  to  me  and  about  what  you  wright  in last  comment point1 & 2  you're  right  this  is  exactly  what  i  want  but  it  doesn't  work   ,  sorry  buddy  for  this  problem
leopard (rep: 40) Jan 12, '21 at 5:50 am
Leopard   My macro was never intended to create bulk emails- yours already did that. My macro was only to capture changes or additions to the data on (populated) sheet1 and exisiting sheets.   My file works here but I've modified it only to add the code to delete sheets in your macro ( I suggested above)   That's covered in Revision 1 to my Answer, along with an attached file- please follow the instructions in Revision 1 to prove all is working correctly.
John_Ru (rep: 502) Jan 12, '21 at 6:11 am
yes  now  it  works  , every  thing  is  ok     thanks  for  your  time  and  patience 
leopard (rep: 40) Jan 12, '21 at 6:49 am
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login