Paste-Special workbook

[WindsorKnot]

Hi All,

I'm having a little bit of trouble with my code. I basically need to copy-paste special a large workbook (50 worksheets) into a new range-valued workbook. I've defined Sheet as S, but in some instances when I use the variable "S" I received a runtime error 1004.

Please see the highlighted red "S" in the code below.

Also, if one knew of an easier method for this particular operation that would be great!

Sub PasteSpecial()


Dim W As Workbook
Dim S1 As Worksheet
Dim S As Sheets

Set W = Workbooks("newfile2")

Set S1 = W.Worksheets("Sheet1")

Set S = Sheets


S(Array("Sheet1", "Sheet2", "Sheet3")).Select
S1.Activate
S(Array("Sheet1", "Sheet2", "Sheet3")).Copy
Cells.Select
Range("a1").Activate
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Paste:=XlPasteFormat
S("Sheet2").Select
Cells.Select
Selection.PasteSpecial Paste:=xlPasteValues, Paste:=XlPasteFormat
S("Sheet3").Select
Cells.Select
Selection.PasteSpecial Paste:=xlPasteValues, Paste:=XlPasteFormat


MsgBox "New Range-Valued Workbook has been created"

Application.CutCopyMode = False


End Sub

 

[jasonb75]

Haven't tested this thoroughly, but think it should do what you want, as long as the sheets in both books are still named sheet1, sheet2, etc.

(Have both workbooks open while it runs).


You need to change oldbook.xlsm to the name of the book you're copying from (with the correct file extention).

newbook.xlsm to the book you're copying to

and value 50 to the number of sheets you want to copy

Sub PasteSpecial()
Dim x As Integer
x = 1
 
Windows("oldbook.xlsm").Activate
    Sheets ("Sheet") & x.Select
    Cells.Copy
    Windows("newbook.xlsm").Activate
    Sheets ("Sheet") & x.Select
    Cells.Select
    ActiveSheet.PasteSpecial Paste:=xlPasteValues, Paste:=XlPasteFormat
If x < 50 Then x = x + 1
Next x
MsgBox "New Range-Valued Workbook has been created"
Application.CutCopyMode = False
 
End Sub

Regards

Jason


** I would like to add that I'm not a an experienced user, so I would suggest saving your files before running as a precaution**

 

[WindsorKnot]

Will test out later today.

Thank you!

 

[WindsorKnot]

Your code seems to work well and should help me with my project.

Follow-up Question: I realized in my code the error occurred in the line below:

S("Sheet2").Select

I replaced the "S" with "Sheet" and it fixed the problem. However, since I had previously used Dim to define S as sheet shouldn't this have taken care of the problem?

I'm still new to VBA so any input would be greatly appreciated.

 

[jasonb75]

Your code seems to work well and should help me with my project.

Follow-up Question: I realized in my code the error occurred in the line below:

S("Sheet2").Select

I replaced the "S" with "Sheet" and it fixed the problem. However, since I had previously used Dim to define S as sheet shouldn't this have taken care of the problem?

I'm still new to VBA so any input would be greatly appreciated.

To be perfectly honest I'm new to VBA as well so I'm not entirely sure why your code didn't work, my personal preference is to try and avoid using Dim to abbreviate a single keyword, for example with your

 Dim s As Sheets

I can't see any advantage to it, other than reducing the amount of typing if you have to enter it a number of times.

When you're repeating your code with 1 minor change as you're doing it's easier to loop the code using If and Next, again using your task as example, the only change in each cycle was to go through sheet1, sheet2, etc.

By defining the value of x as 1, and then entering the code line

Sheets ("Sheet") & x.Select

You are telling the code to carry out the function

Sheets ("Sheetx").Select

Of course ("sheetx") doesn't actually exist, if this was entered into the code x would not be treated as a number, so this would fail, but by using ("Sheet") & x instead the value of x translates to a number which is then joined to the end of ("Sheet").

