copy values for each row separately to a new sheet

0

hi

I  face  dilemma   and  this  case   is  complicated  .  I  searched   in   the  internet  to  get  the  clue  to  start  adding  some  lines  code  to  achieve  that   but    I  do not any  basic  to  do  that   . so   after add a new  sheet  and  copy  specific cells   also   I  need  copy  specific  cells  differntly way   . when  I  add a new  sheet  then  should  copy some  values for  each  row  to  a new sheet .

 I  put   the  result  in sheet 1,2  after add  a new  sheet .

 when I  add anew sheet "1 "   it  should  show  the  values  in C2,N2,P2 how  get  theses  values ?   . it's from sheet main   . it  begins   from row 5   then  A5+B5  in sheet main  =  C2    in  a new  sheet   "1"   and  C5 in  sheet MAIN = N2 in sheet "1" and   E5  in  sheet MAIN = C2  in sheet "1"  and   when  I  add  a new sheet "2"  it  begins  from row6  . so  it  should  bring the  values  from next  row   when  add  a new  sheet.  

sorry  about  much explaining   but  I  have  to  do  that   to  understand  my  requirments .

Private Sub Workbook_NewSheet(ByVal Sh As Object)


With Sh

    .Move after:=Sheets(Sheets.Count)
    .Name = Numeric & Sheets.Count - 1
    .Range("A1:P12").Borders.Weight = xlMedium
    .Range("A1:P12").HorizontalAlignment = xlCenter
    With .Cells(1).Resize(1, 16)
        .Value = Array("ITEM NUMBER", "ITEM DESC", "QUANTITY", "UNIT PRICE", "TOTAL", "WHSE", "ACOUNT CODE", "BUSINESS UNIT", "DEPARTMENT", "WORK CENTER", "FLOCK", "tt", "weight ", " 0.9", "ll", " 1.34")
        .Interior.ColorIndex = 53
        .Font.Bold = True
        .Font.Color = vbWhite
        '.EntireColumn.AutoFit

Dim sh1 As Worksheet, Nwr As Long
Set sh1 = Sheets("main")

        Nwr = .Range("a" & Rows.Count).End(xlUp).Row + 1

        Sh.Range("a" & Nwr) = sh1.Range("e3").Value
        Sh.Range("d" & Nwr) = sh1.Range("e2").Value
        Sh.Range("f" & Nwr) = "DAT010"
        Sh.Range("g" & Nwr) = "1141000022"
        Sh.Range("h" & Nwr) = "JP-PROD."
        Sh.Range("i" & Nwr) = "JP-WIPDP"
        Sh.Range("j" & Nwr) = "JP-WIPWC"
        Sh.Range("k" & Nwr) = "Flock_4"
        .Range("A1:o" & Nwr).EntireColumn.AutoFit
     End With
End With
End Sub
Answer
Discuss

Discussion

Leap

I'm not clear on your requirement. Do you want to add several sheets when you run the macro (so 5 sheets if there are 5 rows in column A of "main" sheet)?

What about the other item numbers in cells K3 and Q3of "main"? Do they need new sheets (if rows are populated)?

Would it be helpful to have the macro put all rows on a single new sheet?

Please clarify your ORIGINAL question and I'll try to look at it later in my day.
John_Ru (rep: 1917) Jul 14, '21 at 3:21 am
John
about K3 and Q3of "main"   I  don't  need  them  so  it  should  ignore  them  and  every  time   should  add  a new  sheet if rows are populated  so  when  create  a new  sheet    from  the  first  time  then  should  start copying  from row5  based what  filled  from A5: E5  and when  create  second  sheet   should   copy  from A6: E6   and when  create  third sheet   should   copy  from A7: E7 if  they are  filled  and with   ignore A6:E6  beacuse  it's copied in second sheet  and  so on  when  add  a new shee     .it should  not repeat copying  values  if  they have  ever  copied in  any  sheet . so any  a new  values  in  range  A:E   should fill  into a new sheet
leap (rep: 22) Jul 14, '21 at 5:01 am
Add to Discussion

