Copy full sheet of just values and formates if possible, am I on the right track?

[Ryk]

What I am doing should be very easy, but alas I am trying this vb thing
for just the second application. I have a main sheet that has formulas
throughout it, and want to make a macro or something in VB to just make
a copy of the sheet into a new sheet, including formatting etc but no
formulas. I need send copies of the sheet out and the size is too
large with formulas included. I managed to find this next bit, and
from what little I know I think it would do the trick....

Sub ValsOnly()
Dim Rng As String, r As Range
Dim wb As Workbook
Set r = Sheets("Sheet1").UsedRange
Rng = r.Address
Workbooks.Add -4167
Set wb = ActiveWorkbook
With wb.ActiveSheet
..Range(Rng).Formula = r.Value
End With
End Sub

(forgot who posted this, but I think name was William, too much surfing
for answers hehe so if it helps and poster sees this, many thanks)

I have tried this in 2 blank workbooks, but cannot get it to work at
all, I think I need more instruction on what names I need include, ie
sheet1, or range name etc. Also do i need to have a second workbook
open? Named?

Thanks in advance.....

Ryk

 

[Dave F]

I just came up with this:

Option Explicit

Sub Macro3()
'
' Macro3 Macro
' Macro recorded 8/17/2006 by HP Authorized Customer
'

'
Cells.Select
Selection.Copy
Workbooks.Add
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
End Sub

 

[Ryk]

Unsure what that is doing, it gives me a paste option and i can
manually chose where to put it and it works, but I think I need a
button tied to a macro or something, this part of excel new to me. Let
me explain a bit, I have 3 spreadsheets full of values and formulas, if
possible with even a macro or three, can i have sheet one, copy to a
preset workbook's sheet one by clicking a "month one" button, then
reset my spreadsheet, and when finished editting, hit a "month two"
button and have that sent to sheet2 of preset workbook etc? I'll have
alot of people working with this program and i need to make it very
easy too use for them. But end all be all, 3 different sheets, sent to
a new workbook, with values only is end goal.

Thanks for your reply as well Dave, I have posted 3 times, and this is
first reply I have recieved...

Ryk

 

[Dave F]

Hi, if you want to assign the macro to a button, just pull up the Forms
toolbar, select the button you want, draw it on the screen, click on the
button, and a dialogue box appears asking you which macro you want to assign
it to. Choose the appropriate macro, close the dialogue box, get out of
design mode, click the button, and watch your macro run.

 

[Ryk]

Just a quick note Dave, all worked out fine, took me awhile but got it
all working. Have bigger fish to fry now with it hehe. get one thing
done and everyone wants this and that added. Anyways thank you very
much for your help.

Ryk