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

 

[WindsorKnot]

I'm just thinking that some of the people I interact with aren't too tech savy and will want the process as automatic as possible. I.E. they just click a commandbutton and the workbook is copied automatically into a new range-valued workbook.

 

[jasonb75]

Ok, I get it now.

Would the format for every book be the same? To make this as easy as possible I think it would be best to have a "Master copy" of the range valued book that users can copy their existing book to.

That I can do for you on the basis of:-

-user opens master copy of range valued workbook
-marco starts as soon as the book opens, displaying the user with 2 options.
-option 1. create a copy of the book and import their data to it.
-option 2. close the book.
-selecting optoin 1 will then ask the user to select their old file.
-VBA will then create a copy of the master range valued workbook
-close the original master.
-copy everthing to the copy of the master using our existing code.
-and finally save the new workbook

If the format is going to vary between the workbooks then I'm afraid it could get beyond my capabilities.

 

[WindsorKnot]

Jason,

Instead launching the command upon opening the workbook, would we be able to do what you are saying by clicking a commandbutton instead?

Also, as a VBA "noob" how did you initially learn VBA? Course, self-study, etc?

 

[WindsorKnot]

I'm currently reading Excel VBA Macro Programming by Richard Shepherd. Its an easy read, but I'm hoping to be able to memorize more commands

 

[jasonb75]

The command button is do-able for me, but does everything else match up with what you're looking for?

My reason behind the auto launch idea was that you could then password protect the master to prevent any mishaps, the vba would have the ability to take the password off of the copy after it's created.

As for learning VBA, I only started a few weeks ago, so I'm still in the "noob" stage of things, most of my ideas come from 3 sources, the first, if I want vba to automate a procedure that can be done manually, such as your initial copy/paste special I simply record a macro while I carry out the actions, then edit the macro to create the loop.

The second source, if I didn't know where to start for the loop, i would do an internet search for "VBA loop", which never gives you exactly what you're looking for, but usually you can find a code example that gives you the right idea.

The third source is this forum, if I can't get the result i need from the first idea and the web search comes up with nothing of use then I post here for other peoples opinions or ideas, you will find that almost every time someone has had a similar task to carry out, or will be able to provide you with some possible solutions.

Also I will be looking at a course of some type in the future, but I would like to have an understanding of things first.

Jason

 

[WindsorKnot]

Jason,

Upon further thought, I think your idea will work best.

Thanks for the VBA tips.

-John

 

[jasonb75]

Ok John, leave it with me, I'm going to be quite busy for the next day or 2 but I'll try to fit this in as soon as I get chance,
unless someone else reads this and offers to do it before I have chance.

Jason

 

[WindsorKnot]

Hi Jason have you have a chance to try and figure this one out?

 

[WindsorKnot]

I've tried another approach which has gotten me close, but no cigar.


The code below works when you remove the line:

 Cells.PasteSpecial Paste:=xlPasteValues, Paste:=XlPasteFormat 

But it simply copies each sheet into a different new workbook instead of just one. Second, if we could find out how to incorporate the code above into the subfunction below, I think it will work.

Sub PastSpecial()


Dim nSheets As Integer
nSheets = ActiveWorkbook.Sheets.Count
For lSheets = 1 To nSheets
ActiveWorkbook.Sheets(lSheets).Activate

Sheets.Select
Sheets.Copy

    Workbooks.Add
    
    ActiveWorkbook.Sheets(lSheets).Activate
    
      Cells.PasteSpecial Paste:=xlPasteValues, Paste:=XlPasteFormat
      
   
        Next lSheets
        
        
      
      
        
 End Sub

 

[jasonb75]

John, haven't had much time to look at this one, sorry.

Had a quick look at your last post and moved the add workbook out of the loop, that should do the trick, but you might need to put in an active workbook switch below the add function, as it stands it might try to activate and copy the new book to the old one instead, not sure without testing.

I've tried another approach which has gotten me close, but no cigar.


The code below works when you remove the line:

 Cells.PasteSpecial Paste:=xlPasteValues, Paste:=XlPasteFormat 

But it simply copies each sheet into a different new workbook instead of just one. Second, if we could find out how to incorporate the code above into the subfunction below, I think it will work.

Sub PastSpecial()
 
 
Dim nSheets As Integer
nSheets = ActiveWorkbook.Sheets.Count
 
Workbooks.Add
 
For lSheets = 1 To nSheets
ActiveWorkbook.Sheets(lSheets).Activate
 
Sheets.Select
Sheets.Copy
 
     
    ActiveWorkbook.Sheets(lSheets).Activate
 
      Cells.PasteSpecial Paste:=xlPasteValues, Paste:=XlPasteFormat
 
 
        Next lSheets
 
 
 
 
 
 End Sub