当源区域中有空单元格时，我希望停止循环。
例如：
我的源范围数据在"A36"之后为空。
应停止在目标工作表中粘贴特定单元格数据"B9"、"H9"等。
此代码仍在目标工作表中粘贴"B9"和"H9"的数据。
另外，如何将复制的数据粘贴到目标工作表的可用行中？

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