CODEWITHSUNDEEP
vbaexcel
jwoff
jwoff

Reputation: 165

Excel not transferring data properly from one sheet to another

Recently I've been working on a spreadsheet that allows users to input data on an invoice template page, and then save that information on another sheet. The invoice template looks like this: Invoice Box

I just put in random inputs for each cell. Then the data here from B17:H37 needs to be moved to a sheet named Invoice data when the "Save" forum button is pressed by the user. The data is supposed to be inputted into D:G on the Invoice data page, but the result is as such: Invoice Data

A:C are filled in with different values outside B17:H37 (as shown in code below), but only the "Name" data (column B) from the Invoice box is copied into the Invoice data sheet while "Hours", "cost", and "Total" are neglected. Here's the code I have so far (from a tutorial I found online):

Sub save_invoice()
  Dim rng As Range
  Dim i As Long
  Dim a As Long
  Dim rng_dest As Range
  Application.ScreenUpdating = False
  'Check if invoice # is found on sheet "Invoice data"
  i = 1
  Do Until Sheets("Invoice data").Range("A" & i).Value = ""
    If Sheets("Invoice data").Range("A" & i).Value = Sheets("Invoice").Range("E7") & "-" & Range("F7").Value Then
      'Ask overwrite invoice #?
      If MsgBox("Overwrite invoice data?", vbYesNo) = vbNo Then
        Exit Sub
      Else
        Exit Do
      End If
    End If
    i = i + 1
  Loop
  i = 1
  Set rng_dest = Sheets("Invoice data").Range("D:F")
  'Delete rows if invoice # is found
  Do Until Sheets("Invoice data").Range("A" & i).Value = ""
    If Sheets("Invoice data").Range("A" & i).Value = Sheets("Invoice").Range("E7") & "-" & Range("F7").Value Then
      Sheets("Invoice data").Range("A" & i).EntireRow.Delete
      i = 1
    End If
    i = i + 1
  Loop
  ' Find first empty row in columns D:G on sheet Invoice data
  Do Until WorksheetFunction.CountA(rng_dest.Rows(i)) = 0
    i = i + 1
  Loop
  'Copy range B17:F37 on sheet Invoice
  Set rng = Sheets("Invoice").Range("B17:H37")
  ' Copy rows containing values to sheet Invoice data
 For a = 1 To rng.Rows.Count
    If WorksheetFunction.CountA(rng.Rows(a)) <> 0 Then
      rng_dest.Rows(i).Value = rng.Rows(a).Value
      'Copy Invoice number
      Sheets("Invoice data").Range("A" & i).Value = Sheets("Invoice").Range("E7") & "-" & Range("F7").Value
      'Copy Date
      Sheets("Invoice data").Range("B" & i).Value = Sheets("Invoice").Range("B2").Value
      'Copy Project Name
      Sheets("Invoice data").Range("C" & i).Value = Sheets("Invoice").Range("C7").Value
      i = i + 1
    End If
  Next a
  Application.ScreenUpdating = True
End Sub

I'm still very new to VBA so I am utterly confused. I tried the debug function and this is where the code usually screws up: For a = 1 To rng.Rows.Count If WorksheetFunction.CountA(rng.Rows(a)) <> 0 Then rng_dest.Rows(i).Value = rng.Rows(a).Value . If you could help me fix this problem, I would appreciate it very much:)

Also, as a side note, is there a way to make it so rows with "0"s in the "Total"(column H) are not transferred to the Invoice data page? Thank you for your time. New Invoice page

In this picture I want everything from row 7 and below to be deleted because I didn't input anything in the invoice page for those rows.

Upvotes: 0

Views: 641

Answers (1)

R.Katnaan
R.Katnaan

Reputation: 2526

I modified the result for loop as follow:

'Copy rows containing values to sheet Invoice data
For a = 17 To 37

    If WorksheetFunction.CountA(Sheets("Invoice").Range("B" & a & ":H" & a)) <> 0 Then

        'Copy Invoice number
        Sheets("Invoice data").Range("A" & i) = Sheets("Invoice").Range("E7") & "-" & Sheets("Invoice").Range("F7")

        'Copy Date
        Sheets("Invoice data").Range("B" & i) = Sheets("Invoice").Range("B2")

        'Copy Project Name
        Sheets("Invoice data").Range("C" & i) = Sheets("Invoice").Range("C7")

        'Copy Name
        Sheets("Invoice data").Range("D" & i) = Sheets("Invoice").Range("B" & a)

        'Copy Hours
        Sheets("Invoice data").Range("E" & i) = Sheets("Invoice").Range("F" & a)

        'Copy Cost
        Sheets("Invoice data").Range("F" & i) = Sheets("Invoice").Range("G" & a)

        'Copy Total
        Sheets("Invoice data").Range("G" & i) = Sheets("Invoice").Range("H" & a)

        'increase invoice data row
        i = i + 1

    End If

Next a

I already tested the code with your input data. It work well for me. Try This.

Added:

If you want to add a new condition, modify if statement as follow:

If WorksheetFunction.CountA(Sheets("Invoice").Range("B" & a & ":G" & a)) <> 0  And Sheets("Invoice").Range("H" & a) > 0 Then

Upvotes: 1

Related Questions