# Stop loop when cell is empty



## Shaharyar (Jan 1, 2023)

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


----------



## mumps (Jan 1, 2023)

> if in source range data is not available then stop pasting specific cells for example b9 h9 etc in targeted sheet


I'm not sure what you mean by this statement but try this macro.  If it doesn't work as you requested, then please use the XL2BB add-in (icon in the menu) to attach screenshots (*not pictures*) of your two sheets.  Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary). 

```
Sub CopyPasteValues()
    Application.ScreenUpdating = False
    Dim sourceSheet As Worksheet, targetSheet As Worksheet, rng As Range
    Set sourceSheet = Sheets("Create Output")
    If WorksheetFunction.CountA(sourceSheet.Range("A23:H44")) = 0 Then Exit Sub
    Set targetSheet = Sheets("New Output")
    For Each rng In sourceSheet.Range("A2", sourceSheet.Range("A" & Rows.Count).End(xlUp))
        If rng <> "" Then
            With sourceSheet
                targetSheet.Cells(targetSheet.Rows.Count, "A").End(xlUp).Offset(1).Resize(, 13).Value = Array(.Range("B9").Value, .Range("H9").Value, _
                    .Range("C12").Value, .Range("C15").Value, .Range("G18").Value, .Range("C" & rng.row).Value, .Range("B" & rng.row).Value, _
                    .Range("A" & rng.row).Value, .Range("D" & rng.row).Value, .Range("E" & rng.row).Value, .Range("F" & rng.row).Value, _
                    .Range("G" & rng.row).Value, .Range("H" & rng.row).Value)
                .Range("G18").ClearContents
            End With
        End If
    Next rng
    Application.ScreenUpdating = True
End Sub
```


----------



## Shaharyar (Jan 1, 2023)

this is source sheet from where i need data to copy.

Account File.xlsmABCDEFGH12ADD34Sales Tax Invoice56Sales Tax Registration No. 12-20-9999-780-37NTN #789Serial No.1Date05.05.2020101112Buyer's NameXYZNTN #131415AddressKARACHINIC #161718S.T. Reg. #RECIPEFONDANT CAKE19202122QuantityUomDescriptionItem RateEx.Sale Tax ValueSales Tax RateTotal Sales TaxIn.Sale Tax Value23100GMSUGAR10.001,000.001,000.0024100GMBROWN SUGAR10.001,000.001,000.00252PCSEGGS10.0020.0020.002680GMOIL10.00800.00800.002780GMYOUGURT10.00800.00800.0028125GMFLOUR10.001,250.001,250.002930GMCOCO POWDER10.00300.00300.00304GMBAKING POWDER10.0040.0040.00312GMBAKING SODA10.0020.0020.00322GMSALT10.0020.0020.00331GMVANILLA ESSENCE10.0010.0010.0034250GMCHOCOLATE10.002,500.002,500.0035150GMWHIPPED CREAM10.001,500.001,500.0036392GMFONDANT10.003,920.003,920.0037      38      39      40      41      42      43      444513,180-13,18046CREATE OUTPUTCell FormulasRangeFormulaA23:A41A23=IFERROR(INDEX('OUTPUT FORMULA'!$D$4:D1000,'OUTPUT FORMULA'!$N4),"")B23:B41B23=IFERROR(INDEX('OUTPUT FORMULA'!$F$4:F1000,'OUTPUT FORMULA'!$N4),"")C23:C41C23=IFERROR(INDEX('OUTPUT FORMULA'!$C$4:C1000,'OUTPUT FORMULA'!$N4),"")D23:D41D23=IFERROR(INDEX('OUTPUT FORMULA'!$E$4:E1000,'OUTPUT FORMULA'!$N4),"")E23:E43E23=IFERROR(A23*D23,"")A42:A43A42=IFERROR(INDEX('OUTPUT FORMULA'!$D$4:D1018,'OUTPUT FORMULA'!$N22),"")B42:B43B42=IFERROR(INDEX('OUTPUT FORMULA'!$F$4:F1018,'OUTPUT FORMULA'!$N22),"")C42:C43C42=IFERROR(INDEX('OUTPUT FORMULA'!$C$4:C1018,'OUTPUT FORMULA'!$N22),"")D42:D43D42=IFERROR(INDEX('OUTPUT FORMULA'!$E$4:E1018,'OUTPUT FORMULA'!$N22),"")H23:H43H23=IFERROR(E23+G23,"")E45,G45:H45E45=SUM(E23:E44)Cells with Data ValidationCellAllowCriteriaG18List=RECIPESEARCHLISTH18Any value

and this is targeted sheet where i need to paste data, so what is happening here that after copy data from a23:h36 and paste in targeted sheet it should stop pasting specific cell data like cell b9 h9 c12 c15 and g18 in targeted sheet because there is no data in a37. what my code doing is that after a37 is counting columns till h44 or i can say a44 and pasting specific cell data in targeted sheet. hope i explain well now , sorry if still not getting my point. thanks for your response and help.  

