Paste special in VBA

[Scottie7275]

Hi,

I have the following code which creates a table in a worksheet based on data copied from another worksheet. However it throws up a syntax error and everything I have tried isn't working.

 With Sheets("Data")        x = .Cells(1, .Columns.Count).End(xlToLeft).Column
        On Error Resume Next
        Set rng = .Cells(1, 1).Resize(, x).Find(what:=Sheets("New Product or Supplier").Cells(4, 3).Value, LookIn:=xlValues, lookat:=xlWhole)
        On Error GoTo 0
        
        If Not rng Is Nothing Then
            .Cells(.Rows.Count, rng.Column).End(xlUp).Offset(1).Value = Sheets("New Product or Supplier").Cells(4, 4).Value
            Set rng = Nothing
        Else
             Dim NextCol As Long
    If Sheets("Data").Range("A1").Value = "" Then
        NextCol = 1
    Else
        NextCol = Sheets("Data").Cells(1, Columns.Count).End(xlToLeft).Column + 1
    End If
    Sheets("New Product or Supplier").Range("C4").Copy Sheets("Data").Cells(1, NextCol).PasteSpecial xlPasteValuesAndNumberFormats
    Sheets("New Product or Supplier").Range("D4").Copy Sheets("Data").Cells(2, NextCol).PasteSpecial xlPasteValuesAndNumberFormats

I want to paste the values but not the formats etc.

Thanks for any help.

Scott

 

[Gerald Higgins]

I'm not a VBA expert, but have you tried replacing

xlPasteValuesAndNumberFormats

with

xlPasteValues

?

 

[Fluff]

try

   Sheets("New Product or Supplier").Range("C4").Copy
   Sheets("Data").Cells(1, NextCol).PasteSpecial xlPasteValuesAndNumberFormats
   Sheets("New Product or Supplier").Range("D4").Copy
   Sheets("Data").Cells(2, NextCol).PasteSpecial xlPasteValuesAndNumberFormats

Alternaively if you just want values

    Sheets("Data").Cells(1, NextCol).Value = Sheets("New Product or Supplier").Range("C4").Value
    Sheets("Data").Cells(2, NextCol).Value = Sheets("New Product or Supplier").Range("D4").Value

 

[Scottie7275]

Thanks Fluff.

Works a treat!

 

[Fluff]

Glad to help & thanks for the feedback