PC Review


Reply
Thread Tools Rate Thread

Copy & Paste Special Using Values

 
 
Paul Black
Guest
Posts: n/a
 
      25th Nov 2006
Hi Everybody,

I would like VBA Code that is Similar to the One Below ( but Not
Adapting the One Below, I Still want to Use it as it is ) which Works a
Treat ...

Code:
Sub SheetInsert()
Dim s1 As String, s2 As String
Dim x As Long, y As Long, i As Long
Dim pasteto As String
Application.ScreenUpdating = False

s1 = "How many sheets would you like to add?"
s2 = "What would you like the number of the first sheet to be?"
x = InputBox(s1)
y = InputBox(s2, "Insert Sheets")

For i = 1 To x
Sheets.Add After:=Sheets(Sheets.Count)
pasteto = i + y - 1
ActiveSheet.Name = pasteto
With Sheets(pasteto)
Sheets("Master").Cells.Copy _
Destination:=.Range("A1")
.Range("A1").Value = .Name
.Range("B22").Select
End With
Next i

Application.ScreenUpdating = True
End Sub

.... to Ask me in the First Box which Sheet to Start with, and the
Second Box to Ask me which Sheet to End with ( the Sheets are Numbered
1 to Whatever for Example ).
I then want Each of those Sheets to be Highlighted in the Top Left Hand
Corner so it Selects ALL of the Sheet, and for ALL of the Sheets
Specified. I then want to Copy and Paste Special Using Values.
And Finally, I would like the Cursor to End Up in Cell B22 of EVERY
Sheet Please.

Thanks in Advance.
All the Best.
Paul

 
Reply With Quote
 
 
 
 
Nigel
Guest
Posts: n/a
 
      26th Nov 2006
This does it...
..
Sub SheetCopyValues()
Dim s1 As String, s2 As String
Dim x As Long, y As Long, i As Long
Dim pasteto As String
Application.ScreenUpdating = False

s1 = "Enter number of first sheet"
s2 = "Enter number of last sheet"
x = InputBox(s1, "First Sheet to copy to")
y = InputBox(s2, "Last Sheet to copy to")

For i = x To y
Sheets("Master").Cells.Copy
On Error Resume Next
With Sheets(CStr(i))
.Activate
.Range("A1").PasteSpecial Paste:=xlPasteValues
.Range("A1") = .Name
.Range("B22").Select
End With
Next i
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub



--
Cheers
Nigel



"Paul Black" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hi Everybody,
>
> I would like VBA Code that is Similar to the One Below ( but Not
> Adapting the One Below, I Still want to Use it as it is ) which Works a
> Treat ...
>
> Code:
> Sub SheetInsert()
> Dim s1 As String, s2 As String
> Dim x As Long, y As Long, i As Long
> Dim pasteto As String
> Application.ScreenUpdating = False
>
> s1 = "How many sheets would you like to add?"
> s2 = "What would you like the number of the first sheet to be?"
> x = InputBox(s1)
> y = InputBox(s2, "Insert Sheets")
>
> For i = 1 To x
> Sheets.Add After:=Sheets(Sheets.Count)
> pasteto = i + y - 1
> ActiveSheet.Name = pasteto
> With Sheets(pasteto)
> Sheets("Master").Cells.Copy _
> Destination:=.Range("A1")
> .Range("A1").Value = .Name
> .Range("B22").Select
> End With
> Next i
>
> Application.ScreenUpdating = True
> End Sub
>
> ... to Ask me in the First Box which Sheet to Start with, and the
> Second Box to Ask me which Sheet to End with ( the Sheets are Numbered
> 1 to Whatever for Example ).
> I then want Each of those Sheets to be Highlighted in the Top Left Hand
> Corner so it Selects ALL of the Sheet, and for ALL of the Sheets
> Specified. I then want to Copy and Paste Special Using Values.
> And Finally, I would like the Cursor to End Up in Cell B22 of EVERY
> Sheet Please.
>
> Thanks in Advance.
> All the Best.
> Paul
>



 
Reply With Quote
 
Paul Black
Guest
Posts: n/a
 
      26th Nov 2006
Hi Nigel,

Thanks for the Reply.
What my Original Macro does is Copy x Number of the Sheet Named
"Master" and Insert them After the Sheet Number I put in Box 1 Until it
Reaches the Sheet Number I put in Box 2.
This is OK but you can Appreciate that After Several Hundred Inserts of
Sheets the File Size gets Quite Big Because of All the Formulas.
Ideally the New Macro will go from the Sheet Number I Specify in Box 1
Until the Sheet Number I Specify in Box 2, and Transfer All the
Formulas to Values and Leave the Cursor on Cell B22.
My Appologies for Not Explaining Myself Clearly Enough in my Original
Post.

