Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copy full sheet of just values and formates if possible, am I on the right track?
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copy full sheet of just values and formates if possible, am I on t
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" wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copy full sheet of just values and formates if possible, am I on t
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copy full sheet of just values and formates if possible, am I
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" wrote: 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copy full sheet of just values and formates if possible, am I
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|