ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Excel VB Macro HELP! (https://www.excelbanter.com/excel-programming/291358-excel-vbulletin-macro-help.html)

alexm999[_26_]
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

Vasant Nanavati
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/

Bob Phillips[_6_]
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/

alexm999[_31_]
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

Bob Phillips[_6_]
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/

Tom Ogilvy
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/

Tom Ogilvy
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/


All times are GMT +1. The time now is 12:30 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com