Thanks in Advance.
All the Best.
Paul

Nigel wrote:
> This does it...
> .
> Sub SheetCopyValues()
> Dim s1 As String, s2 As String
> Dim x As Long, y As Long, i As Long
> Dim pasteto As String
> Application.ScreenUpdating = False
>
> s1 = "Enter number of first sheet"
> s2 = "Enter number of last sheet"
> x = InputBox(s1, "First Sheet to copy to")
> y = InputBox(s2, "Last Sheet to copy to")
>
> For i = x To y
> Sheets("Master").Cells.Copy
> On Error Resume Next
> With Sheets(CStr(i))
> .Activate
> .Range("A1").PasteSpecial Paste:=xlPasteValues
> .Range("A1") = .Name
> .Range("B22").Select
> End With
> Next i
> Application.CutCopyMode = False
> Application.ScreenUpdating = True
> End Sub
>
>
>
> --
> Cheers
> Nigel
>
>
>
> "Paul Black" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > Hi Everybody,
> >
> > I would like VBA Code that is Similar to the One Below ( but Not
> > Adapting the One Below, I Still want to Use it as it is ) which Works a
> > Treat ...
> >
> > Code:
> > Sub SheetInsert()
> > Dim s1 As String, s2 As String
> > Dim x As Long, y As Long, i As Long
> > Dim pasteto As String
> > Application.ScreenUpdating = False
> >
> > s1 = "How many sheets would you like to add?"
> > s2 = "What would you like the number of the first sheet to be?"
> > x = InputBox(s1)
> > y = InputBox(s2, "Insert Sheets")
> >
> > For i = 1 To x
> > Sheets.Add After:=Sheets(Sheets.Count)
> > pasteto = i + y - 1
> > ActiveSheet.Name = pasteto
> > With Sheets(pasteto)
> > Sheets("Master").Cells.Copy _
> > Destination:=.Range("A1")
> > .Range("A1").Value = .Name
> > .Range("B22").Select
> > End With
> > Next i
> >
> > Application.ScreenUpdating = True
> > End Sub
> >
> > ... to Ask me in the First Box which Sheet to Start with, and the
> > Second Box to Ask me which Sheet to End with ( the Sheets are Numbered
> > 1 to Whatever for Example ).
> > I then want Each of those Sheets to be Highlighted in the Top Left Hand
> > Corner so it Selects ALL of the Sheet, and for ALL of the Sheets
> > Specified. I then want to Copy and Paste Special Using Values.
> > And Finally, I would like the Cursor to End Up in Cell B22 of EVERY
> > Sheet Please.
> >
> > Thanks in Advance.
> > All the Best.
> > Paul
> >


 
Reply With Quote
 
Nigel
Guest
Posts: n/a
 
      27th Nov 2006
Minor changes required -- see below

Sub SheetCopyValues()
Dim s1 As String, s2 As String
Dim x As Long, y As Long, i As Long

Application.ScreenUpdating = False
s1 = "Enter number of first sheet"
s2 = "Enter number of last sheet"
x = InputBox(s1, "First Sheet to copy to")
y = InputBox(s2, "Last Sheet to copy to")

For i = x To y
On Error Resume Next
With Sheets(CStr(i))
.Activate
.Cells.Copy
.Range("A1").PasteSpecial Paste:=xlPasteValues
.Range("A1") = .Name
Application.CutCopyMode = False
.Range("B22").Select
End With
Next i

Application.ScreenUpdating = True
End Sub

--
Cheers
Nigel



