Stop loop when cell is empty

[Shaharyar]

hello everyone i need help in vba code, in this code i want that if in source range data is not available then stop pasting specific cells for example b9 h9 etc in targeted sheet and also need code of pasting in last available row in targeted sheet and after everything then delete cell g18 value in source sheet and where to implement in code thanks.

Sub CopyPasteValues()
Dim sourceSheet As Worksheet
Dim targetSheet As Worksheet
Dim sourceRange As Range
Dim targetRange As Range
Dim row As Long
Dim lastRow As Long
Dim targetRow As Long
' Set the source sheet and range
Set sourceSheet = ThisWorkbook.Sheets("Create Output")
Set sourceRange = sourceSheet.Range("A23:H44")
' Set the target sheet and range
Set targetSheet = ThisWorkbook.Sheets("New Output")
Set targetRange = targetSheet.Range("A2:M2") 'start pasting after the header row
' Find the last row with data in the source range
lastRow = sourceRange.Find(What:="*", After:=sourceRange.Cells(1, 1), LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).row
' If there is no data in the source range, exit the subroutine
If lastRow = 0 Then
Exit Sub
End If
' Set the target row to the first row after the header row
targetRow = 2
' Loop through each row in the source range
For row = 1 To lastRow
' If there is data in the current row, copy and paste the values
If Not IsEmpty(sourceRange.Cells(row, 1)) Then
' Set the values for the fixed cells (invoice no, invoice date, etc.)
targetSheet.Range("A" & targetRow).value = sourceSheet.Range("B9").value 'invoice no
targetSheet.Range("B" & targetRow).value = sourceSheet.Range("H9").value 'invoice date
targetSheet.Range("C" & targetRow).value = sourceSheet.Range("C12").value 'buyer's name
targetSheet.Range("D" & targetRow).value = sourceSheet.Range("C15").value 'buyer's address
targetSheet.Range("E" & targetRow).value = sourceSheet.Range("G18").value 'recipe
' Set the values for the data cells (description, Uom, etc.)
targetSheet.Range("F" & targetRow).value = sourceRange.Cells(row, 3).value 'description
targetSheet.Range("G" & targetRow).value = sourceRange.Cells(row, 2).value 'Uom targetSheet
' Set the values for the data cells (quantity, item rate, etc.)
targetSheet.Range("H" & targetRow).value = sourceRange.Cells(row, 1).value 'quantity
targetSheet.Range("I" & targetRow).value = sourceRange.Cells(row, 4).value 'item rate
targetSheet.Range("J" & targetRow).value = sourceRange.Cells(row, 5).value 'Ex.Sale Tax Value
targetSheet.Range("K" & targetRow).value = sourceRange.Cells(row, 6).value 'Sales Tax Rate
targetSheet.Range("L" & targetRow).value = sourceRange.Cells(row, 7).value 'Total Sales Tax
targetSheet.Range("M" & targetRow).value = sourceRange.Cells(row, 8.value 'TOTAL AMOUNT
' Increment the target row
targetRow = targetRow + 1
End If
Next row
End Sub

 

[Shaharyar]

sir i am wondering if i need more help regarding this vba code continuous with same data with different requirement, so can i ask you here or i need to post new thread ?

 

[mumps]

If you have an entirely different question, you should start a new thread.

 

[Shaharyar]

not now but soon my question will be that my input data less output data which we created by your code, show remaining closing stock in closing stock sheet and it should be first in and first out this report generate on auto when ever i add new input it shows in closing stock and when i use that input in output it should less or 0 and when 0 it should delete from closing stock and also i will need stock reporter like in which all history remain like when which input used in which output invoice number date and quantity