View Full Version : More vba help please
I have been chuntering away happily now for months developing a prog, but need some aid now if poss. Google is coming up short.
I have a workbook "PPrune.xls" which has 3 worksheets, 1, 2 and 3. I wish to be able to select which of the three (or all) I save to a new workbook 'Danny.xls", 1,2 and 3. I'm happy with the selection, and can save the first sheet as sheet 1 - it is adding the next 1 or 2 I am stumped on.
Milo Minderbinder
14th Jun 2012, 14:54
I don't have an answer - I know nowt about spreadsheets, but I suspect the lack if response is that people don't understand the question
What are you actually trying to do - simple resave a spreadsheet under another name? Or are these spreadsheets outputs from a program and you're trying to redirect the outputs to another format? Or what?
"What are you actually trying to do - simple resave a spreadsheet under another name? Or are these spreadsheets outputs from a program and you're trying to redirect the outputs to another format? Or what? " - I'm not sure where you got that from, but as you say you don't understand spreadsheets.
I thought "I have a workbook "PPrune.xls" which has 3 worksheets, 1, 2 and 3. I wish to be able to select which of the three (or all) I save to a new workbook 'Danny.xls", 1,2 and 3. I'm happy with the selection, and can save the first sheet as sheet 1 - it is adding the next 1 or 2 I am stumped on."
was pretty straightforward to anyone who understands Excel/Libre-Office programming, and I suspect it is. Hopefully someone will come along one day.
Milo Minderbinder
14th Jun 2012, 16:17
the comment "I have been chuntering away happily now for months developing a prog" is likely to throw a few people in a false direction then
The assumption would be that the spreadsheets are outputs from some other program that you're working on...
The assumption would be that the spreadsheets are outputs from THIS program that you're working on. would be correct. You may not realise, but a 'programme' and a 'worksheet' (although the VBA code is contained in a worksheet just to confuse you.....that's how VBA works, VB is slightly different) are two separate animals.
Milo Minderbinder
14th Jun 2012, 17:27
I'm well aware they are and so are most of the readers - and thats whats going to confuse.
It will not confuse those who understand.
Superpilot
15th Jun 2012, 01:46
I'm not being funny, but if all else fails you could always try a Mouse/Keyboard input recorder of some sort to do the job.
No sp, that's a good suggestion, but I am well past that stage. I need someone with a good knowledge of VBA who is prepared to help here (by PM/email).
Since you ask, the routine works fine as a keyboard recorded macro but will not run correctly in the main programme.
Milo Minderbinder
15th Jun 2012, 17:03
Try asking here
Visual Basic for Applications (VBA) Forum (http://social.msdn.microsoft.com/Forums/en-US/isvvba/threads)
or here
Free Excel\VBA Help Forum (http://www.ozgrid.com/forum/)
Yes, Milo, it is on the list. I am already 'registered from some years back, but I am a bit busy and I had hoped for a quick fix here.
Agent86
16th Jun 2012, 22:39
BOAC,
Google search bought up this
MSlink (http://support.microsoft.com/kb/288402)
Sub Mover3()
Dim BkName As String
Dim NumSht As Integer
Dim BegSht As Integer
'Starts with second sheet - replace with index number of starting sheet.
BegSht = 2
'Moves two sheets - replace with number of sheets to move.
NumSht = 2
BkName = ActiveWorkbook.Name
For x = 1 To NumSht
'Moves second sheet in source to front of designated workbook.
Workbooks(BkName).Sheets(BegSht).Move _
Before : = Workbooks("Test.xls").Sheets(1)
'In each loop, the next sheet in line becomes indexed as number 2.
'Replace Test.xls with the full name of the target workbook you want.
Next
End Sub
That should give you some ideas
Max
Good find, thanks Agent. I'll give that a go. Since the intitial responses I have been looking at a different approach, so with thanks to all for the help I will let this thread die..