Stop loop when cell is empty

Shaharyar

New Member
Joined
Jan 1, 2023
Messages
7
Office Version
  1. 2010
Platform
  1. Windows
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
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
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).
VBA Code:
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
 
Upvote 0
this is source sheet from where i need data to copy.

Account File.xlsm
ABCDEFGH
1
2ADD
3
4Sales Tax Invoice
5
6Sales Tax Registration No. 12-20-9999-780-37NTN #
7
8
9Serial No.1Date05.05.2020
10
11
12Buyer's NameXYZNTN #
13
14
15AddressKARACHINIC #
16
17
18S.T. Reg. #RECIPEFONDANT CAKE
19
20
21
22QuantityUomDescriptionItem RateEx.Sale Tax ValueSales Tax RateTotal Sales TaxIn.Sale Tax Value
23100GMSUGAR10.001,000.001,000.00
24100GMBROWN SUGAR10.001,000.001,000.00
252PCSEGGS10.0020.0020.00
2680GMOIL10.00800.00800.00
2780GMYOUGURT10.00800.00800.00
28125GMFLOUR10.001,250.001,250.00
2930GMCOCO POWDER10.00300.00300.00
304GMBAKING POWDER10.0040.0040.00
312GMBAKING SODA10.0020.0020.00
322GMSALT10.0020.0020.00
331GMVANILLA ESSENCE10.0010.0010.00
34250GMCHOCOLATE10.002,500.002,500.00
35150GMWHIPPED CREAM10.001,500.001,500.00
36392GMFONDANT10.003,920.003,920.00
37   
38   
39   
40   
41   
42   
43   
44
4513,180-13,180
46
CREATE OUTPUT
Cell Formulas
RangeFormula
A23: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 Validation
CellAllowCriteria
G18List=RECIPESEARCHLIST
H18Any 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
ABCDEFGHIJKLM
1INVOICE NOINVOICE DATEBUYER'S NAMEBUYERS' ADDRESSRECIPEDESCRIPTIONUOMQUANTITY Gm/Ml/PcsItem rateEx.Sale Tax ValueSales Tax RateTotal Sales TaxTOTAL AMOUNT
2105.05.2020XYZKARACHIFONDANT CAKESUGARGM1001010001000
3105.05.2020XYZKARACHIFONDANT CAKEBROWN SUGARGM1001010001000
4105.05.2020XYZKARACHIFONDANT CAKEEGGSPCS2102020
5105.05.2020XYZKARACHIFONDANT CAKEOILGM8010800800
6105.05.2020XYZKARACHIFONDANT CAKEYOUGURTGM8010800800
7105.05.2020XYZKARACHIFONDANT CAKEFLOURGM1251012501250
8105.05.2020XYZKARACHIFONDANT CAKECOCO POWDERGM3010300300
9105.05.2020XYZKARACHIFONDANT CAKEBAKING POWDERGM4104040
10105.05.2020XYZKARACHIFONDANT CAKEBAKING SODAGM2102020
11105.05.2020XYZKARACHIFONDANT CAKESALTGM2102020
12105.05.2020XYZKARACHIFONDANT CAKEVANILLA ESSENCEGM1101010
13105.05.2020XYZKARACHIFONDANT CAKECHOCOLATEGM2501025002500
14105.05.2020XYZKARACHIFONDANT CAKEWHIPPED CREAMGM1501015001500
15105.05.2020XYZKARACHIFONDANT CAKEFONDANTGM3921039203920
16105.05.2020XYZKARACHIFONDANT CAKE
17105.05.2020XYZKARACHIFONDANT CAKE
18105.05.2020XYZKARACHIFONDANT CAKE
19105.05.2020XYZKARACHIFONDANT CAKE
20105.05.2020XYZKARACHIFONDANT CAKE
21105.05.2020XYZKARACHIFONDANT CAKE
22105.05.2020XYZKARACHIFONDANT CAKE
NEW OUTPUT
 
Upvote 0
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?
 
Upvote 0
Do you want to clear the data in the New Output sheet before copying the data for a new recipe?
 
Upvote 0
no, i want to paste new recipe or old recipe data in available row in new output sheet
 
Upvote 0
Each time you run this macro, make sure that cell G18 in the CREATE OUTPUT sheet contains the recipe name.
VBA Code:
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,223,893
Messages
6,175,248
Members
452,623
Latest member
cliftonhandyman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top