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
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