macro problem

[jimmywanna]

Sorry to go on about this but i must be so close now!!!!!!

the following macro runs perfectly, copying a sheet from book 1 and pasting it as the very first sheet in book 3.



Windows("Book1").Activate
Sheets("Sheet1 (2)").Select
Sheets("Sheet1 (2)").Copy Before:=Workbooks("Book3").Sheets(1)

I really need ("Book3") to be generic, so the the sheet from book one will paste into the only other open workbook.

i have tried ("") and ("Book.xls") and (".xls") but to no avail.

thanks again guys

jim

 

[Lukex]

Jim,
I assume that you know where you want the copy to go when you call your routine. One way to do that is to set your sub routine up to take a worksheet as a parameter.

In the calling routine set a worksheet object to the sheet:
Dim wsCopyBefore as Worksheet

Set wsCopyBefore = Workbooks("OTHER OPEN WORKBOOK.xls").Sheets(1)
' Call Format routine
FORMAT wsCopyBefore

In the formating routine:
Sub Format(ws as Worksheet)
.
.
Sheets("Sheet2").Copy Before:=ws

Lukex

 

[jimmywanna]

Thanks very much for replying, i have to admit your knowledge of excel exceeds mine.

I'm not really sure what you mean.

In the calling routine set a worksheet object to the sheet:
Dim wsCopyBefore as Worksheet

Set wsCopyBefore = Workbooks("OTHER OPEN WORKBOOK.xls").Sheets(1)
' Call Format routine
FORMAT wsCopyBefore

"Other open workbook" will be called a different thing each time, does excel know that "other open workbook" is exactly that, the only other workbook open?

In the formating routine:
Sub Format(ws as Worksheet)
.
.
Sheets("Sheet2").Copy Before:=ws

Is this a new sub routine? or an addition to the macro?

Sorry to question you again but to be honest i'm still really learning this and although i can record and assign macro's i'm not clear on what the coding means!!!

thanks again for your reply, if i can get this working it will save me hours as i have about 120 workbooks which i need to attach this "template" sheet to after the first sheet is formated.

cheers

jim

 

[jimmywanna]

I think all i really need is what the code would be for "open workbook"
this way it would be completely generic and the actual workbooks name wouldn't matter.

does this exist?


or alternitively is there any way of copying the sheet, then pasting it into multiple workbooks in one go?


jim

 

[jimmywanna]

I have reworded my original post, sorry to keep asking about this but i recon there must be an answer. this answer could save my life!!!!!!
(well, a lot of time anyway!)

 

[jimmywanna]

I have reworded my original post, sorry to keep asking about this but i recon there must be an answer. this answer could save my life!!!!!!
(well, a lot of time anyway!)

 

[Lukex]

Jim,
I see your problem. You will have to look for the other open workbook within your macro. Follow your formatting code with a loop thru the open workbooks. Make sure to declare your variables. It should look something like this. -I've tested it...

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> FORMAT()
<SPAN style="color:#00007F">Dim</SPAN> sAppWorkbook <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> wb <SPAN style="color:#00007F">As</SPAN> Workbook
  
  
   <SPAN style="color:#007F00">'--- This should follow your formatting code</SPAN>
   <SPAN style="color:#007F00">' This gets the name of the workbook that contains this macro</SPAN>
   sAppWorkbook = ThisWorkbook.Name
  
   <SPAN style="color:#007F00">' Loop thru each open workbook. Look for the Other wb.</SPAN>
   <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> wb <SPAN style="color:#00007F">In</SPAN> Workbooks
      <SPAN style="color:#00007F">If</SPAN> wb.Name <> sAppWorkbook <SPAN style="color:#00007F">Then</SPAN>
         <SPAN style="color:#007F00">' We now have the wb object set to the other workbook.</SPAN>
         <SPAN style="color:#007F00">' So exit.</SPAN>
         <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">For</SPAN>
      <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
   <SPAN style="color:#00007F">Next</SPAN> wb
   Sheets("Sheet2").Copy Before:=wb.Sheets(1)
  
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

Lukex

 

[jimmywanna]

Thanks Lukex.

i can almost see what it's doing, (i'm getting there)

where do i place this code?

here?
Windows("Book1").Activate
Sheets("Sheet1 (2)").Select
Sheets("Sheet1 (2)").Copy Before:=Workbooks("").Sheets(1)
or here?

thanks again for your help Lukex.

jim

 

[jimmywanna]

I really must be missing something, i placed the code in the places you suggested, but it didn't paste the sheet.

my head is hurting now!!!!

jim

 

[jimmywanna]

me again.

does this look like i'm anywhere near the answer?
this only copies and pastes the sheet into the the same workbook, not into the other open workbook.

Sub Button1_Click()
'
' Button1_Click Macro
' Macro recorded 02/11/2005 by GibsonJa
'

Windows("Book1").Activate
Sheets("Sheet1").Select
Dim sAppWorkbook As String
Dim wb As Workbook


'--- This should follow your formatting code
' This gets the name of the workbook that contains this macro
sAppWorkbook = ThisWorkbook.Name

' Loop thru each open workbook. Look for the Other wb.
For Each wb In Workbooks
If wb.Name <> sAppWorkbook Then
' We now have the wb object set to the other workbook.
' So exit.
Exit For
End If
Next wb
Sheets("Sheet2").Copy Before:=wb.Sheets(1)

End Sub