Excel VBA Query
Thread Starter
Per Ardua ad Astraeus
Joined: Mar 2000
Posts: 18,575
Likes: 4
From: UK
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
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
Joined: May 2011
Posts: 56
Likes: 0
From: London
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
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
Joined: May 2011
Posts: 56
Likes: 0
From: London
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
AfterwbkTarget.Sheets(3)
wbkSource.Worksheets("Sheet1").Copy
AfterwbkTarget.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
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
Thread Starter
Per Ardua ad Astraeus
Joined: Mar 2000
Posts: 18,575
Likes: 4
From: UK
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.
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.
Joined: Jun 2009
Posts: 1,344
Likes: 80
From: Bedford, UK
Joined: Jun 2009
Posts: 1,344
Likes: 80
From: Bedford, UK
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
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
Thread Starter
Per Ardua ad Astraeus
Joined: Mar 2000
Posts: 18,575
Likes: 4
From: UK
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........................
