1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Excel VB Macro Question

Discussion in 'Business Applications' started by Debian, Apr 1, 2008.

Thread Status:
Not open for further replies.
Advertisement
  1. Debian

    Debian Thread Starter

    Joined:
    Apr 1, 2008
    Messages:
    4
    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
     
  2. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    "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.
     
  3. Debian

    Debian Thread Starter

    Joined:
    Apr 1, 2008
    Messages:
    4
    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
     

    Attached Files:

  4. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    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
     

    Attached Files:

  5. Debian

    Debian Thread Starter

    Joined:
    Apr 1, 2008
    Messages:
    4
    Solved !! Thank you


    Debian
     
  6. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/699139

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice