Macro to Copy completed cells after formula

Dsgoddard

New Member
Joined
Jul 8, 2004
Messages
29
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
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

Dsgoddard

New Member
Joined
Jul 8, 2004
Messages
29
Is anyone able to advise please?

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

Thanks
 

JWARD

Board Regular
Joined
Aug 6, 2004
Messages
137
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?
 

Forum statistics

Threads
1,147,508
Messages
5,741,571
Members
423,668
Latest member
Audorin

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
Top