Hi, I am using following vb code
Its working fine but also copying data all the way down because my cells containes formula
cell range AE30:AG46 (Sheet: Quote)
So it is copying all empty data all the way, I just want to copy that data which is visible, i am using following formula in cell
For example cell: AE30 have formula : =+IF(C30="","",$B$25&$AF$29&C30&$AE$29&D30&$AE$29&E30&$AG$29&$D$28)
Code:
Sub Save_Data()
Dim rng As Range
Dim i As Long
Dim a As Long
Dim rng_dest As Range
Application.ScreenUpdating = False
i = 1
Set rng_dest = Sheets("Database").Range("D:F")
' Find first empty row in columns D:F on sheet Database
Do Until WorksheetFunction.CountA(rng_dest.Rows(i)) = 0
i = i + 1
Loop
'Copy range AE30:AG46 on sheet Quote to Variant array
Set rng = Sheets("Quote").Range("AE30:AG46")
' Copy rows containing values to sheet Database
For a = 1 To rng.Rows.Count
If WorksheetFunction.CountA(rng.Rows(a)) <> 0 Then
rng_dest.Rows(i).Value = rng.Rows(a).Value
'Copy Date
Sheets("Database").Range("A" & i).Value = Sheets("Quote").Range("J15").Value
'Copy Quote#
Sheets("Database").Range("B" & i).Value = Sheets("Quote").Range("J16").Value
'Copy Customer
Sheets("Database").Range("C" & i).Value = Sheets("Quote").Range("J17").Value
i = i + 1
End If
Next a
Application.ScreenUpdating = True
Range("J16").Value = Range("J16").Value + 1
Range("J17").ClearContents
Range("D16:E18").ClearContents
Range("D3:E3").ClearContents
Range("D4:J5").ClearContents
Range("P30:P46").ClearContents
Range("C30:E46").ClearContents
End Sub
Its working fine but also copying data all the way down because my cells containes formula
cell range AE30:AG46 (Sheet: Quote)
So it is copying all empty data all the way, I just want to copy that data which is visible, i am using following formula in cell
For example cell: AE30 have formula : =+IF(C30="","",$B$25&$AF$29&C30&$AE$29&D30&$AE$29&E30&$AG$29&$D$28)