Help continuation...

[thelastflame]

Hey guys, here's what we've got.

Option Explicit
Sub Macro1()



Dim lngMyRow As Long

If WorksheetFunction.CountA(Sheets("B").Cells) = 0 Then
lngMyRow = 1
Else
lngMyRow = Sheets("B").Cells(Rows.Count, "A").End(xlUp).Row + 1
End If

Sheets("A").Range("D3:D20").Copy
Sheets("B").Range("A" & lngMyRow).PasteSpecial Transpose:=True
Application.CutCopyMode = False

End Sub

Thanks to another forum member, he helped me get this far however there is another issue. What the code above does, is copy the cells D3 through D20 into another sheet horizontally, adding a new row of copied cells every time the macro button is hit. Thing is, the cell it's copying updates every so often and I need to copied cells on sheet B to be static, basically copy the value not the formula? Any input ill greatly help, thx!

 

[thelastflame]

Also I'm not sure if this effects the outcome in any way, but the first cell, D3 is a Data Validation list, and everything following it is a formula, vlookup.

 

[Hermanito]

Changing this line:

Sheets("B").Range("A" & lngMyRow).PasteSpecial Transpose:=True

into this:

Sheets("B").Range("A" & lngMyRow).PasteSpecial Paste:=xlPasteValues, Transpose:=True

should do the trick.

 

[thelastflame]

Hey there and thanks a bunch for your help, unfortunately however im getting this error:

Compile error:
syntax error

 

[Hermanito]

That might have to do with the comma:

Sheets("B").Range("A" & lngMyRow).PasteSpecial Paste:=xlPasteValues[COLOR=red][B],[/B][/COLOR] Transpose:=True

In my system, it has to be a comma, try a semicolon instead maybe?