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.

Excel 2007 Macro Question

Discussion in 'Business Applications' started by Hchuck, Dec 10, 2011.

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

    Hchuck Thread Starter

    Joined:
    Dec 10, 2011
    Messages:
    3
    Hi. My name is Chuck and I am new to the forum. I am working on an Excel 2007 Workbook in which I am saving certain activities to macros. When creating these macros, I am storing the macros in "This Workbook". My ultimate goal is to be able to save this workbook with the macros I have recorded and assigned to buttons within the workbook to a thumb drive and give it to a friend who can then open the file on any machine that has Microsoft Excel 2007 running on it, and use the workbook and have the macros work. However, it appears that when I close the file and reopen it later to continue working on it (on the machine I am creating the file on), it is as if I never even recorded the macros... they are gone. Please help.
     
  2. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    Hi, I don't kwno how you recorded your macro's but it could be that they are stored in your perwonal macro book.

    When you start with recording a new macro it promost you, besides the name you want to give the macro als where you want it saved.

    Default is the pwronal Macro book which is a hidden sheet which is always available.

    The other options is that you did save the workbook but forgot to select the Macro Enabled workbook, if this was the case, all macro's in that workbook will be removed and cannot be found again.
    Uset the Help function, it explains someaht how the personal macrobook works and where it's stored,
     
  3. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    Are you saving your changes to the original workbook and then copying and pasting the file to the thumbdrive or are you using FILE >> SAVE AS and then selecting the thumbdrive as the save path?

    Rollin
     
  4. Hchuck

    Hchuck Thread Starter

    Joined:
    Dec 10, 2011
    Messages:
    3
    Rollin - I was using FILE >> SAVE AS and then selecting the thumbdrive as the save path. I ended up saving the file to the Personal Macro Workbook and that seems to fix the problem. Thanks for your response and suggestion I appreciate it.
     
  5. Hchuck

    Hchuck Thread Starter

    Joined:
    Dec 10, 2011
    Messages:
    3
    Keebellah - Thanks. The default when I saved the macros was "This Workbook" and every time I would the save and close the workbook, when I reopened it all the macros were gone. Once I tried saving the macros to the Personal Macro Workbook, then everything started being retained no problem. I did try the macro-enabled workbook (.xlsm) thing, but ran into problems with that... I decided to try that before I tried the Personal Macro Workbook option. So, I was able to get it working. I appreciate your response. Thanks so much.
     
  6. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    Don’t forget to mark your own threads solved using the button at the top of the page of the thread in the upper left corner

    :)
     
  7. sumanp

    sumanp

    Joined:
    Jan 12, 2012
    Messages:
    1
    Hi All,
    I am new to macros, using ms excel 2007. I have recorded the codes using macros. Now I need to modify the codes where :
    1. I need to extend the selection criteria for a column, for Eg; my data for a column varies each time( 100 rows data, next time maybe it may dip to 50 or increase to 200 rows) So Macros code should be able to consider this changes for the column and select automatically the range of data it has at the moment for further process.
    This is the part from where I wanted to have a help. I appreciate if you can help me with this, really appreciate to have patience and also Sorry if the code i pasted is too long.Accept my apologies!

    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-11],PR4.XLS!C3,1,0)"
    Range("P2").Select
    Selection.Copy
    Range("M2").Select
    Selection.End(xlDown).Select
    Range("P175").Select
    Range(Selection, Selection.End(xlUp)).Select
    Range("P3:p175").Select
    Range("P175").Activate
    ActiveSheet.Paste
    Selection.End(xlUp).Select
    Columns("P:p").EntireColumn.AutoFit
    Range("P5").Select
    Selection.End(xlDown).Select
    Range("O175").Select
    Selection.End(xlUp).Select
    Range("N2").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = _
    "=VLOOKUP(RC[-11],'[All input data.xlsx]CP4 sales Org'!C2:C3,2,0)"
    Range("N2").Select
    Selection.Copy
    Range("M2").Select
    Selection.End(xlDown).Select
    Range("N175").Select
    Range(Selection, Selection.End(xlUp)).Select
    Range("N3:N175").Select
    Range("N175").Activate
    ActiveSheet.Paste
    Selection.End(xlUp).Select
    Range("O2").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=RC[-11]"
    Range("O2").Select
    Selection.Copy
    Range("N2").Select
    Selection.End(xlDown).Select
    Range("O175").Select
    Range(Selection, Selection.End(xlUp)).Select
    Range("O3:O175").Select
    Range("O175").Activate
    ActiveSheet.Paste
    Selection.End(xlUp).Select
    Range("P1").Select
    Application.CutCopyMode = False
    Selection.AutoFilter
    Range("P1").Select
    ActiveSheet.Range("$A$1:$P$175").AutoFilter Field:=16, Criteria1:="#N/A"
    Range("P21").Select
    Selection.End(xlToLeft).Select
    Range("E1").Select
    Columns("E:E").EntireColumn.AutoFit
    Range("E21:E56").Select
    Selection.Copy
    Sheets("Sheet2").Select
    Range("A2").Select
    ActiveSheet.Paste
    Range("A1").Select
    Sheets("Sheet1").Select
    Range("E1").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Sheet2").Select
    ActiveSheet.Paste
    Cells.Select
    Cells.EntireColumn.AutoFit
    Range("A2:A4").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Final Output.xlsx").Activate
    Range("C2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Windows("PR4.XLS").Activate
    Windows("Final Output.xlsx").Activate
    Sheets("User Update Vai Mail").Select
    Range("A2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Sheets("Sharepoint Update").Select
    Columns("C:C").EntireColumn.AutoFit
    Range("C2").Select
    Sheets("User Update Vai Mail").Select
    Range("B2").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],[CP4.XML]Sheet1!C5:C15,11,0)"
    Range("B2").Select
    Selection.Copy
    Range("A2").Select
    Selection.End(xlDown).Select
    Range("B4").Select
    Range(Selection, Selection.End(xlUp)).Select
    Range("B3:B4").Select
    Range("B4").Activate
    ActiveSheet.Paste
    Range("C2").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-2],[CP4.XML]Sheet1!C5:C6,2,0)"
    Range("C2").Select
    Selection.Copy
    Range("B2").Select
    Selection.End(xlDown).Select
    Range("C4").Select
    Range(Selection, Selection.End(xlUp)).Select
    Range("C3:C4").Select
    Range("C4").Activate
    ActiveSheet.Paste
    Range("I2").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-8],[CP4.XML]Sheet1!C5:C14,10,0)"
    Range("I2").Select
    Selection.Copy
    Range("I3:I4").Select
    ActiveSheet.Paste
    Range("J2").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=vlookup(A2,[CP4.XML]Sheet1!$E:$M,9,0)"
    Range("J2").Select
    Selection.TextToColumns Destination:=Range("J2"), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
    Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
    :=Array(1, 1), TrailingMinusNumbers:=True
    Selection.Copy
    Range("I2").Select
    Selection.End(xlDown).Select
    Range("J3:J4").Select
    Range("J4").Activate
    ActiveSheet.Paste
    Range("K2").Select
    Application.CutCopyMode = False
    Range("J2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.ClearContents
    ActiveCell.FormulaR1C1 = "=vlookup(A2,[CP4.XML]Sheet1!$E:$L,8,0)"
    Range("J2").Select
    Selection.TextToColumns Destination:=Range("J2"), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
    Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
    :=Array(1, 1), TrailingMinusNumbers:=True
    Range("J2").Select
    Selection.Copy
    Range("I2").Select
    Selection.End(xlDown).Select
    Range("J3:J4").Select
    Range("J4").Activate
    ActiveSheet.Paste
    Range("K2").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=vlookup(A2,[CP4.XML]Sheet1!$E:$M,9,0)"
    Range("K2").Select
    Selection.TextToColumns Destination:=Range("K2"), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
    Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
    :=Array(1, 1), TrailingMinusNumbers:=True
    Selection.Copy
    Range("J2").Select
    Selection.End(xlDown).Select
    Range("K3:K4").Select
    Range("K4").Activate
    ActiveSheet.Paste
    Range("K3").Select
    Application.CutCopyMode = False
    Range("L2").Select
    ActiveCell.FormulaR1C1 = _
    "=VLOOKUP(RC[-3],'[All input data.xlsx]CP4 sales Org'!C3:C6,4,0)"
    Range("L2").Select
    Selection.Copy
    Range("K2").Select
    Selection.End(xlDown).Select
    Range("L3:L4").Select
    Range("L4").Activate
    ActiveSheet.Paste
    Selection.End(xlUp).Select
    Range("K1").Select
    Selection.End(xlToLeft).Select
    Cells.Select
    Cells.EntireColumn.AutoFit
    Application.CutCopyMode = False
    Sheets("Sharepoint Update").Select
    Range("D2").Select
    ActiveCell.FormulaR1C1 = "='User Update Vai Mail'!RC[-2]"
    Range("D2").Select
    Selection.Copy
    Range("D3:D4").Select
    ActiveSheet.Paste
    Range("E2").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "='User Update Vai Mail'!RC[-2]"
    Range("E2").Select
    Selection.Copy
    Range("E3:E4").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range("F2").Select
    ActiveSheet.Next.Select
    ActiveSheet.Previous.Select
    ActiveCell.FormulaR1C1 = "='User Update Vai Mail'!RC[3]"
    Range("F2").Select
    Selection.Copy
    Range("E2").Select
    Selection.End(xlDown).Select
    Range("F3:F4").Select
    Range("F4").Activate
    ActiveSheet.Paste
    Range("G2").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "='User Update Vai Mail'!J2"
    Range("G2").Select
    Selection.TextToColumns Destination:=Range("G2"), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
    Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
    :=Array(1, 1), TrailingMinusNumbers:=True
    Range("G2").Select
    Selection.Copy
    Range("G3:G4").Select
    Range("G4").Activate
    ActiveSheet.Paste
    Range("H2").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "='User Update Vai Mail'!K2"
    Range("H2").Select
    Selection.TextToColumns Destination:=Range("H2"), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
    Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
    :=Array(1, 1), TrailingMinusNumbers:=True
    Range("H2").Select
    Selection.Copy
    Range("H3:H4").Select
    Range("H4").Activate
    ActiveSheet.Paste
    Range("I2").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "='User Update Vai Mail'!RC[3]"
    Range("I2").Select
    Selection.Copy
    Application.CutCopyMode = False
    Selection.Copy
    Range("I3:I4").Select
    Range("I4").Activate
    ActiveSheet.Paste
    Columns("I:I").EntireColumn.AutoFit
    Range("J2").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "Suman Pattnaik"
    Range("J2:J4").Select
    Selection.FillDown
    Range("K2").Select
    ActiveCell.FormulaR1C1 = "11-Jan-2012"
    Range("K2:K4").Select
    Selection.FillDown
    Range("B2").Select
    ActiveCell.FormulaR1C1 = "CP4"
    Range("B2:B4").Select
    Selection.FillDown
    Range("A2").Select
    ActiveCell.FormulaR1C1 = "2"
    Range("A3").Select
    ActiveCell.FormulaR1C1 = "2"
    Range("A4").Select
    ActiveCell.FormulaR1C1 = "2"
    Range("A4").Select
    Selection.End(xlUp).Select
    Range("B2").Select
    ActiveSheet.Next.Select
    ActiveSheet.Previous.Select




    2. At the end I want to integrate final output with my system MS Outlook to create a new mail with To, cc, Subject, matter filled with specific data.How can I do that?

    My sincere thanks for your help.


    Best wishes and regards,
    Suman P
     
  8. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    Hi Suman,
    Welcome to the forum.

    I suggest you start your own post with this question and past the code in a file, makes it easier to understand
     
  9. 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/1030644

  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