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.
Thanks!
Adam
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