Excel 2007 Macro Question

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

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.
 

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,641
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,
 
Joined
Sep 4, 2003
Messages
4,916
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
 

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.
 

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.
 

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,641
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

:)
 
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
 

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,641
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
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

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 807,865 other people just like you!

Latest posts

Top