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,
THat code should paste the sheet into the workbook that does not contain the macro. I wouls advise you to add a msgbox to the code to see what is going on.

After your loop begins:
For Each wb In Workbooks
msgbox wb.Name
.
.
next wb

Then before you copy
msgbox wb.Name
Sheets("Sheet2").Copy Before:=wb.Sheets(1)

I may not completely understand what you're doing. But, you've only got two workbooks open. The loop can find either one depending on the logic!

(Either <> OR =)

Lukex

 

[jimmywanna]

It does exactly that Lukex.

But i need the sheet to paste into the workbook that does contain the macro.

my intention was to make a button on each of the front sheets of the workbooks i am updating, assign this macro, run it, then save the updated workbook.

i will always have the workbook i am copying the sheet from open, and the ones i am amending will be the only other on open.

i hope this makes sense.

thanks again for your time

jim

 

[Lukex]

OOOH! In that case you don't need the loop because you can grab the application workbook with:

Set wb = ThisWorkbook
Sheets("Mapping").Copy Before:=wb.Sheets(1)

Keep the:
Dim wb As Workbook

Lukex

 

[jimmywanna]

fantastic, I'll try that tomorrow when i'm at work.

I really appreciate your time mate.

thanks

jim

 

[jimmywanna]

Hi Lukex,

I have added the code you suggested, albeit blindly!!!

Could you have a look at what i have so far, as i am getting a run time error when i run the macro. I've obviously placed it in the wrong place.

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

Windows("Book1").Activate
Sheets("Sheet1").Select

Dim wb As Workbook
Set wb = ThisWorkbook
Sheets("Mapping").Copy Before:=wb.Sheets(1)


End Sub

any help would be great, i can taste the victory!!!!

jim

 

[Lukex]

Hey Jim,
That ("Mapping") sheet is from my workbook. Try changing it to the name of the sheet that you want to copy.

Let me know how it goes.

Lukex

 

[jimmywanna]

YOU BEAUTY!!!!!!!!!!!!!!!!

thanks so much mate.

This is revolutionary. I can complete this in hours and not weeks
Thank you soooooooo much



jim

 

[Lukex]

Not a problem. Glad to help.

Good Luck!
Lukex