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

Asked By farrukh on 06-Jan-12 01:40 PM
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

Pichart Y. replied to farrukh on 07-Jan-12 02:49 AM
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
---------------------------------------- end of code ----------------------------------------

Here attachment for you----> AddDate.zip

Pichart Y.
farrukh replied to Pichart Y. on 07-Jan-12 07:25 AM
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

Pichart Y. replied to farrukh on 08-Jan-12 01:31 AM
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 -------------------------------------
farrukh replied to Pichart Y. on 08-Jan-12 02:35 AM
Dear Pichart Y  

Stunning no words to say thanks, you have done a great deal for me. Saved lot of time :-)

Thanks and Regards,
Farrukh
Pichart Y. replied to farrukh on 08-Jan-12 08:17 AM
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.
farrukh replied to Pichart Y. on 09-Jan-12 08:25 AM
end of post