Macro to Copy completed cells after formula

[Dsgoddard]

I have created a working document full of Vlookup formulas with various headings. The aim of this is to provide a sales person with what we call a Quote Sheet. Using this sheet they can put in their product code, put in the margin & it works out the sell price. They input their codes into one worksheet “Quote Sheet (Code)”, then go to another sheet that is basically is a copy of the first sheet except the cost prices have been removed.

On the “Quote Sheet (Code)” this is what happens: Cell D8 is where the user enters a product code, the value returned is going to be information about it – description, part number, cost etc in different columns. The user then inputs a margin in G8 & the price is worked out in column H. There are additional colummns J:R, S:V & T:W that add info about similar products that could be sold instead. If no value is entered in D8, then all the corresponding formulas return a blank value. Here’s a formula =IF(D8>0,VLOOKUP($D8,Prodman!$E:$M,8,FALSE),"")

The user can enter any product codes from cell D8 through to D601. All of the sheet is write protected apart from the Margin & Code cells.

This info is then duplicated on another worksheet “External Copy (Code)” having the cost price removed. This is done using variations of this formula to return the various info we need.
=UPPER(IF('Quote Sheet (Code)'!D8>0,'Quote Sheet (Code)'!D8,"")). Columns B:F give the Manufacturer, Description, Code, Part No. & Price. H:I, K:L & N:O return the similar products.

Once all the above info is completed, the user can press a button to run a macro to copy all data to a new worksheet with the correct formatting. The problem is it copies ALL lines down to line 602 regardless of whether it has been filled in or not. I’ve tried looking on this forum for a macro so it copies only down to the completed cells. This didn’t happen, probably because of the “” answer for no entries in each formula. Here is my current Macro:

' Macro1 Macro

'
With Application
.ScreenUpdating = False
With Cells
.Copy
Set wb = Workbooks.Add
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
End With
Range("A1").Select
Windows("Master Quote.xls").Activate
ActiveSheet.Shapes("Picture 20").Select
Application.CutCopyMode = False
Selection.Copy
wb.Activate
ActiveSheet.PasteSpecial Format:="Picture (Enhanced Metafile)", Link:=False _
, DisplayAsIcon:=False
Range("C8:C601").Select
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = True
End With
Columns("B:B").EntireColumn.AutoFit
Columns("D:F").EntireColumn.AutoFit
With ActiveSheet.PageSetup
.LeftFooter = _
.CenterFooter = _
"All information correct at time of publication." & Chr(10) & "All prices EXCLUDE VAT." & Chr(10) & "E and OE."
.RightFooter = "This list printed &D" & Chr(10) & "Page &P of &N"
.Orientation = xlLandscape
.Zoom = 58
End With
Application.DisplayAlerts = False
Sheets(Array("sheet2", "sheet3")).Delete
Application.DisplayAlerts = True
Sheets("Sheet1").Select
Sheets("Sheet1").Name = "Pricelist"
Range("B8").Select
End With
End Sub

I’d appreciate if someone could help me try & find a way of getting it to only copy over down to the last row that has a completed formula value in it.

Thanks in anticipation

 

[Dsgoddard]

Is anyone able to advise please?

Please ask questions if you need more info, sorry for the length or the request.

Thanks

 

[Dsgoddard]

I take it this isn't possible?

 

[JWARD]

im not the best at this but after you copy the data to the new sheet couldnt you insert something like

Range(Range("D65536"), Range("D65536").End(xlUp).Offset(1, 0)).EntireRow.delete

to delete all unnessary data?