Then by using the line

If x < 50 Then x = x + 1
Next x

you are now saying if the value of x (number of pages copied) is less than 50, then add 1 to the current value of x and do this again*, but if the value of x is more than 50 then this is done. (If and Next are dependant on each other, so Next only happens when If increases the value of x, when If finds that the value of x is 50 it stops and so does Next.


*In my code this would mean that the line "x = 1" below "Dim x" would be "x = 2" on the second loop and so on until "x = 50", but it doesn't actually change the code, so if you use the code again later, x will always start at 1.

Hope all that makes sense.

 

[WindsorKnot]

Hi Jason, I tried running your code and ran into the following error:

Sheets ("Sheet") & [B][COLOR=red]x.[/COLOR][/B]Select

VBA says that x is an invalid qualifer on this line. Any thoughts?

 

[jasonb75]

Sorry Windsor, I've found my mistake(s), I've done a test on a workbook I have and its doing the job but not looping.

I'm making a few changes then trying again, once its doing the loop I'll post the revised code.

Jason

 

[jasonb75]

Found it

I've marked the changes and additions in red, the blue text was for my test sheets so that will be your book names and number of sheets as before.
Also the line

If x < 50 Then x = x + 1

needs to be deleted.

Sub PasteSpecial()
Dim x As Integer
For x = 1 To 3
 
  
Windows("sample.xlsm").Activate
    Sheets("Sheet" & x).Select
    Cells.Copy
    Windows("book3.xlsm").Activate
    Sheets("Sheet" & x).Select
    Cells.PasteSpecial Paste:=xlPasteValues, Paste:=XlPasteFormat
Next x

MsgBox "New Range-Valued Workbook has been created"
Application.CutCopyMode = False
 
End Sub

I had the ) in the wrong places, and also the wrong loop functions, should have been For and Next, not If and Next.

Sorry for any confusion, I felt certain I had it right before, but now it definitely works.

The only thing i noticed was that the cell format wasn't copied over so times converted to numbers, etc. I assume that your range format on the new sheet takes care of that but thought I would mention in in case you need a change for that.

Jason

 

[WindsorKnot]

Hi Jason,

Your code works fine. Two small follow up questions:

If I have more than three sheet would I just the highlighted statement below to 50?

Also, suppose the end-user didn't want to create a new workbook to input the code. Would it be possible to format the code to incorporate the statement

 [B][COLOR=red]workbooks.Add[/COLOR][/B] [\Code] ?
 
Thanks again
 
 
[CODE]
 
Sub PasteSpecial()
Dim x As Integer
[B][COLOR=red]For x = 1 To 3[/COLOR][/B]
 
  
Windows("Book3.xls").Activate
    Sheets("Sheet" & x).Select
    Cells.Copy
    Windows("book4.xls").Activate
    Sheets("Sheet" & x).Select
    Cells.PasteSpecial Paste:=xlPasteValues, Paste:=XlPasteFormat
Next x
MsgBox "New Range-Valued Workbook has been created"
Application.CutCopyMode = False
 
End Sub

 

[jasonb75]

Hi Windsor, in answer to your questions.

Hi Jason,

If I have more than three sheet would I just the highlighted statement below to 50?

Thats right, it also works both ways, for example if you wanted to copy the middle of your 50 sheet book you would do

For x = first sheet to copy To last sheet to copy

and inserting the sheet numbers in place of the text, although you would need to add another variable if you wanted to copy say sheet 11 to 50 from the old book to sheets 1 to 40 in the new one, the existing code would still copy each sheet to the identically named sheet in the new book.

Also, suppose the end-user didn't want to create a new workbook to input the code. Would it be possible to format the code to incorporate the statement

 workbooks.Add [\Code] ?

I'm not sure exactly what you mean there, are you looking for the code to create the new book and copy the pages to it, to save the user having to create the book manually first? In which case it would probable be easier to duplicate the workbook.