I am using office 2003
I recorded a macro to open my master wb, copy an entire sheet and
pastespecial to a blank sheet inserted in another workbook.
The problem is when I run the macro I get a dialog box giving me the
choice of pasting unicode text or just text. I chose Unicode Text - but
that did not paste my formatting or my formulas.

Here is the macro:
Sub GetMstrInfo()
Workbooks.Open Filename:="C:\Pricing\TestJoes\TestMaster.xls"
Sheets("Tr-Vul").Select
Range("A1:K32").Select
Range("A2").Activate
Selection.Copy
ActiveWindow.Close
Sheets.Add
ActiveSheet.PasteSpecial Format:="Unicode Text", Link:=False, _
DisplayAsIcon:=False
End Sub

Could someone please tell me how to adapt this macro so that my new
worksheet has all the formulas and the formatting as the worksheet I am
copying from? I see PasteSpecial Format:="Unicode Text", but I don't
know how to change that statement to get what i need.

TIA
Joanne

maybe something like this:

Sub GetMstrInfo()
Dim wb1 As Workbook
Dim wb2 As Workbook
Set wb1 = ActiveWorkbook
Workbooks.Open Filename:="C:\Pricing\TestJoes\TestMaster.xls"
Set wb2 = ActiveWorkbook
With wb2.Sheets("Tr-Vul")
.Range("A1:K32").Copy
End With
With wb1
.Sheets.Add
.Worksheets(Worksheets.Count).Paste
End With
Application.CutCopyMode = False
wb2.Close savechanges:=False
End Sub

--


Gary


"Joanne" wrote in message
...
I am using office 2003
I recorded a macro to open my master wb, copy an entire sheet and
pastespecial to a blank sheet inserted in another workbook.
The problem is when I run the macro I get a dialog box giving me the
choice of pasting unicode text or just text. I chose Unicode Text - but
that did not paste my formatting or my formulas.

Here is the macro:
Sub GetMstrInfo()
Workbooks.Open Filename:="C:\Pricing\TestJoes\TestMaster.xls"
Sheets("Tr-Vul").Select
Range("A1:K32").Select
Range("A2").Activate
Selection.Copy
ActiveWindow.Close
Sheets.Add
ActiveSheet.PasteSpecial Format:="Unicode Text", Link:=False, _
DisplayAsIcon:=False
End Sub

Could someone please tell me how to adapt this macro so that my new
worksheet has all the formulas and the formatting as the worksheet I am
copying from? I see PasteSpecial Format:="Unicode Text", but I don't
know how to change that statement to get what i need.

TIA
Joanne

"Joanne" wrote in message
...
I am using office 2003
I recorded a macro to open my master wb, copy an entire sheet and
pastespecial to a blank sheet inserted in another workbook.
The problem is when I run the macro I get a dialog box giving me the
choice of pasting unicode text or just text. I chose Unicode Text - but
that did not paste my formatting or my formulas.

Here is the macro:
Sub GetMstrInfo()
Workbooks.Open Filename:="C:\Pricing\TestJoes\TestMaster.xls"
Sheets("Tr-Vul").Select
Range("A1:K32").Select
Range("A2").Activate
Selection.Copy
ActiveWindow.Close
Sheets.Add
ActiveSheet.PasteSpecial Format:="Unicode Text", Link:=False, _
DisplayAsIcon:=False
End Sub

Could someone please tell me how to adapt this macro so that my new
worksheet has all the formulas and the formatting as the worksheet I am
copying from? I see PasteSpecial Format:="Unicode Text", but I don't
know how to change that statement to get what i need.

TIA
Joanne

Joanne,

Since it appears that you want to paste everything instead of just the text
or just the values, try replacing the line:

ActiveSheet.PasteSpecial Format:="Unicode Text", Link:=False, _
DisplayAsIcon:=False

with:

ActiveSheet.Paste

or:

ActiveSheet.PasteSpecial Paste:=xlPasteAll

And see if that gives you the results you desire...

David

If you want formulas and formatting, then you probably just want to copy and
paste, not pastespecial:

