| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
help with EXCEL SCRIPT
hi, trying to get help with script for copying 4 data
column sections to backup / history positions. 1 col: DU to DT 22 col (main, 21 col back up), COPY: EE - EY, Paste-Special-Values to right 1 col: EF - EZ double columns (10 sets of 2), COPY: FE - FV, Paste-Special-Values to right 2 cols: FG - FX double columns (1 set of 2), COPY: EC - ED, Paste-Special-Values to: FE - FF the following is a copy of the script currently in use. Option Explicit Private Sub Worksheet_Change(ByVal Target As Excel.Range) With Target If .Count 1 Then Exit Sub If Target.Row < 130 Then Exit Sub If Me.Cells(.Row, "A").Value = "." Then Exit Sub 'add "+" to blank spaces col A: If Not Intersect(Me.Range("a:a"), .Cells) Is Nothing Then Application.EnableEvents = False .Value = Replace(.Value, " ", "+") Application.EnableEvents = True End If 'make column changes: If Not Intersect(Me.Range("CK:CO"), .Cells) Is Nothing Then Application.EnableEvents = False 'Destination: With Me.Cells(.Row, "CF") .NumberFormat = "dd" .Value = Now End With Application.EnableEvents = True End If 'make column changes: If Not Intersect(Me.Range("CW:CW"), .Cells) Is Nothing Then Application.EnableEvents = False 'Destination With Me.Cells(.Row, "CG") .NumberFormat = "dd" .Value = Now End With Application.EnableEvents = True End If End With End Sub |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
help with EXCEL SCRIPT
What is the help required?
-- Always provide your feedback so that others know whether the solution worked or problem still persists ... "Nastech" wrote: hi, trying to get help with script for copying 4 data column sections to backup / history positions. 1 col: DU to DT 22 col (main, 21 col back up), COPY: EE - EY, Paste-Special-Values to right 1 col: EF - EZ double columns (10 sets of 2), COPY: FE - FV, Paste-Special-Values to right 2 cols: FG - FX double columns (1 set of 2), COPY: EC - ED, Paste-Special-Values to: FE - FF the following is a copy of the script currently in use. Option Explicit Private Sub Worksheet_Change(ByVal Target As Excel.Range) With Target If .Count 1 Then Exit Sub If Target.Row < 130 Then Exit Sub If Me.Cells(.Row, "A").Value = "." Then Exit Sub 'add "+" to blank spaces col A: If Not Intersect(Me.Range("a:a"), .Cells) Is Nothing Then Application.EnableEvents = False .Value = Replace(.Value, " ", "+") Application.EnableEvents = True End If 'make column changes: If Not Intersect(Me.Range("CK:CO"), .Cells) Is Nothing Then Application.EnableEvents = False 'Destination: With Me.Cells(.Row, "CF") .NumberFormat = "dd" .Value = Now End With Application.EnableEvents = True End If 'make column changes: If Not Intersect(Me.Range("CW:CW"), .Cells) Is Nothing Then Application.EnableEvents = False 'Destination With Me.Cells(.Row, "CG") .NumberFormat = "dd" .Value = Now End With Application.EnableEvents = True End If End With End Sub |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
help with EXCEL SCRIPT
hi, I have 4 different positions that I copy paste-values to a different
location, daily, for couple of main uses of reviewing old data/ comparing to today's data.. if using script for moving data is possible.. would try to get a button that would do the task, that is performed once per day. automating would help keep mistakes down but would have a letter in a specific cell to be a guard from hitting the button accidently. the old data is for percent change and average calculations. the column designations are as below. where sheet is somewhat mature from need of add - subtract of columns, wonder if Script could still allow for movement of column locations if need be, but that is of second concern. not sure if anything else you need to know? thanks.. "Sheeloo" wrote: What is the help required? -- Always provide your feedback so that others know whether the solution worked or problem still persists ... "Nastech" wrote: hi, trying to get help with script for copying 4 data column sections to backup / history positions. 1 col: DU to DT 22 col (main, 21 col back up), COPY: EE - EY, Paste-Special-Values to right 1 col: EF - EZ double columns (10 sets of 2), COPY: FE - FV, Paste-Special-Values to right 2 cols: FG - FX double columns (1 set of 2), COPY: EC - ED, Paste-Special-Values to: FE - FF the following is a copy of the script currently in use. Option Explicit Private Sub Worksheet_Change(ByVal Target As Excel.Range) With Target If .Count 1 Then Exit Sub If Target.Row < 130 Then Exit Sub If Me.Cells(.Row, "A").Value = "." Then Exit Sub 'add "+" to blank spaces col A: If Not Intersect(Me.Range("a:a"), .Cells) Is Nothing Then Application.EnableEvents = False .Value = Replace(.Value, " ", "+") Application.EnableEvents = True End If 'make column changes: If Not Intersect(Me.Range("CK:CO"), .Cells) Is Nothing Then Application.EnableEvents = False 'Destination: With Me.Cells(.Row, "CF") .NumberFormat = "dd" .Value = Now End With Application.EnableEvents = True End If 'make column changes: If Not Intersect(Me.Range("CW:CW"), .Cells) Is Nothing Then Application.EnableEvents = False 'Destination With Me.Cells(.Row, "CG") .NumberFormat = "dd" .Value = Now End With Application.EnableEvents = True End If End With End Sub |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
help with EXCEL SCRIPT
Test this out... You can assign it to a button...
It will execute only if A1 contains $.$OK$.$ Sub Macro1() If Range("A1").Value = "$.$OK$.$" Then '1 col: DU to DT Columns("DU:DU").Select Selection.Copy Range("DT").Select ActiveSheet.Paste '22 col (main, 21 col back up), COPY: EE - EY, 'Paste-Special-Values to right 1 col: EF - EZ Columns("EE:EY").Select Selection.Copy Range("EF1").Select ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _ IconFileName:=False 'double columns (10 sets of 2), COPY: FE - FV, 'Paste-Special-Values to right 2 cols: FG - FX Columns("FE:FV").Select Selection.Copy Range("FG").Select ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _ IconFileName:=False 'double columns (1 set of 2), COPY: EC - ED, 'Paste-Special-Values to: FE - FF Columns("EC:ED").Select Selection.Copy Range("FE").Select ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _ IconFileName:=False End If End Sub -- Always provide your feedback so that others know whether the solution worked or problem still persists ... "Nastech" wrote: hi, I have 4 different positions that I copy paste-values to a different location, daily, for couple of main uses of reviewing old data/ comparing to today's data.. if using script for moving data is possible.. would try to get a button that would do the task, that is performed once per day. automating would help keep mistakes down but would have a letter in a specific cell to be a guard from hitting the button accidently. the old data is for percent change and average calculations. the column designations are as below. where sheet is somewhat mature from need of add - subtract of columns, wonder if Script could still allow for movement of column locations if need be, but that is of second concern. not sure if anything else you need to know? thanks.. "Sheeloo" wrote: What is the help required? -- Always provide your feedback so that others know whether the solution worked or problem still persists ... "Nastech" wrote: hi, trying to get help with script for copying 4 data column sections to backup / history positions. 1 col: DU to DT 22 col (main, 21 col back up), COPY: EE - EY, Paste-Special-Values to right 1 col: EF - EZ double columns (10 sets of 2), COPY: FE - FV, Paste-Special-Values to right 2 cols: FG - FX double columns (1 set of 2), COPY: EC - ED, Paste-Special-Values to: FE - FF the following is a copy of the script currently in use. Option Explicit Private Sub Worksheet_Change(ByVal Target As Excel.Range) With Target If .Count 1 Then Exit Sub If Target.Row < 130 Then Exit Sub If Me.Cells(.Row, "A").Value = "." Then Exit Sub 'add "+" to blank spaces col A: If Not Intersect(Me.Range("a:a"), .Cells) Is Nothing Then Application.EnableEvents = False .Value = Replace(.Value, " ", "+") Application.EnableEvents = True End If 'make column changes: If Not Intersect(Me.Range("CK:CO"), .Cells) Is Nothing Then Application.EnableEvents = False 'Destination: With Me.Cells(.Row, "CF") .NumberFormat = "dd" .Value = Now End With Application.EnableEvents = True End If 'make column changes: If Not Intersect(Me.Range("CW:CW"), .Cells) Is Nothing Then Application.EnableEvents = False 'Destination With Me.Cells(.Row, "CG") .NumberFormat = "dd" .Value = Now End With Application.EnableEvents = True End If End With End Sub |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
help with EXCEL SCRIPT
Thanks.., may take awhile for me to make work 1st time, then test;
but wonder if paste function will be VALUES ONLY. thanks. (there is different Standard & Conditional Formatting in all locations) "Sheeloo" wrote: Test this out... You can assign it to a button... It will execute only if A1 contains $.$OK$.$ Sub Macro1() If Range("A1").Value = "$.$OK$.$" Then '1 col: DU to DT Columns("DU:DU").Select Selection.Copy Range("DT").Select ActiveSheet.Paste '22 col (main, 21 col back up), COPY: EE - EY, 'Paste-Special-Values to right 1 col: EF - EZ Columns("EE:EY").Select Selection.Copy Range("EF1").Select ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _ IconFileName:=False 'double columns (10 sets of 2), COPY: FE - FV, 'Paste-Special-Values to right 2 cols: FG - FX Columns("FE:FV").Select Selection.Copy Range("FG").Select ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _ IconFileName:=False 'double columns (1 set of 2), COPY: EC - ED, 'Paste-Special-Values to: FE - FF Columns("EC:ED").Select Selection.Copy Range("FE").Select ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _ IconFileName:=False End If End Sub -- Always provide your feedback so that others know whether the solution worked or problem still persists ... "Nastech" wrote: hi, I have 4 different positions that I copy paste-values to a different location, daily, for couple of main uses of reviewing old data/ comparing to today's data.. if using script for moving data is possible.. would try to get a button that would do the task, that is performed once per day. automating would help keep mistakes down but would have a letter in a specific cell to be a guard from hitting the button accidently. the old data is for percent change and average calculations. the column designations are as below. where sheet is somewhat mature from need of add - subtract of columns, wonder if Script could still allow for movement of column locations if need be, but that is of second concern. not sure if anything else you need to know? thanks.. "Sheeloo" wrote: What is the help required? -- Always provide your feedback so that others know whether the solution worked or problem still persists ... "Nastech" wrote: hi, trying to get help with script for copying 4 data column sections to backup / history positions. 1 col: DU to DT 22 col (main, 21 col back up), COPY: EE - EY, Paste-Special-Values to right 1 col: EF - EZ double columns (10 sets of 2), COPY: FE - FV, Paste-Special-Values to right 2 cols: FG - FX double columns (1 set of 2), COPY: EC - ED, Paste-Special-Values to: FE - FF the following is a copy of the script currently in use. Option Explicit Private Sub Worksheet_Change(ByVal Target As Excel.Range) With Target If .Count 1 Then Exit Sub If Target.Row < 130 Then Exit Sub If Me.Cells(.Row, "A").Value = "." Then Exit Sub 'add "+" to blank spaces col A: If Not Intersect(Me.Range("a:a"), .Cells) Is Nothing Then Application.EnableEvents = False .Value = Replace(.Value, " ", "+") Application.EnableEvents = True End If 'make column changes: If Not Intersect(Me.Range("CK:CO"), .Cells) Is Nothing Then Application.EnableEvents = False 'Destination: With Me.Cells(.Row, "CF") .NumberFormat = "dd" .Value = Now End With Application.EnableEvents = True End If 'make column changes: If Not Intersect(Me.Range("CW:CW"), .Cells) Is Nothing Then Application.EnableEvents = False 'Destination With Me.Cells(.Row, "CG") .NumberFormat = "dd" .Value = Now End With Application.EnableEvents = True End If End With End Sub |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
help with EXCEL SCRIPT
In one set you had NOT mentioned paste special so I took that out. You can
add it just like in other sets... Test one set at a time... :-) "Nastech" wrote: Thanks.., may take awhile for me to make work 1st time, then test; but wonder if paste function will be VALUES ONLY. thanks. (there is different Standard & Conditional Formatting in all locations) "Sheeloo" wrote: Test this out... You can assign it to a button... It will execute only if A1 contains $.$OK$.$ Sub Macro1() If Range("A1").Value = "$.$OK$.$" Then '1 col: DU to DT Columns("DU:DU").Select Selection.Copy Range("DT").Select ActiveSheet.Paste '22 col (main, 21 col back up), COPY: EE - EY, 'Paste-Special-Values to right 1 col: EF - EZ Columns("EE:EY").Select Selection.Copy Range("EF1").Select ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _ IconFileName:=False 'double columns (10 sets of 2), COPY: FE - FV, 'Paste-Special-Values to right 2 cols: FG - FX Columns("FE:FV").Select Selection.Copy Range("FG").Select ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _ IconFileName:=False 'double columns (1 set of 2), COPY: EC - ED, 'Paste-Special-Values to: FE - FF Columns("EC:ED").Select Selection.Copy Range("FE").Select ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _ IconFileName:=False End If End Sub -- Always provide your feedback so that others know whether the solution worked or problem still persists ... "Nastech" wrote: hi, I have 4 different positions that I copy paste-values to a different location, daily, for couple of main uses of reviewing old data/ comparing to today's data.. if using script for moving data is possible.. would try to get a button that would do the task, that is performed once per day. automating would help keep mistakes down but would have a letter in a specific cell to be a guard from hitting the button accidently. the old data is for percent change and average calculations. the column designations are as below. where sheet is somewhat mature from need of add - subtract of columns, wonder if Script could still allow for movement of column locations if need be, but that is of second concern. not sure if anything else you need to know? thanks.. "Sheeloo" wrote: What is the help required? -- Always provide your feedback so that others know whether the solution worked or problem still persists ... "Nastech" wrote: hi, trying to get help with script for copying 4 data column sections to backup / history positions. 1 col: DU to DT 22 col (main, 21 col back up), COPY: EE - EY, Paste-Special-Values to right 1 col: EF - EZ double columns (10 sets of 2), COPY: FE - FV, Paste-Special-Values to right 2 cols: FG - FX double columns (1 set of 2), COPY: EC - ED, Paste-Special-Values to: FE - FF the following is a copy of the script currently in use. Option Explicit Private Sub Worksheet_Change(ByVal Target As Excel.Range) With Target If .Count 1 Then Exit Sub If Target.Row < 130 Then Exit Sub If Me.Cells(.Row, "A").Value = "." Then Exit Sub 'add "+" to blank spaces col A: If Not Intersect(Me.Range("a:a"), .Cells) Is Nothing Then Application.EnableEvents = False .Value = Replace(.Value, " ", "+") Application.EnableEvents = True End If 'make column changes: If Not Intersect(Me.Range("CK:CO"), .Cells) Is Nothing Then Application.EnableEvents = False 'Destination: With Me.Cells(.Row, "CF") .NumberFormat = "dd" .Value = Now End With Application.EnableEvents = True End If 'make column changes: If Not Intersect(Me.Range("CW:CW"), .Cells) Is Nothing Then Application.EnableEvents = False 'Destination With Me.Cells(.Row, "CG") .NumberFormat = "dd" .Value = Now End With Application.EnableEvents = True End If End With End Sub |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
help with EXCEL SCRIPT
since once per day.. don't know if important, but not concerned with how it
is done.. speed not important, if would need to be 1 step at a time anyways? is fine.. if possible would like double guard: Cell: $DN$6="z", if possible: (automatic) adjust of Columns & Cell: $DN$6 if other columns / items are moved. "Sheeloo" wrote: What is the help required? -- Always provide your feedback so that others know whether the solution worked or problem still persists ... "Nastech" wrote: hi, trying to get help with script for copying 4 data column sections to backup / history positions. 1 col: DU to DT 22 col (main, 21 col back up), COPY: EE - EY, Paste-Special-Values to right 1 col: EF - EZ double columns (10 sets of 2), COPY: FE - FV, Paste-Special-Values to right 2 cols: FG - FX double columns (1 set of 2), COPY: EC - ED, Paste-Special-Values to: FE - FF the following is a copy of the script currently in use. Option Explicit Private Sub Worksheet_Change(ByVal Target As Excel.Range) With Target If .Count 1 Then Exit Sub If Target.Row < 130 Then Exit Sub If Me.Cells(.Row, "A").Value = "." Then Exit Sub 'add "+" to blank spaces col A: If Not Intersect(Me.Range("a:a"), .Cells) Is Nothing Then Application.EnableEvents = False .Value = Replace(.Value, " ", "+") Application.EnableEvents = True End If 'make column changes: If Not Intersect(Me.Range("CK:CO"), .Cells) Is Nothing Then Application.EnableEvents = False 'Destination: With Me.Cells(.Row, "CF") .NumberFormat = "dd" .Value = Now End With Application.EnableEvents = True End If 'make column changes: If Not Intersect(Me.Range("CW:CW"), .Cells) Is Nothing Then Application.EnableEvents = False 'Destination With Me.Cells(.Row, "CG") .NumberFormat = "dd" .Value = Now End With Application.EnableEvents = True End If End With End Sub |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
help with EXCEL SCRIPT
hi, is there a way to modify the reference to columns,
to be from a different (single) cell, such as INDIRECT.. within a macro / script? Thanks the type of lines I want to reference a Range("A1").Value Columns("B:C").Select Range("D:E").Select Intersect(Me.Range("F:G"), With Me.Cells(.Row, "H") |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| help with EXCEL SCRIPT | Excel Discussion (Misc queries) | |||
| Excel Script for Drop Down Menu | Excel Discussion (Misc queries) | |||
| command line Excel Script | Excel Discussion (Misc queries) | |||
| VB Script in Excel to copy a row | Excel Worksheet Functions | |||
| Excel Graph script | Charts and Charting in Excel | |||
Hybrid Mode
