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

 

[mumps]

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]

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

Account File.xlsm
	A	B	C	D	E	F	G	H
1
2	ADD
3
4	Sales Tax Invoice
5
6	Sales Tax Registration No. 12-20-9999-780-37						NTN #
7
8
9	Serial No.	1					Date	05.05.2020
10
11
12	Buyer's Name		XYZ			NTN #
13
14
15	Address		KARACHI			NIC #
16
17
18	S.T. Reg. #					RECIPE	FONDANT CAKE
19
20
21
22	Quantity	Uom	Description	Item Rate	Ex.Sale Tax Value	Sales Tax Rate	Total Sales Tax	In.Sale Tax Value
23	100	GM	SUGAR	10.00	1,000.00			1,000.00
24	100	GM	BROWN SUGAR	10.00	1,000.00			1,000.00
25	2	PCS	EGGS	10.00	20.00			20.00
26	80	GM	OIL	10.00	800.00			800.00
27	80	GM	YOUGURT	10.00	800.00			800.00
28	125	GM	FLOUR	10.00	1,250.00			1,250.00
29	30	GM	COCO POWDER	10.00	300.00			300.00
30	4	GM	BAKING POWDER	10.00	40.00			40.00
31	2	GM	BAKING SODA	10.00	20.00			20.00
32	2	GM	SALT	10.00	20.00			20.00
33	1	GM	VANILLA ESSENCE	10.00	10.00			10.00
34	250	GM	CHOCOLATE	10.00	2,500.00			2,500.00
35	150	GM	WHIPPED CREAM	10.00	1,500.00			1,500.00
36	392	GM	FONDANT	10.00	3,920.00			3,920.00
37
38
39
40
41
42
43
44
45					13,180		-	13,180
46
CREATE OUTPUT

Cell Formulas
Range	Formula
A23:A41	A23	=IFERROR(INDEX('OUTPUT FORMULA'!$D$4:D1000,'OUTPUT FORMULA'!$N4),"")
B23:B41	B23	=IFERROR(INDEX('OUTPUT FORMULA'!$F$4:F1000,'OUTPUT FORMULA'!$N4),"")
C23:C41	C23	=IFERROR(INDEX('OUTPUT FORMULA'!$C$4:C1000,'OUTPUT FORMULA'!$N4),"")
D23:D41	D23	=IFERROR(INDEX('OUTPUT FORMULA'!$E$4:E1000,'OUTPUT FORMULA'!$N4),"")
E23:E43	E23	=IFERROR(A23*D23,"")
A42:A43	A42	=IFERROR(INDEX('OUTPUT FORMULA'!$D$4:D1018,'OUTPUT FORMULA'!$N22),"")
B42:B43	B42	=IFERROR(INDEX('OUTPUT FORMULA'!$F$4:F1018,'OUTPUT FORMULA'!$N22),"")
C42:C43	C42	=IFERROR(INDEX('OUTPUT FORMULA'!$C$4:C1018,'OUTPUT FORMULA'!$N22),"")
D42:D43	D42	=IFERROR(INDEX('OUTPUT FORMULA'!$E$4:E1018,'OUTPUT FORMULA'!$N22),"")
H23:H43	H23	=IFERROR(E23+G23,"")
E45,G45:H45	E45	=SUM(E23:E44)

Cells with Data Validation
Cell	Allow	Criteria
G18	List	=RECIPESEARCHLIST
H18	Any 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.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	INVOICE NO	INVOICE DATE	BUYER'S NAME	BUYERS' ADDRESS	RECIPE	DESCRIPTION	UOM	QUANTITY Gm/Ml/Pcs	Item rate	Ex.Sale Tax Value	Sales Tax Rate	Total Sales Tax	TOTAL AMOUNT
2	1	05.05.2020	XYZ	KARACHI	FONDANT CAKE	SUGAR	GM	100	10	1000			1000
3	1	05.05.2020	XYZ	KARACHI	FONDANT CAKE	BROWN SUGAR	GM	100	10	1000			1000
4	1	05.05.2020	XYZ	KARACHI	FONDANT CAKE	EGGS	PCS	2	10	20			20
5	1	05.05.2020	XYZ	KARACHI	FONDANT CAKE	OIL	GM	80	10	800			800
6	1	05.05.2020	XYZ	KARACHI	FONDANT CAKE	YOUGURT	GM	80	10	800			800
7	1	05.05.2020	XYZ	KARACHI	FONDANT CAKE	FLOUR	GM	125	10	1250			1250
8	1	05.05.2020	XYZ	KARACHI	FONDANT CAKE	COCO POWDER	GM	30	10	300			300
9	1	05.05.2020	XYZ	KARACHI	FONDANT CAKE	BAKING POWDER	GM	4	10	40			40
10	1	05.05.2020	XYZ	KARACHI	FONDANT CAKE	BAKING SODA	GM	2	10	20			20
11	1	05.05.2020	XYZ	KARACHI	FONDANT CAKE	SALT	GM	2	10	20			20
12	1	05.05.2020	XYZ	KARACHI	FONDANT CAKE	VANILLA ESSENCE	GM	1	10	10			10
13	1	05.05.2020	XYZ	KARACHI	FONDANT CAKE	CHOCOLATE	GM	250	10	2500			2500
14	1	05.05.2020	XYZ	KARACHI	FONDANT CAKE	WHIPPED CREAM	GM	150	10	1500			1500
15	1	05.05.2020	XYZ	KARACHI	FONDANT CAKE	FONDANT	GM	392	10	3920			3920
16	1	05.05.2020	XYZ	KARACHI	FONDANT CAKE
17	1	05.05.2020	XYZ	KARACHI	FONDANT CAKE
18	1	05.05.2020	XYZ	KARACHI	FONDANT CAKE
19	1	05.05.2020	XYZ	KARACHI	FONDANT CAKE
20	1	05.05.2020	XYZ	KARACHI	FONDANT CAKE
21	1	05.05.2020	XYZ	KARACHI	FONDANT CAKE
22	1	05.05.2020	XYZ	KARACHI	FONDANT CAKE
NEW OUTPUT

 

[mumps]

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]

all do rows refer to the same recipe

 

[mumps]

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

 

[Shaharyar]

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

 

[mumps]

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]

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]

You are very welcome.