create data for each invoice after issue invoice

0

hello 

I try  making  macro   to  copy  data from sheet   "SALES"  to  sheet "DATA" but  my  code  doesn't  work  as  what  I  want    I  put  the  result  in  sheet  data  what code   should  do   

so  I  truly  appreciate  if  any  body  fixing  my  mistake  

Sub J3v16()
Dim ws As Worksheet, nr As Long, lr As Long
Application.ScreenUpdating = False
Set ws = Sheets("sales")

    With ws
        lr = .Cells(.Rows.Count, 1).End(xlUp).Row
        If lr > 22 Then
            .Range("b23:g" & lr).Copy
            With Sheets("Data ")
                nr = .Cells(.Rows.Count, 2).End(xlUp).Row + 1
                .Range("D" & nr).PasteSpecial xlPasteValues
                .Range("A" & nr).Resize(lr - 22) = ws.Range("A23:A" & lr).Value
                .Range("B" & nr).Resize(lr - 22, 2) = Array(ws.Range("F7"), ws.Range("F9"))
                .Range("J" & nr).Value = Application.Sum(.Range("I" & nr).Resize(lr - 22))
                .UsedRange.Borders.Weight = 2
                .Columns(7).Resize(, 4).NumberFormat = "0.00"
            End With
        End If
    End With
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub
Answer
Discuss

Answers

0
Selected Answer

Leap

The problem with your file is that in "sales", column A has TOTAL, DISCOUNT, PAID and NET in A35:A38 so your last row calculation lr became 38- (beyond your invoice item data).

In the attached sheet, those cells are moved to column E (merged with F). Now your calculation of lr can work. The code is further modified as below:

Sub J3v16()
Dim ws As Worksheet, nr As Long, lr As Long
Application.ScreenUpdating = False
Set ws = Sheets("sales")

    With ws
        lr = .Cells(.Rows.Count, 1).End(xlUp).Row
        If lr > 22 Then
            .Range("b23:g" & lr).Copy
            With Sheets("DATA")
                nr = .Cells(.Rows.Count, 2).End(xlUp).Row + 1
                .Range("D" & nr).PasteSpecial xlPasteValues
                .Range("A" & nr).Resize(lr - 22) = ws.Range("A23:A" & lr).Value
                .Range("B" & nr).Resize(lr - 22) = ws.Range("F7")
                .Range("C" & nr).Resize(lr - 22) = ws.Range("F9")
                .Range("J" & nr).Resize(lr - 22) = ws.Range("G35")
                '.UsedRange.Borders.Weight = 2
                '.Columns(7).Resize(, 4).NumberFormat = "0.00"
            End With
        End If
    End With

Application.CutCopyMode = False

Application.ScreenUpdating = True

MsgBox "Invoice values copied to DATA sheet"

End Sub
Note that I've commented out the formatting you applied- it's better to format DATA ahead of its population. Also this macro does little so there's no real advantage in turning Application.ScreenUpdating off and on.

I leave you to clear (parts of) your invoice after the transfer.

Have a good weekend!

Discuss

Discussion

p.s. suggest you look as using defined Names and Named ranges in Excel- it will help to simplify your code in cases like this.
John_Ru (rep: 1002) Mar 20, '21 at 11:15 am
John
 thanks  for  fixing  the  code  and  provide  me  the  assistance 
Have a good weekend    too ! 
leap (rep: 12) Mar 20, '21 at 2:23 pm
Add to Discussion


Answer the Question

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