Account File.xlsmABCDEFGHIJKLM1INVOICE NOINVOICE DATEBUYER'S NAMEBUYERS' ADDRESSRECIPEDESCRIPTIONUOMQUANTITY Gm/Ml/PcsItem rateEx.Sale Tax ValueSales Tax RateTotal Sales TaxTOTAL AMOUNT2105.05.2020XYZKARACHIFONDANT CAKESUGARGM10010100010003105.05.2020XYZKARACHIFONDANT CAKEBROWN SUGARGM10010100010004105.05.2020XYZKARACHIFONDANT CAKEEGGSPCS21020205105.05.2020XYZKARACHIFONDANT CAKEOILGM80108008006105.05.2020XYZKARACHIFONDANT CAKEYOUGURTGM80108008007105.05.2020XYZKARACHIFONDANT CAKEFLOURGM12510125012508105.05.2020XYZKARACHIFONDANT CAKECOCO POWDERGM30103003009105.05.2020XYZKARACHIFONDANT CAKEBAKING POWDERGM410404010105.05.2020XYZKARACHIFONDANT CAKEBAKING SODAGM210202011105.05.2020XYZKARACHIFONDANT CAKESALTGM210202012105.05.2020XYZKARACHIFONDANT CAKEVANILLA ESSENCEGM110101013105.05.2020XYZKARACHIFONDANT CAKECHOCOLATEGM250102500250014105.05.2020XYZKARACHIFONDANT CAKEWHIPPED CREAMGM150101500150015105.05.2020XYZKARACHIFONDANT CAKEFONDANTGM392103920392016105.05.2020XYZKARACHIFONDANT CAKE17105.05.2020XYZKARACHIFONDANT CAKE18105.05.2020XYZKARACHIFONDANT CAKE19105.05.2020XYZKARACHIFONDANT CAKE20105.05.2020XYZKARACHIFONDANT CAKE21105.05.2020XYZKARACHIFONDANT CAKE22105.05.2020XYZKARACHIFONDANT CAKENEW OUTPUT


----------



## mumps (Jan 1, 2023)

Assuming that you have data in multiple rows starting at row 23 in the CREATE OUTPUT sheet, does each row refer to a different recipe (G18) or do all rows refer to the same recipe?


----------



## Shaharyar (Jan 1, 2023)

all do rows refer to the same recipe


----------



## mumps (Jan 1, 2023)

Do you want to clear the data in the New Output sheet before copying the data for a new recipe?


----------



## Shaharyar (Jan 1, 2023)

no, i want to paste new recipe or old recipe data in available row in new output sheet


----------



## mumps (Jan 2, 2023)

Each time you run this macro, make sure that cell G18 in the CREATE OUTPUT sheet contains the recipe name.

```
Sub CopyPasteValues()
    Application.ScreenUpdating = False
    Dim sourceSheet As Worksheet, targetSheet As Worksheet, rng As Range, LastRow As Long
    Set sourceSheet = Sheets("CREATE OUTPUT")
    LastRow = sourceSheet.Columns(1).Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row
    If WorksheetFunction.CountA(sourceSheet.Range("A23:H44")) = 0 Then Exit Sub
    Set targetSheet = Sheets("NEW OUTPUT")
    With sourceSheet
            For Each rng In .Range("A23:A" & LastRow)
                targetSheet.Cells(targetSheet.Rows.Count, "A").End(xlUp).Offset(1).Resize(, 5).Value = Array(.Range("B9").Value, .Range("H9").Value, _
                    .Range("C12").Value, .Range("C15").Value, .Range("G18").Value)
                targetSheet.Cells(targetSheet.Rows.Count, "F").End(xlUp).Offset(1).Resize(, 8).Value = Array(.Range("C" & rng.Row).Value, _
                 .Range("B" & rng.Row).Value, .Range("A" & rng.Row).Value, .Range("D" & rng.Row).Value, .Range("E" & rng.Row).Value, _
                 .Range("F" & rng.Row).Value, .Range("G" & rng.Row).Value, .Range("H" & rng.Row).Value)
            Next rng
        .Range("G18").ClearContents
    End With
    Application.ScreenUpdating = True
End Sub
```


----------



## Shaharyar (Jan 2, 2023)

sir thanks a lot its so amazing and simple and fast its now done everything what i need right now thanks again sir love you salute you <3


----------



## mumps (Jan 2, 2023)

You are very welcome.


----------



## Shaharyar (Jan 1, 2023)

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 (Jan 2, 2023)

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 (Jan 2, 2023)

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


----------



## Shaharyar (Jan 2, 2023)

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


----------

