Solved: Excel VB Macro Question

Hello All,

Background:

I would like to have Key figures entered entered in to "345 Manpower.xls" (User Imput) Take these key figures on this clear daily new spreadsheet and click a macro button that allows the user to input a tab name that the data will copy to in spreadsheet "345 Other.xls" Please see below start to it. I have no problem selecting data and sending it to the new spreadsheet. The problem that I have is I do not know how I will get the macro to request the tab name in the 345 Other.xls spreadsheet when it is run. So all data selected / copied will go to that already existing tab in 345 Other.xls. Any Ideas would be greatly appreciated.

Code:

Sub Transition()
'
' Copy from Manpower to Transition Macro
' Created by AAYE on 31-Mar-08
' Keyboard Shortcut: Ctrl+Shift+T
'


    'Open what directory'
    ChDir "C:\Excel"
    'Select workbook to be second one open'
    Workbooks.Open Filename:= _
    "C:\Excel\345 Other.xls", UpdateLinks:=3
    'Change formost window to current workbook'
    Windows("345 Manpower.xls").Activate
    'Select Range'
    'Range can be selected multiple if you wish (Example (O6:O46))'
    Range("O6").Select
    'Copy'
    Selection.Copy
    Windows("345 other.xls").Activate
    Sheets("Sheet2").Select
    'Change formost window to current workbook'
    Range("C3").Select
    'The line below pastes values not formulas'
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    'Anything below this line will not be quoted as I am lazy please see above'
    Windows("345 Manpower.xls").Activate
    Range("O12").Select
    Selection.Copy
    Windows("345 other.xls").Activate
    Range("C4").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Windows("345 Manpower.xls").Activate
    Range("O15").Select
    Selection.Copy
    Windows("345 other.xls").Activate
    Range("C5").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Windows("345 Manpower.xls").Activate
    Range("O19").Select
    Selection.Copy
    Windows("345 other.xls").Activate
    Range("H3").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Windows("345 Manpower.xls").Activate
    Range("O9").Select
    Selection.Copy
    Windows("345 other.xls").Activate
    Range("H4").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    
End Sub

Thanks!

Adam

 

"The problem that I have is I do not know how I will get the macro to request the tab name in the 345 Other.xls spreadsheet when it is run."

Prompting a user for input is easy (note: "Book2.xls" is an example):

x = Application.InputBox("Which tab from Book2.xls do you require?")

With the tab name assigned to variable "x", you can then proceed to do whatever, eg:

Sheets(x).Select

(assuming that Book2 is the active workbook).

But it depends how you want to do this. Unless you give the user a list of valid sheet names to select from, the chances of an invalid value being input for x are fairly high, I'd have thought. We really need an idea of your wb structure to be able to advise properly.

 

Bomb,

You are correct. I did not think of a list box and that is a great idea if possible. However I have no clue where to start with that in VB. I have attached a bland version of both files so that you have data to play with. Any help you can provide is greatly appreciated.

Manpower is the file that contains the daily macro that will populate the tab in Transition when the macro is run. Transition will have number of tabs == to the number of days in that month. Once again what is needed is use macro in manpower and have tab's in Transition prompt to which one we want to write the data too...

Any help is greatly appreciated.

Thanks!

Debian

 

See my attached example. You will need to move your code to copy and paste the values from your module to the button click event on the form. Just open the VB editor and right click the form name and choose "View Code" and paste your code under the button click event. Instead of using a listbox like Bomb suggested I have added a combobox so that the user can either select from the drop down values or manually type the tab name instead. I have included validation so that a message will be given if the user types a value in the combobox that is not valid. Validation has also been included to make sure that the Transition workbook is open when the copy button is clicked. I hope this helps! Be sure to post back if you need more assistance.

Regards,
Rollin

 

Solved !! Thank you


Debian