Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VB Macro HELP!
I just made 31 macro and they all work perfectly.
Now my boss asked me to create 5 worksheets (for 5 stores) and make th macro's work on each worksheet individually or worksheet specific. Currently when i try to change the macro in one worksheet, it change the macro for all the worksheets... I need to make these macros worksheet specific.. If I cannot make it worksheet specific, then Can I add an IF statemen that will tell the code that if you're in the 101-JAN04 worksheet, the use all the information from the c:\UDC\101 directory? HELP -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VB Macro HELP!
Any reason for posting substantially the same question 9 minutes later?
"alexm999 " wrote in message ... I just made 31 macro and they all work perfectly. Now my boss asked me to create 5 worksheets (for 5 stores) and make the macro's work on each worksheet individually or worksheet specific. Currently when i try to change the macro in one worksheet, it changes the macro for all the worksheets... I need to make these macros worksheet specific.. If I cannot make it worksheet specific, then Can I add an IF statement that will tell the code that if you're in the 101-JAN04 worksheet, then use all the information from the c:\UDC\101 directory? HELP! --- Message posted from http://www.ExcelForum.com/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VB Macro HELP!
Alex,
There is no difficulty with the principle of what you want to do, but it is light on detail. Post back with some code that doesn't do what you want, and details of what will need changing. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "alexm999 " wrote in message ... I just made 31 macro and they all work perfectly. Now my boss asked me to create 5 worksheets (for 5 stores) and make the macro's work on each worksheet individually or worksheet specific. Currently when i try to change the macro in one worksheet, it changes the macro for all the worksheets... I need to make these macros worksheet specific.. If I cannot make it worksheet specific, then Can I add an IF statement that will tell the code that if you're in the 101-JAN04 worksheet, then use all the information from the c:\UDC\101 directory? HELP! --- Message posted from http://www.ExcelForum.com/ |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VB Macro HELP!
Here's the code... Any ideas?
I have the following directories: C:\UDC\101 C:\UDC\102 C:\UDC\103 C:\UDC\104 C:\UDC\105 the sheets are 101-JAN04,102-JAN04 and so on to 105-JAN04 Sub Macro1() Application.DisplayAlerts = False If ActiveSheet.Name = "101-JAN04" Then Folder = "c:\UDC\101\" End If Workbooks.OpenText Filename:="c:\UDC\101\1.TXT", Origin:=xlWindows StartRow _ :=7, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _ ConsecutiveDelimiter:=True, Tab:=True, Semicolon:=False, Comma:=False _ Space:=True, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1) Array(3 _ , 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1)) If Range("a:a").Find(what:="DEV") Is Nothing Then Range("a16").EntireRow.Insert shift:=xlDown End If Windows("1.txt").Activate Range("E62").Select Selection.Copy Windows("DAILY OPERATIONS_2004.xls").Activate Range("AL9").Select ActiveSheet.Paste Range("AM9").Select Windows("1.txt").Activate Range("I62").Select Application.CutCopyMode = False Selection.Copy Windows("DAILY OPERATIONS_2004.xls").Activate ActiveSheet.Paste Windows("1.txt").Activate Range("F13").Select Selection.Copy Windows("DAILY OPERATIONS_2004.xls").Activate Range("C9").Select ActiveSheet.Paste Range("E9").Select Windows("1.TXT").Activate Range("F14").Select Application.CutCopyMode = False Selection.Copy Windows("DAILY OPERATIONS_2004.xls").Activate ActiveSheet.Paste Range("J9").Select Windows("1.TXT").Activate Range("E17").Select Application.CutCopyMode = False Selection.Copy Windows("DAILY OPERATIONS_2004.xls").Activate ActiveSheet.Paste Range("K9").Select Windows("1.TXT").Activate Range("G18").Select Application.CutCopyMode = False Selection.Copy Windows("DAILY OPERATIONS_2004.xls").Activate ActiveSheet.Paste ActiveWindow.LargeScroll ToRight:=2 Range("AI9").Select Windows("1.TXT").Activate Range("F18").Select Application.CutCopyMode = False Selection.Copy Windows("DAILY OPERATIONS_2004.xls").Activate ActiveSheet.Paste Windows("1.TXT").Activate ActiveWindow.Close ActiveWindow.LargeScroll ToRight:=-2 ActiveWindow.ScrollColumn = 1 Application.DisplayAlerts = True End Su -- Message posted from http://www.ExcelForum.com |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VB Macro HELP!
Let's close this duplicate thread and stick to the other.
-- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "alexm999 " wrote in message ... Here's the code... Any ideas? I have the following directories: C:\UDC\101 C:\UDC\102 C:\UDC\103 C:\UDC\104 C:\UDC\105 the sheets are 101-JAN04,102-JAN04 and so on to 105-JAN04 Sub Macro1() Application.DisplayAlerts = False If ActiveSheet.Name = "101-JAN04" Then Folder = "c:\UDC\101\" End If Workbooks.OpenText Filename:="c:\UDC\101\1.TXT", Origin:=xlWindows, StartRow _ :=7, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _ ConsecutiveDelimiter:=True, Tab:=True, Semicolon:=False, Comma:=False, _ Space:=True, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3 _ , 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1)) If Range("a:a").Find(what:="DEV") Is Nothing Then Range("a16").EntireRow.Insert shift:=xlDown End If Windows("1.txt").Activate Range("E62").Select Selection.Copy Windows("DAILY OPERATIONS_2004.xls").Activate Range("AL9").Select ActiveSheet.Paste Range("AM9").Select Windows("1.txt").Activate Range("I62").Select Application.CutCopyMode = False Selection.Copy Windows("DAILY OPERATIONS_2004.xls").Activate ActiveSheet.Paste Windows("1.txt").Activate Range("F13").Select Selection.Copy Windows("DAILY OPERATIONS_2004.xls").Activate Range("C9").Select ActiveSheet.Paste Range("E9").Select Windows("1.TXT").Activate Range("F14").Select Application.CutCopyMode = False Selection.Copy Windows("DAILY OPERATIONS_2004.xls").Activate ActiveSheet.Paste Range("J9").Select Windows("1.TXT").Activate Range("E17").Select Application.CutCopyMode = False Selection.Copy Windows("DAILY OPERATIONS_2004.xls").Activate ActiveSheet.Paste Range("K9").Select Windows("1.TXT").Activate Range("G18").Select Application.CutCopyMode = False Selection.Copy Windows("DAILY OPERATIONS_2004.xls").Activate ActiveSheet.Paste ActiveWindow.LargeScroll ToRight:=2 Range("AI9").Select Windows("1.TXT").Activate Range("F18").Select Application.CutCopyMode = False Selection.Copy Windows("DAILY OPERATIONS_2004.xls").Activate ActiveSheet.Paste Windows("1.TXT").Activate ActiveWindow.Close ActiveWindow.LargeScroll ToRight:=-2 ActiveWindow.ScrollColumn = 1 Application.DisplayAlerts = True End Sub --- Message posted from http://www.ExcelForum.com/ |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VB Macro HELP!
Sub Macro1()
Dim rng As Range, sh As Worksheet Dim sh2 As Worksheet, sFile As String Dim Folder As String ' sFile just demo's that you could set other activesheet ' dependent data. Application.DisplayAlerts = False If ActiveSheet.Name = "101-JAN04" Then Folder = "c:\UDC\101\" sFile = "DAILY OPERATIONS_2004.xls" Elseif Activesheet.Name = "102-JAN04" Folder = "c:\UDC\102\" sFile = "DAILY OPERATIONS_2004.xls" Elseif Activesheet.Name = "103-JAN04" Folder = "c:\UDC\103\" sFile = "DAILY OPERATIONS_2004.xls" Elseif Activesheet.Name = "104-JAN04" Folder = "c:\UDC\104\" sFile = "DAILY OPERATIONS_2004.xls" Elseif Activesheet.Nmae = "105-JAN04" Folder = "c:\UDC\105\" sFile = "DAILY OPERATIONS_2004.xls" End If Workbooks.OpenText FileName:=Folder & "1.TXT", _ Origin:=xlWindows, _ StartRow:=7, _ DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, _ ConsecutiveDelimiter:=True, _ Tab:=True, _ Semicolon:=False, _ Comma:=False, _ Space:=True, _ Other:=False, _ FieldInfo:=Array(Array(1, 1), _ Array(2, 1), Array(3, 1), _ Array(4, 1), Array(5, 1), _ Array(6, 1), Array(7, 1), _ Array(8, 1)) Set sh = ActiveSheet Set rng = sh.Range("a:a").Find(what:="DEV") If rng Is Nothing Then sh.Range("a16").EntireRow.Insert shift:=xlDown Exit Sub End If Set sh2 = Windows(sFile).ActiveSheet sh.Range("E62").Copy Destination:=sh2.Range("AL9") sh.Range("I62").Copy Destination:=sh2.Range("AM9") sh.Range("F13").Copy Destination:=sh2.Range("C9") sh.Range("F14").Copy Destination:=sh2.Range("E9") sh.Range("E17").Copy Destination:=sh2.Range("J9") sh.Range("G18").Copy Destination:=sh2.Range("K9") sh.Range("F18").Copy Destination:=sh2.Range("AI9") Workbooks("1.Text").Close Application.DisplayAlerts = True End Sub -- Regards, Tom Ogilvy alexm999 wrote in message ... Here's the code... Any ideas? I have the following directories: C:\UDC\101 C:\UDC\102 C:\UDC\103 C:\UDC\104 C:\UDC\105 the sheets are 101-JAN04,102-JAN04 and so on to 105-JAN04 Sub Macro1() Application.DisplayAlerts = False If ActiveSheet.Name = "101-JAN04" Then Folder = "c:\UDC\101\" End If Workbooks.OpenText Filename:="c:\UDC\101\1.TXT", Origin:=xlWindows, StartRow _ :=7, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _ ConsecutiveDelimiter:=True, Tab:=True, Semicolon:=False, Comma:=False, _ Space:=True, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3 _ , 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1)) If Range("a:a").Find(what:="DEV") Is Nothing Then Range("a16").EntireRow.Insert shift:=xlDown End If Windows("1.txt").Activate Range("E62").Select Selection.Copy Windows("DAILY OPERATIONS_2004.xls").Activate Range("AL9").Select ActiveSheet.Paste Range("AM9").Select Windows("1.txt").Activate Range("I62").Select Application.CutCopyMode = False Selection.Copy Windows("DAILY OPERATIONS_2004.xls").Activate ActiveSheet.Paste Windows("1.txt").Activate Range("F13").Select Selection.Copy Windows("DAILY OPERATIONS_2004.xls").Activate Range("C9").Select ActiveSheet.Paste Range("E9").Select Windows("1.TXT").Activate Range("F14").Select Application.CutCopyMode = False Selection.Copy Windows("DAILY OPERATIONS_2004.xls").Activate ActiveSheet.Paste Range("J9").Select Windows("1.TXT").Activate Range("E17").Select Application.CutCopyMode = False Selection.Copy Windows("DAILY OPERATIONS_2004.xls").Activate ActiveSheet.Paste Range("K9").Select Windows("1.TXT").Activate Range("G18").Select Application.CutCopyMode = False Selection.Copy Windows("DAILY OPERATIONS_2004.xls").Activate ActiveSheet.Paste ActiveWindow.LargeScroll ToRight:=2 Range("AI9").Select Windows("1.TXT").Activate Range("F18").Select Application.CutCopyMode = False Selection.Copy Windows("DAILY OPERATIONS_2004.xls").Activate ActiveSheet.Paste Windows("1.TXT").Activate ActiveWindow.Close ActiveWindow.LargeScroll ToRight:=-2 ActiveWindow.ScrollColumn = 1 Application.DisplayAlerts = True End Sub --- Message posted from http://www.ExcelForum.com/ |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VB Macro HELP!
Sub Macro1()
Dim rng As Range, sh As Worksheet Dim sh2 As Worksheet, sFile As String Dim Folder As String ' sFile just demo's that you could set other activesheet ' dependent data. Application.DisplayAlerts = False If ActiveSheet.Name = "101-JAN04" Then Folder = "c:\UDC\101\" sFile = "DAILY OPERATIONS_2004.xls" Elseif Activesheet.Name = "102-JAN04" Folder = "c:\UDC\102\" sFile = "DAILY OPERATIONS_2004.xls" Elseif Activesheet.Name = "103-JAN04" Folder = "c:\UDC\103\" sFile = "DAILY OPERATIONS_2004.xls" Elseif Activesheet.Name = "104-JAN04" Folder = "c:\UDC\104\" sFile = "DAILY OPERATIONS_2004.xls" Elseif Activesheet.Nmae = "105-JAN04" Folder = "c:\UDC\105\" sFile = "DAILY OPERATIONS_2004.xls" End If Workbooks.OpenText FileName:=Folder & "1.TXT", _ Origin:=xlWindows, _ StartRow:=7, _ DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, _ ConsecutiveDelimiter:=True, _ Tab:=True, _ Semicolon:=False, _ Comma:=False, _ Space:=True, _ Other:=False, _ FieldInfo:=Array(Array(1, 1), _ Array(2, 1), Array(3, 1), _ Array(4, 1), Array(5, 1), _ Array(6, 1), Array(7, 1), _ Array(8, 1)) Set sh = ActiveSheet Set rng = sh.Range("a:a").Find(what:="DEV") If rng Is Nothing Then sh.Range("a16").EntireRow.Insert shift:=xlDown Exit Sub End If Set sh2 = Windows(sFile).ActiveSheet sh.Range("E62").Copy Destination:=sh2.Range("AL9") sh.Range("I62").Copy Destination:=sh2.Range("AM9") sh.Range("F13").Copy Destination:=sh2.Range("C9") sh.Range("F14").Copy Destination:=sh2.Range("E9") sh.Range("E17").Copy Destination:=sh2.Range("J9") sh.Range("G18").Copy Destination:=sh2.Range("K9") sh.Range("F18").Copy Destination:=sh2.Range("AI9") Workbooks("1.Text").Close Application.DisplayAlerts = True End Sub -- Regards, Tom Ogilvy alexm999 wrote in message ... Here's the code... Any ideas? I have the following directories: C:\UDC\101 C:\UDC\102 C:\UDC\103 C:\UDC\104 C:\UDC\105 the sheets are 101-JAN04,102-JAN04 and so on to 105-JAN04 Sub Macro1() Application.DisplayAlerts = False If ActiveSheet.Name = "101-JAN04" Then Folder = "c:\UDC\101\" End If Workbooks.OpenText Filename:="c:\UDC\101\1.TXT", Origin:=xlWindows, StartRow _ :=7, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _ ConsecutiveDelimiter:=True, Tab:=True, Semicolon:=False, Comma:=False, _ Space:=True, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3 _ , 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1)) If Range("a:a").Find(what:="DEV") Is Nothing Then Range("a16").EntireRow.Insert shift:=xlDown End If Windows("1.txt").Activate Range("E62").Select Selection.Copy Windows("DAILY OPERATIONS_2004.xls").Activate Range("AL9").Select ActiveSheet.Paste Range("AM9").Select Windows("1.txt").Activate Range("I62").Select Application.CutCopyMode = False Selection.Copy Windows("DAILY OPERATIONS_2004.xls").Activate ActiveSheet.Paste Windows("1.txt").Activate Range("F13").Select Selection.Copy Windows("DAILY OPERATIONS_2004.xls").Activate Range("C9").Select ActiveSheet.Paste Range("E9").Select Windows("1.TXT").Activate Range("F14").Select Application.CutCopyMode = False Selection.Copy Windows("DAILY OPERATIONS_2004.xls").Activate ActiveSheet.Paste Range("J9").Select Windows("1.TXT").Activate Range("E17").Select Application.CutCopyMode = False Selection.Copy Windows("DAILY OPERATIONS_2004.xls").Activate ActiveSheet.Paste Range("K9").Select Windows("1.TXT").Activate Range("G18").Select Application.CutCopyMode = False Selection.Copy Windows("DAILY OPERATIONS_2004.xls").Activate ActiveSheet.Paste ActiveWindow.LargeScroll ToRight:=2 Range("AI9").Select Windows("1.TXT").Activate Range("F18").Select Application.CutCopyMode = False Selection.Copy Windows("DAILY OPERATIONS_2004.xls").Activate ActiveSheet.Paste Windows("1.TXT").Activate ActiveWindow.Close ActiveWindow.LargeScroll ToRight:=-2 ActiveWindow.ScrollColumn = 1 Application.DisplayAlerts = True End Sub --- Message posted from http://www.ExcelForum.com/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro Help Needed - Excel 2007 - Print Macro with Auto Sort | Excel Worksheet Functions | |||
My excel macro recorder no longer shows up when recording macro | Excel Discussion (Misc queries) | |||
My excel macro recorder no longer shows up when recording macro | Excel Discussion (Misc queries) | |||
Launch Macro in Access via Macro running in Excel??? | Excel Programming |