HOW T COPY WORKSHEET 1

[Scunningham99]

How do you copy if possible, a single worksheet from a workbook to multiple workbooks. I know how to do this individually but i have got loads to do and this will take forever!!!!


Thanks in Advance!!

si

 

[logius]

I'm looking into this and almost have a solution.... I think. It's an interesting problem. : ----------------------------------------
If you are reading this, then you have read too far...

lightwarrior@hotmail.com

 

[logius]

WOO HOO!! :-D

Sub SelectFiles()
   Dim FileName$
   Dim Path$
'  Initialize a string variable for the first file
'  in a specified directory.  This sets the Dir( )
'  function to that directory.

   Path$ = InputBox("Enter the path: ", _
                    "Path", "C:\Temp\test")
   Select Case Right$(Path$, 1)

      Case "\":   FileName$ = Dir(Path$)

      Case Else:  FileName$ = Dir(Path$ & "\")

   End Select

'  Loop through the specified directory until the
'  Dir( ) function returns an empty string, indicating
'  there are not any more contents to be evaluated.

   Do While Len(FileName$) > 0

      PasteWorksheet Path$ & "\" & FileName$

   '  Re-initialize the string variable to the next
   '  file in the directory
      FileName$ = Dir()
   Loop
End Sub


Sub PasteWorksheet(FileNme$)
Dim wb As Workbook
    Application.ScreenUpdating = False
    
' open the source workbook
    Set wb = Workbooks.Open(FileNme$, True, False) 
    ThisWorkbook.Sheets("MySheet").Copy Before:= _
                         Workbooks(wb.Name).Sheets(1)

' close the source workbook, saving any changes
    wb.Close True 
    Set wb = Nothing 'free memory
    Application.ScreenUpdating = True
End Sub

The only stipulations are that you place all the workbooks into a single directory, then the macro will loop through all of the items in the directory and paste the selected worksheet into each (so make sure nothing else is in the directory). Make sure to change

MySheet

(in the last few lines) to the name of the sheet you want to copy from the current workbook. To use this, open the workbook with the sheet you want to copy, then hit Alt+F11, right-click on the workbook and select Insert>Module, then paste the code into the window. From there you go back to the Excel sheet and hit Alt+F8 and select the

SelectFiles

macro. This will paste the worksheet into each book as the first sheet (I don't know if that will be a problem). Try it with a small set of workbooks at first to see if it does what you want. ----------------------------------------
If you are reading this, then you have read too far...

lightwarrior@hotmail.com

 

[JohnYingling]

Record a macro while you do it manually and look at the generated code?

http://www.VBCompare.com

Compare Code (Text)

http://www.VBSortGen.com

Generate Sort in VB or VBScript

 

[logius]

Yeah, that's what I did :-D . Except I fiddled with it to work in a general sense. ----------------------------------------
If you are reading this, then you have read too far...

lightwarrior@hotmail.com