Hi all trying to join SpecialCells(xlConstants) with SpecialCells(xlFormulas)

[Pfereday]

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Hi all I'm trying to join SpecialCells(xlConstants) with SpecialCells(xlFormulas) and no matter what I [/FONT]try I can only one or the other to work never both.

Sub Done()
Dim NR As Long
Application.ScreenUpdating = False
Application.EnableEvents = False
With Sheets("Your Quotation")
    NR = .Range("D" & .Rows.Count).End(xlUp).Row + 1
    If NR > 149 Then NR = NR + 1    .Range("C" & NR).Resize(, 1).Value = Range("B:C").Value
    
    
    Range("A:A").SpecialCells(xlConstants & xlFormulas).Offset(, 2).Copy .Range("C" & NR)
    Range("A:A").SpecialCells(xlConstants).Offset(, 3).Copy .Range("D" & NR)
    Range("E:E").SpecialCells(xlConstants).Offset(, 1).Copy .Range("D" & NR)
    
End With
Application.ScreenUpdating = True
Application.EnableEvents = True
Beep
End Sub

 

[Pfereday]

If I could show you a screenshot it would be clear what I am trying to do, but unfortunately I am not at home by the pc and can't send a screenshot. Thanks for all the help will be back at the pc tomorrow and have another go at it.

 

[Pfereday]

a		Front elevation
a		Ground Floor
a		Three section bay
a		Left
a		Lounge

<colgroup><col><col><col></colgroup><tbody>
</tbody>

the A opposite the word left is produced by a formula all the other a are text

 

[Fluff]

With my suggestion from post#2 I got the results in col F.

Book1
	A	B	C	D	E	F
2	a		Front elevation			Front elevation
3			Ground Floor			Three section bay
4	a		Three section bay			Left
5	a		Left			Lounge
6	a		Lounge			Tom
7			Tom
Sheet2

Cell Formulas
Range	Formula
A4	=IF(C4="Three section bay","a","")
A7	=IF(C7="Fluff","a","")

Did you try Rick's suggestion in post#10?

 

[Pfereday]

Sub Done()
Dim NR As Long
Application.ScreenUpdating = False
Application.EnableEvents = False
With Sheets("Your Quotation")
    NR = .Range("D" & .Rows.Count).End(xlUp).Row + 1
    If NR > 149 Then NR = NR + 1
    .Range("C" & NR).Resize(, 1).Value = Range("B:C").Value
    
    
    Union(Range("A:A").SpecialCells(xlConstants), Range("A:A").SpecialCells(xlFormulas)).Offset(, 2).Copy Range("C" & NR)
    
    Range("A:A").SpecialCells(xlConstants).Offset(, 3).Copy .Range("D" & NR)
    
    Range("E:E").SpecialCells(xlConstants).Offset(, 1).Copy .Range("D" & NR)
    
    Range("A:A").SpecialCells(xlConstants).Offset(, 2).Copy .Range("C" & NR)
End With
Application.ScreenUpdating = True
Application.EnableEvents = True
Beep
End Sub

still only coping the text A'S not copying the formula one

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]=IF(C4 ="Three section bay","a","")[/FONT]

 

[Pfereday]

I didn't understand Rick's suggestion in post#10? ​

 

[Fluff]

There's a missing . on this line before the last Range

Union(Range("A:A").SpecialCells(xlConstants), Range("A:A").SpecialCells(xlFormulas)).Offset(, 2).Copy [COLOR=#ff0000].[/COLOR]Range("C" & NR)

 

[Pfereday]

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Union(Range("A:A").SpecialCells(xlConstants), Range("A:A").SpecialCells(xlFormulas)).Offset(, 2).Copy .Range("C" & NR)[/FONT]

altered the above to .range and now getting the five items but the last two are both lounge not left and lounge

Front elevation

Ground Floor

Three section bay

Lounge

Lounge

<colgroup><col></colgroup><tbody>
</tbody>

 

[Fluff]

That's probably because you're overwriting the results with this line

Range("A:A").SpecialCells(xlConstants).Offset(, 2).Copy .Range("C" & NR)

 

[Pfereday]

Altered code to this

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Sub Done()
Dim NR As Long
Application.ScreenUpdating = False
Application.EnableEvents = False
With Sheets("Your Quotation")
    NR = .Range("D" & .Rows.Count).End(xlUp).Row + 1
    If NR > 149 Then NR = NR + 1[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]    .Range("C" & NR).Resize(, 1).Value = Range("B:C").Value
    
    
    Union(Range("A:A").SpecialCells(xlConstants), Range("A:A").SpecialCells(xlFormulas)).Offset(, 2).Copy .Range("C" & NR)
    
    Range("A:A").SpecialCells(xlConstants).Offset(, 3).Copy .Range("D" & NR)
    
    Range("E:E").SpecialCells(xlConstants).Offset(, 1).Copy .Range("D" & NR)
    
    
End With
Application.ScreenUpdating = True
Application.EnableEvents = True
Beep
End Sub[/FONT]

but now no line space between the next lot thats copied

 

[Fluff]

I have no idea what you are trying to do & a lot of your code is simply overwriting what other lines of code have done.
If the last used row in col D is less than 150 you are not adding a blank row.