Sub GetMstrInfo()
Dim sh as Worksheet
Dim bk as Workbook
sheets.add
set sh = Activesheet
set bk= Workbooks.Open( Filename:="C:\Pricing\TestJoes\TestMaster.xls")
bk.Sheets("Tr-Vul").Range("A1:K32").copy sh.Range("A1")
bk.close SaveChanges:=False
End Sub

--
Regards,
Tom Ogilvy


"Joanne" wrote:

I am using office 2003
I recorded a macro to open my master wb, copy an entire sheet and
pastespecial to a blank sheet inserted in another workbook.
The problem is when I run the macro I get a dialog box giving me the
choice of pasting unicode text or just text. I chose Unicode Text - but
that did not paste my formatting or my formulas.

Here is the macro:
Sub GetMstrInfo()
Workbooks.Open Filename:="C:\Pricing\TestJoes\TestMaster.xls"
Sheets("Tr-Vul").Select
Range("A1:K32").Select
Range("A2").Activate
Selection.Copy
ActiveWindow.Close
Sheets.Add
ActiveSheet.PasteSpecial Format:="Unicode Text", Link:=False, _
DisplayAsIcon:=False
End Sub

Could someone please tell me how to adapt this macro so that my new
worksheet has all the formulas and the formatting as the worksheet I am
copying from? I see PasteSpecial Format:="Unicode Text", but I don't
know how to change that statement to get what i need.

TIA
Joanne

Hi Joanne,

If you want to copy a worksheet with all formats etc, then try the Worksheet
copy. Record a macro to do it and you will get the code. You do it this way:-

Open the workbook into which you want to copy the worksheet.

Change windows to the worksheet to be copied.

Right click on the worksheet tab name-Move or Copy-Select the To workbook-
Check box to Create copy- Ok.

The copied worksheet becomes the active sheet. If it is a unique name in the
new workbook then it keeps the same name it had in the original workbook
otherwise it will have (2) after the name. If required, in the macro use
ActiveSheet.Name = "Whatever" if you want to rename it.

Regards,

OssieMac


"Joanne" wrote:

I am using office 2003
I recorded a macro to open my master wb, copy an entire sheet and
pastespecial to a blank sheet inserted in another workbook.
The problem is when I run the macro I get a dialog box giving me the
choice of pasting unicode text or just text. I chose Unicode Text - but
that did not paste my formatting or my formulas.

Here is the macro:
Sub GetMstrInfo()
Workbooks.Open Filename:="C:\Pricing\TestJoes\TestMaster.xls"
Sheets("Tr-Vul").Select
Range("A1:K32").Select
Range("A2").Activate
Selection.Copy
ActiveWindow.Close
Sheets.Add
ActiveSheet.PasteSpecial Format:="Unicode Text", Link:=False, _
DisplayAsIcon:=False
End Sub

Could someone please tell me how to adapt this macro so that my new
worksheet has all the formulas and the formatting as the worksheet I am
copying from? I see PasteSpecial Format:="Unicode Text", but I don't
know how to change that statement to get what i need.

TIA
Joanne

Thanks for your help everyone.
Joanne wrote:

I am using office 2003
I recorded a macro to open my master wb, copy an entire sheet and
pastespecial to a blank sheet inserted in another workbook.
The problem is when I run the macro I get a dialog box giving me the
choice of pasting unicode text or just text. I chose Unicode Text - but
that did not paste my formatting or my formulas.

Here is the macro:
Sub GetMstrInfo()
Workbooks.Open Filename:="C:\Pricing\TestJoes\TestMaster.xls"
Sheets("Tr-Vul").Select
Range("A1:K32").Select
Range("A2").Activate
Selection.Copy
ActiveWindow.Close
Sheets.Add
ActiveSheet.PasteSpecial Format:="Unicode Text", Link:=False, _
DisplayAsIcon:=False
End Sub

Could someone please tell me how to adapt this macro so that my new
worksheet has all the formulas and the formatting as the worksheet I am
copying from? I see PasteSpecial Format:="Unicode Text", but I don't
know how to change that statement to get what i need.

TIA
Joanne