Answers

0
Selected Answer

Leap

I suggest instead that you use the Worksheet_Change event. Remember that event is triggered when enter is pressed in a cell (even if it doesn't change the value). In the code below (and the attached file) I do that but the first 9 lines check if a row contains 5 numeric values and if the user is ready to create a new sheet. I've done that since if you later correct a sheet, you should have a sheet that reflects that.

I changed the sheet name line too since you included Numeric (which was a vraiable with no value).

In the bold lines further down, I transfer values from the row (or Dta range I created) to the new sheet, adding two for P2 in new sheet:

Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("A5:E12")) Is Nothing Then Exit Sub
Dim Dta As Range, Resp As VbMsgBoxResult

Set Dta = Cells(Target.Row, 1).Resize(1, 5)

For Each Cll In Dta.Cells 'look at cells in A:E
    If IsNumeric(Cll) = False Or Len(Cll) < 1 Then Exit Sub 'leave if any cells are empty or alphabetic
Next Cll

Resp = MsgBox("Enough data- want to create new or replacement sheet now?", vbYesNo, "Number values in columns A:E") 'offer user new sheet
If Resp = vbNo Then Exit Sub

Set Sh = Sheets.Add

With ActiveSheet
    .Move after:=Sheets(Sheets.Count)
    .Name = Sheets.Count - 1
    .Range("A1:P12").Borders.Weight = xlMedium
    .Range("A1:P12").HorizontalAlignment = xlCenter

    With .Cells(1).Resize(1, 16)
        .Value = Array("ITEM NUMBER", "ITEM DESC", "QUANTITY", "UNIT PRICE", "TOTAL", "WHSE", "ACOUNT CODE", "BUSINESS UNIT", "DEPARTMENT", "WORK CENTER", "FLOCK", "tt", "weight ", " 0.9", "ll", " 1.34")
        .Interior.ColorIndex = 53
        .Font.Bold = True
        .Font.Color = vbWhite
    End With

Dim sh1 As Worksheet
Set sh1 = Sheets("main")

    .Range("A2") = sh1.Range("E3").Value
    .Range("C2") = Dta(5) ' get E value from Dta range
    .Range("D2") = sh1.Range("E" & Target.Row).Value
    .Range("F2:K2").Value = Array("DAT010", "1141000022", "JP-PROD.", "JP-WIPDP", "JP-WIPWC", "Flock_4")
    .Range("N2") = Dta(3) ' from column C
    .Range("P2") = Dta(1) + Dta(2) ' add A & B values

    .Columns("A:P").EntireColumn.AutoFit

End With

End Sub
To try this, enter a cell in completed row 5 say and press Enter. On a new row, you'll get  the requestor only after all columns are complete.

In the file, I've also made some revisions (to your sheet "main" of your workbook)  as follows...

In column F, to remove #DIV0! messages, replace =E5/(A5+B5+C5) with (e.g. for F5):

=IFERROR(E5/(A5+B5+C5),"")
. Also in column E, I replaced =D5-(C5*$C$4+B5*$B$4+A5*$A$4) with (e.g. for E5):
=D5-SUMPRODUCT($A$4:$C$4,A5:C5)
Hope this works for you.
Discuss

Discussion

thanks
but  there   is  somthing  missed  the  D2   should  be =  E2    not  copy  values  from COL F
leap (rep: 22) Jul 14, '21 at 2:10 pm
Oops! My mistake. Revised answer and file now have this line:
.Range("D2") = sh1.Range("E" & Target.Row).Value
To be honest I thought you might see and be able to correct that yourself.
John_Ru (rep: 1917) Jul 14, '21 at 2:17 pm
John
thanks  . you're  right   . suppossing  I   try  to  fix  this  simple  mistake . I  was  my  all of focus      how  the  file  works  before  see  other anything .
leap (rep: 22) Jul 14, '21 at 3:04 pm
Np problem Leap. Glad it works and thanks for selecting my Answer.
John_Ru (rep: 1917) Jul 14, '21 at 4:53 pm
Add to Discussion


Answer the Question

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