"Paul Black" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hi Nigel,
>
> Thanks for the Reply.
> What my Original Macro does is Copy x Number of the Sheet Named
> "Master" and Insert them After the Sheet Number I put in Box 1 Until it
> Reaches the Sheet Number I put in Box 2.
> This is OK but you can Appreciate that After Several Hundred Inserts of
> Sheets the File Size gets Quite Big Because of All the Formulas.
> Ideally the New Macro will go from the Sheet Number I Specify in Box 1
> Until the Sheet Number I Specify in Box 2, and Transfer All the
> Formulas to Values and Leave the Cursor on Cell B22.
> My Appologies for Not Explaining Myself Clearly Enough in my Original
> Post.
>
> Thanks in Advance.
> All the Best.
> Paul
>
> Nigel wrote:
>> This does it...
>> .
>> Sub SheetCopyValues()
>> Dim s1 As String, s2 As String
>> Dim x As Long, y As Long, i As Long
>> Dim pasteto As String
>> Application.ScreenUpdating = False
>>
>> s1 = "Enter number of first sheet"
>> s2 = "Enter number of last sheet"
>> x = InputBox(s1, "First Sheet to copy to")
>> y = InputBox(s2, "Last Sheet to copy to")
>>
>> For i = x To y
>> Sheets("Master").Cells.Copy
>> On Error Resume Next
>> With Sheets(CStr(i))
>> .Activate
>> .Range("A1").PasteSpecial Paste:=xlPasteValues
>> .Range("A1") = .Name
>> .Range("B22").Select
>> End With
>> Next i
>> Application.CutCopyMode = False
>> Application.ScreenUpdating = True
>> End Sub
>>
>>
>>
>> --
>> Cheers
>> Nigel
>>
>>
>>
>> "Paul Black" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>> > Hi Everybody,
>> >
>> > I would like VBA Code that is Similar to the One Below ( but Not
>> > Adapting the One Below, I Still want to Use it as it is ) which Works a
>> > Treat ...
>> >
>> > Code:
>> > Sub SheetInsert()
>> > Dim s1 As String, s2 As String
>> > Dim x As Long, y As Long, i As Long
>> > Dim pasteto As String
>> > Application.ScreenUpdating = False
>> >
>> > s1 = "How many sheets would you like to add?"
>> > s2 = "What would you like the number of the first sheet to be?"
>> > x = InputBox(s1)
>> > y = InputBox(s2, "Insert Sheets")
>> >
>> > For i = 1 To x
>> > Sheets.Add After:=Sheets(Sheets.Count)
>> > pasteto = i + y - 1
>> > ActiveSheet.Name = pasteto
>> > With Sheets(pasteto)
>> > Sheets("Master").Cells.Copy _
>> > Destination:=.Range("A1")
>> > .Range("A1").Value = .Name
>> > .Range("B22").Select
>> > End With
>> > Next i
>> >
>> > Application.ScreenUpdating = True
>> > End Sub
>> >
>> > ... to Ask me in the First Box which Sheet to Start with, and the
>> > Second Box to Ask me which Sheet to End with ( the Sheets are Numbered
>> > 1 to Whatever for Example ).
>> > I then want Each of those Sheets to be Highlighted in the Top Left Hand
>> > Corner so it Selects ALL of the Sheet, and for ALL of the Sheets
>> > Specified. I then want to Copy and Paste Special Using Values.
>> > And Finally, I would like the Cursor to End Up in Cell B22 of EVERY
>> > Sheet Please.
>> >
>> > Thanks in Advance.
>> > All the Best.
>> > Paul
>> >

>



 
Reply With Quote
 
Paul Black
Guest
Posts: n/a
 
      28th Nov 2006
Thanks for the Reply Nigel.

Your Code Works Great if there are NO Merged Cells in Any of the Sheets.
Unfortunately I have Several Merged Cells in Each of the Sheets.

Thanks in Advance.
All the Best.
Paul



*** Sent via Developersdex http://www.developersdex.com ***
 
Reply With Quote
 
Nigel
Guest
Posts: n/a
 
      29th Nov 2006
Unmerge them first, they are not a good idea anyway!

--
Cheers
Nigel



"Paul Black" <(E-Mail Removed)> wrote in message
news:O$(E-Mail Removed)...
> Thanks for the Reply Nigel.
>
> Your Code Works Great if there are NO Merged Cells in Any of the Sheets.
> Unfortunately I have Several Merged Cells in Each of the Sheets.
>
> Thanks in Advance.
> All the Best.
> Paul
>
>
>
> *** Sent via Developersdex http://www.developersdex.com ***



 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Re: Copy - Paste Special (Values) ankur Microsoft Excel Programming 2 19th Dec 2006 01:16 PM
Copy Paste Special Values Brian Matlack Microsoft Excel Programming 1 17th Oct 2005 05:22 PM
copy paste special values mike allen Microsoft Excel Programming 7 12th Oct 2005 06:06 PM
Dynamic Copy/Paste Special Formulas/Paste Special Values Sharon Perez Microsoft Excel Programming 3 7th Aug 2004 09:49 PM
Copy, Paste special, Values Fritz Microsoft Excel Misc 1 17th Nov 2003 06:43 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 05:09 AM.