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]

[TABLE="width: 210"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] a [/TD]
[TD][/TD]
[TD]Front elevation[/TD]
[/TR]
[TR]
[TD] a [/TD]
[TD][/TD]
[TD]Ground Floor[/TD]
[/TR]
[TR]
[TD] a [/TD]
[TD][/TD]
[TD]Three section bay[/TD]
[/TR]
[TR]
[TD] a [/TD]
[TD][/TD]
[TD]Left[/TD]
[/TR]
[TR]
[TD] a [/TD]
[TD][/TD]
[TD]Lounge[/TD]
[/TR]
</tbody>[/TABLE]
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

[TABLE="width: 169"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Front elevation[/TD]
[/TR]
[TR]
[TD]Ground Floor[/TD]
[/TR]
[TR]
[TD]Three section bay[/TD]
[/TR]
[TR]
[TD]Lounge[/TD]
[/TR]
[TR]
[TD]Lounge[/TD]
[/TR]
</tbody>[/TABLE]

 

[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.