Excel VBA Query
 

Any VBA gurus please -

I have a macro which works with data from 3 worksheets in a book. I wish to save only worksheets 1 and 2 to a new workbook.

At the moment I save (all 3) with

ActiveWorkbook.SaveAs TITLE

I have had some but not all success with

ActiveWorkbook.ActiveSheet("Sheet1").SaveAs TITLE

At last I can contribute something of value:

There are two ways of talking to a worksheet in a workbook either by its index:

ActiveWorkbook.Worksheets(1).SaveAs “Title”

Or by its name

ActiveWorkbook.Worksheets(“WorksheetName”).SaveAs “Title”

The worksheets are indexed 1 to n, the 1st worksheet is the leftmost in the viewing pane, if you move Sheet2 in front of Sheet1 it will become worksheets(1)

Equivalently Workbooks("WorkbookName") is preferred to ActiveWorkbook, as it will error rather than do something irreversible to the activeworkbook

This sub will create a new workbook and copy Sheet1, Sheet2 and Sheet3 to the new workbook. As Sheet1 etc. exist in the new workbook then copied sheets will be named Sheet1(1) etc. If this is an issue we could either delete Sheet1 etc. from the new workbook before the copy or rename the sheets in the new workbook

Sub CreateAndCopy()
''' Define this wokbook
Dim wbkSource As Workbook
Set wbkSource = ActiveWorkbook

''' Create a new workbook
Dim wbkTarget As Workbook
Workbooks.Add
Set wbkTarget = ActiveWorkbook

''' Copy the worksheets accross backwards
wbkSource.Worksheets("Sheet3").Copy
After:=wbkTarget.Sheets(3)
wbkSource.Worksheets("Sheet2").Copy
After:=wbkTarget.Sheets(3)
wbkSource.Worksheets("Sheet1").Copy
After:=wbkTarget.Sheets(3)

''' Save and close the new workbook
wbkTarget.SaveAs "Target"
wbkTarget.Close

End Sub


To rename a sheet the syntax
Sheets("Sheet1").Name = "NewName"

The : = syntax id reproduced as :=

Happy to private if you need further advice

Thanks indeed, Phineas - a great help - no PMs necessary!

I need to beg some more VBA help - my thanks indeed to those who helped before.

I am 'firing on all four' now but am stuck on FTP issues. I am able to FTP files up and down, creating the necessary TEXTFILE to "get" or "put" inthe code and using "FTPcommand = "ftp -n -s -v:" & Chr(34) & TEXTFILE & Chr(34)" to execute the up/down request.

What I would like to do is use the same procedure to list the files on my server and add them to a listbox.but I cannot work out how to use "dir" to achieve this. If anyone can help I can PM the current lines of code. Google seem to be devoid of simple subs or functions.

:)

R O - are you saying this is definitely covered in that book? I am on my 4th VBA library book and none have so far.

BOAC, '4th VBA library book' ! What with the re-use of old PATA drives I am getting the impression of penury at your end.

Re book, claims to on CD. May just be a little beginners for you but I liked it (although the faux matey style of these things is nauseating). There is on on VBA for excel (but which version of excel I don't know).

Hope your sitting down, the first of these retails at £22 :eek:

Mr O - not 'extreme poverty - just a little cautious. Your suggestion is noted, thanks. I am receiving excellent help also from PhineasC, but I will be prepared to order that book from my local library........................:rolleyes:

Well I guess there is never any harm in being careful.