Microsoft Excel - Insert row based on master sheet date creteria in all worksheets

sample_1.zip 

HI Experts,

I need a code that Look for master sheet column B Values and match column B values of all the sheets then insert the row in all the sheets if master sheet column B criteria does not match . I need it for whole sheet means no limit of sheets.

Suppose Master sheet Column B Date is 9/8/2010 the code look all the column B of all worksheets then match the date creiteria if data is not in the same row then insert lines in all the worksheet according to master sheet dates. The sample workbook is attached with.

Regards,
Hammeed

Hi Hammeed,

Try this code...this is only rough code, short one...

-------------------------------------- Code start here --------------------------------------------
Sub addRow()
shNum = ActiveWorkbook.Sheets.Count
For Each xdate In Sheets("Master_sheet").Range("B2:B" & Range("B" & Rows.Count).End(xlUp).Row)
For i = 3 To shNum
If WorksheetFunction.CountIf(Sheets(i).Range("B:B"), xdate) = 0 Then
Sheets("Master_sheet").Range("B" & xdate.Row & ":AZ" & xdate.Row).Copy
Sheets(i).Range("B" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
End If
Next i
Next
End Sub

HI Pichart Y.

Thanks for your reply i observed that code is coping the master sheet row to all other sheets .  I need that code adjust all the dates ( rows) of  sheets  as  like  master  sheet.

Suppose  in Master sheet the date exists at  column b and row 6   ( 9/12/2010 ) so macro adjust all the worksheets  row 6   ( 9/12/2010 ) this date row ,  similar  if  (10/7/2011) in master sheet placed on column B row 40 so vba code adjust all the worksheet date (10/7/2011) and row to row 40 and so on except  (Data sheet).


Thanks and Regards,

Hammeed

Hi farrukh,

Okey, then try this code.

Here attachment ---> AddDate_InsertLine.zip

pichart Y.

----------------------------------------------- Code start Here ---------------------------------------
Sub addRow()
shNum = ActiveWorkbook.Sheets.Count


For Each xdate In Sheets("Master_sheet").Range("B2:B" & Range("B" & Rows.Count).End(xlUp).Row)
For i = 3 To shNum
If Sheets("Master_sheet").Range(xdate.Address).Value <> Sheets(i).Range("B" & xdate.Row) Then
Sheets(i).Range("B" & xdate.Row).EntireRow.Insert
Sheets(i).Range("B" & xdate.Row) = xdate
End If
Next i
Next


End Sub
------------------------------------------- End of code -------------------------------------

Hi farukh,

You are welcome. I am also happy to learn that this help you in your daily work, and you are happy with my contribution.

Always see you here when you have question or problem about excel...  :)

pichart Y.

end of post