Macro Problem

A

albertmb

Hi Everyone.

I do not know how to write a Macro, but I find it convenient to record one
when I need it. I encountered a problem on recording the following Macro:

Sub Copy2()
'
' Copy2 Macro
' Macro recorded 21/09/2008 by Albert Bartolo
'

'
ActiveWorkbook.Save
Sheets("Ships").Select
ActiveWindow.ScrollWorkbookTabs Position:=xlLast
Sheets(Array("Ships", "Phoenicia", "Salvo Grima", "Hotels", "Hard Rock", _
"Corinthia Flight Cat", "Airest", "La Salita - Arches", "Lemongrass
Imported", _
"Lemongrass Local")).Select
Sheets("Lemongrass Local").Activate
Sheets(Array("Ships", "Phoenicia", "Salvo Grima", "Hotels", "Hard Rock", _
"Corinthia Flight Cat", "Airest", "La Salita - Arches", "Lemongrass
Imported", _
"Lemongrass Local")).Copy
ChDir "C:\Documents and Settings\Bartolo\Desktop\Quotations\Archive"
ActiveWindow.Close
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets("Ships").Select
End Sub

If I copy the folder I am using this macro in, onto a pendrive or to another
computer it does not work. I realised that the problem is the ChDir, which I
marked in bold. Is there a possibility to modify the Macro so as it can work
on any computer.

Thank You

Albert
 
J

JMB

If you are wanting to save the new workbook in the same folder as the
original you could try using Thisworkbook.Path instead.

Sub Copy2()
ActiveWorkbook.Save
Sheets(Array("Ships", "Phoenicia", "Salvo Grima", "Hotels", "Hard Rock", _
"Corinthia Flight Cat", "Airest", "La Salita - Arches", "Lemongrass
Imported", _
"Lemongrass Local")).Copy
ChDir ThisWorkbook.Path
ActiveWindow.Close
End Sub
 
A

albertmb

Hi JMB,
Thank you for your kind response, but the macro you gave me does not do
exactly what I want, when I run it the save option goes to 'My Documuments'
and not the the workbook the folder is in. What I have is this:

Folder named : Quotations
In this folder I have another folder named 'Archive' and a worksheet named
quotations. What I need is to work on the worksheets named quotations and a
macro to save a copy in the Archive folder. The macro I have does it ok but
if I move the folder onto a USB pen or save it to another computer it does
not work. I know that this can be done easyly manually but I do these
worksheets for someone who is not so computer friendly and needs everything
as simple as possible.

I thank you once again and hope you can solve my problem.

Regards

Albert
 
J

JMB

Sorry about that. I never use ChDir much, but after looking at VBA help it
appears that it will only change the active directory, but not the active
drive. Try this:

Sub Copy2()
ActiveWorkbook.Save
Sheets(Array("Ships", "Phoenicia", "Salvo Grima", "Hotels", "Hard Rock", _
"Corinthia Flight Cat", "Airest", "La Salita - Arches", _
"Lemongrass Imported", "Lemongrass Local")).Copy
ChDrive Left(ThisWorkbook.Path, _
InStr(1, ThisWorkbook.Path, ":", vbTextCompare) - 1)
ChDir ThisWorkbook.Path
ActiveWindow.Close
End Sub
 
A

albertmb

Thank You once again JMB and I apologise for being a nuisance, but still
needs a little. This macro took me to the main folder (Quotations) but not
into the subfolder (Archive).
Thank you once again for your kind help.

Albert
 
Top