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 Macroing?

Discussion in 'Business Applications' started by StumpedTechy, Jun 28, 2007.

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

    StumpedTechy Thread Starter

    Joined:
    Jul 7, 2004
    Messages:
    7,235
    Okay I have a hard one - I think.

    I have an excel workbook with with 2 sheets. In sheet 1 it has 4 different places the same number is applied. In the secondary sheet I have put in a large list of numbers. These are in a column going A1, A2, A3 and so fourth.

    Is there any way to put together a macro or something to get the numbers from sheet 2 of this spreadsheet to systematically do the following -

    Take the field from A1 on sheet 2 and apply them to the 4 modifiable fields on sheet 1. Save this sheet 1 as a another file name - presumeably in the filename-A1.xls format. And finally print out a copy of only worksheet 1 (not worksheet 2). Lastly perform this same set of functions for A2, A3 and so on until it has processed all cells populated on sheet 2?

    I have never done scripts/macros with excel before so I kinda need a crash course and direction not just a yes or no answer. Explination will help.

    Basically sheet 1 is a template and sheet 2 is a dynamically changing list depending on the project.
     
  2. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    Should be pretty easy. Can you post a sample workbook?

    Regards,
    Rollin
     
  3. StumpedTechy

    StumpedTechy Thread Starter

    Joined:
    Jul 7, 2004
    Messages:
    7,235
    Sure here is the file -

    I just put in 5 example items in the sheet 2 list but this can go anywhere from a single number to hundreds of numbers. Depends on what we are moving/fixing.

    Thanks for the help.
     

    Attached Files:

  4. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Rollin, it looks like another Access application. ;)

    Let me know if you want me to do this Macro?
     
  5. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    Sorry it took so long to get back to you. Here is some sample code. This assumes that your first row of data on sheet 2 is in row 1. It also assumes there are no blank rows between each record. Just change the portion of the code below that reads C:\Test\ to reflect the directory that you want to save the files in. As you requested each sheet will be saved with the names A1.xls , A2.xls , A3.xls, etc. If you are planning on running the code multiple times on different source files you will need to use a better naming convension since the existing code does not have logic to determine if a file already exists in the directory with the same name before attempting to save the current worksheet.

    Code:
    Public Sub SaveBooks()
    
    For i = 1 To Sheets(2).Cells.Find _
    ("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    
    Sheets(1).Range("A2:C2").Value = Sheets(2).Range("A" & i).Value
    Sheets(1).Range("E1").Value = Sheets(2).Range("A" & i).Value
    
    Sheets(1).Copy
    
    ActiveWorkbook.SaveAs ("C:\Test\A" & i & ".xls")
    
    ActiveWorkbook.PrintOut
    
    ActiveWorkbook.Close
    
    Next i
    
    End Sub
    Regards,
    Rollin
     
  6. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Rollin, I had the impression that StumpedTechy was looking to name the files with whatever was in the Cells a1, a2 etc. :confused: :)

    StumpedTechy, do you have Access?
     
  7. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    You may be right but in the workbook example he posted cell A1 does not appear to be used or contain any type of value. If he does need to use a cells value as part of the filename he can just change the SaveAs command so that it references the cell he wants.

    Code:
    ActiveWorkbook.SaveAs ("C:\Test\" & Range("A1").Value & ".xls")
    Regards,
    Rollin
     
  8. StumpedTechy

    StumpedTechy Thread Starter

    Joined:
    Jul 7, 2004
    Messages:
    7,235
    No I don't have access to a computer with access unless I bug my supervisor for a softwarel icense which then turns into a cost I don't want to incur.

    I'll play around with this code and get back to you if I have questions. Thanks guys.
     
  9. StumpedTechy

    StumpedTechy Thread Starter

    Joined:
    Jul 7, 2004
    Messages:
    7,235
    Okay works like a champ!

    I had to do the following to make it exactly what I wanted -

    ActiveWorkbook.SaveAs ("C:\Test\Phone Template - " & Range("A2").Value & ".xls")

    The thing is the first suggestion was pointed to the cell number I needed the actual extension. On the second one you were refferencing Cell A1 on the newly created workbook and this workbook has nothing in A1 so I put it to A2 and it worked flawlessly.

    Tested it this morning on a 41 extension build out and after my 10 min typing the list it took about 10 seconds to run. (when in all this would have taken me about an hour to do).

    Now the hard part... to find a way to get these cut without manually cutting them all out of the paper :)
     
  10. 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/589720

  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