Copy and paste selections and formatting to new worksheet

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

i have a large range of options on one worksheet. From these options, i would like to use a method of selection like a check box or cell containing qty to note the options to be copied to the next sheet (a quote). IE: options are 1-10, a person selects 1,4,6 and 9....i want these to appear on the next worksheet. I was using simply =IF(A!E1,A!B1," ") but left with blank cells inbetween those that werent selected. Also, the formatting was left behind. Is there any easier way to accomplish this or can i overcome the formatting problem?

Im a novice so any help is really appreciated!
 
Assume you have a quantity column (column D) on sheet1, list starts in D3,
and you enter data starting in A5 on sheet 2

Sub copydata()
Dim rw as Long, cell as Range
rw = 5
For each cell in Worksheets("Sheet1").Range("D3:D12")
if cell.Value > 0 then
cell.parent.Cells(cell.row,1).Resize(1,11).Copy _
Destination:=Worksheets("Quote").cells(rw,1)
rw = rw + 1
end if
Next
End Sub


copies column A to K of row with quantity > 0 to sheet quote.
--
Regards,
Tom Ogilvy


gavmer said:
i have a large range of options on one worksheet. From these options, i
Click to expand...
would like to use a method of selection like a check box or cell containing
qty to note the options to be copied to the next sheet (a quote). IE:
options are 1-10, a person selects 1,4,6 and 9....i want these to appear on
the next worksheet. I was using simply =IF(A!E1,A!B1," ") but left with
blank cells inbetween those that werent selected. Also, the formatting was
left behind. Is there any easier way to accomplish this or can i overcome
the formatting problem??
 
Back
Top