Macro Assistance

[Rebellion34]

Hi all,

I am new to VB scrpiting and I am trying to set up a worksheet that will auto Caps certain Columbs and auto date Columb D. I have been succseful in doing this seperately fron eachother but have a name conflict when trying to have both run on the same spreadsheet. I also need to auto date to change only in new cells and not update the previously saved ones, the macro Codes are listed below.also will this code allow the previously endered data in an earlier saved doc to remain constand and not update tio the new date when opened the following day? I welcome any assistance on this.

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A1,B1,C1:A100,B100,C100")) Is Nothing Then Exit Sub
Application.EnableEvents = False
Target = UCase(Target)
Application.EnableEvents = True
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("A2:A100")) Is Nothing Then
With Target(1, 4)
.Value = Date
.EntireColumn.AutoFit
End With
End If
End Sub

Thanks

Reb

 

[SkipVought]

Hi,

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub 

    If Not Intersect(Target, Range("A1,B1,C1:A100,B100,C100")) Is Nothing Then 
      Application.EnableEvents = False
      Target = UCase(Target)
      Application.EnableEvents = True
    end if

    If Not Intersect(Target, Range("A2:A100")) Is Nothing Then
       With Target(1, 4)
          .Value = Date
          .EntireColumn.AutoFit
       End With
    End If
End Sub

Skip,

_{Just traded in my old subtlety...
for a brand NUANCE!}

 

[Rebellion34]

SkipVought,

Many, many thanks I tried that and it worked perfectly I would probably still be groping in the dark years for now trying to fix this.

 

[Rebellion34]

I have 1 futher question I am looking for a code that will allow me to reach a pre destined Cell locaton and then create a new sheet using a copy of sheet 1 which will be a master if you will and have that format roll from sheet 2 to sheet 3 to sheet 4 etc is this possible using VB code? also what is the recommended training manual for VB coding?

Thanks

 

[SkipVought]

Hi,

"...and then create a new sheet using a copy of sheet 1 which will be a master ..."

May I ask why you need to create a new sheet as a copy of sheet1? What is the business case for a new identical sheet?

"what is the recommended training manual for VB coding?"

Everyone learns in different ways. This question has been posted before, so you ought to be able to find at least one thread, probably more. Here are some ways...
[tt]
[li]Macro recorder[/li]
[li]VBA Help[/li]
[li]Forums like Tek-Tips[/li]
[li]Tinker, tinker, tinker[/li]
[/tt]



Skip,

_{Just traded in my old subtlety...
for a brand NUANCE!}

 

[Rebellion34]

Skip,

The document I am working on is a shipment log so I figured if i set up sheet 1 as a master with just the basic info of shipper and date/time received who by etc. I could get to a certain cell # and auto generate a copy of that master to start a fresh sheet instead of physically having to do it and not having 1 sheet that goes on for ages and have too much info to search through when required to do so. As a month to month kind of affair if that makes sense. And negating the requirement of erasing all the info on the new sheet.

Thanks for the advice on the learning aids.

Rob

 

[SkipVought]

"...have too much info to search through when required to do so..."

To the extent that your similar data is chopped up int different sheets, it becomes about as useful as a report in a file drawer. Your data int his fragmented state cannot easily be analysed and reported. If data is worth keeping somewhere, it ought to be maintained in a form that is easily accessible. A bunch of separate sheets is NOT.

So how much is too much? I access millions of rows of data to analyse and report on manufacturing schedule issues and resource loads. That is exactly what Excels features are good at!

Skip,

_{Just traded in my old subtlety...
for a brand NUANCE!}