Help continuation...

thelastflame

New Member
Joined
May 3, 2012
Messages
14
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!
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
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.
 
Upvote 0
Changing this line:
Code:
Sheets("B").Range("A" & lngMyRow).PasteSpecial Transpose:=True
into this:
Code:
Sheets("B").Range("A" & lngMyRow).PasteSpecial Paste:=xlPasteValues, Transpose:=True
should do the trick.
 
Upvote 0
Hey there and thanks a bunch for your help, unfortunately however im getting this error:

Compile error:
syntax error
 
Upvote 0
That might have to do with the comma:
Code:
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?
 
Upvote 0

Forum statistics

Threads
1,211,792
Messages
6,103,998
Members
447,889
Latest member
birdman15

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top