[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 277
  • Last Modified:

help me with macro in MS excel 2010

I have a file with many sheets. let say evey sheet represent city. New York,Las Vegas and so on
I run my city query in database and copy the result in each appropriate  sheet. In every sheet  i have two columns (J and M) with numbers and should be formated with 2 decimal places and include 1000 separrator like 1,256.25 and one column should be date MM/dd/yyyy
help me with macro to format J and M as numbers, and A as date
i am including the existing macro
Sub MyFunction()

    Dim ws As Worksheet
    Dim name As String
    
    name = ActiveSheet.name
    
    'To prevent the ugly blinking
    Application.ScreenUpdating = False
    
    For Each ws In Worksheets
        ws.Activate
        ws.Range("A1").Select
' i need your help here

    Next ws
    
   
End Sub

Open in new window

0
rfedorov
Asked:
rfedorov
  • 2
  • 2
2 Solutions
 
StephenJRCommented:
Assume this will work in 2010:
Sub MyFunction()

Dim ws As Worksheet
Dim name As String

name = ActiveSheet.name

'To prevent the ugly blinking
Application.ScreenUpdating = False

For Each ws In Worksheets
    With ws
        .Range("J:J,M:M").NumberFormat = "#,##0.00"
        .Range("A:A").NumberFormat = "mm/dd/yyyy"
    End With
Next ws
   
End Sub

Open in new window

0
 
jppintoCommented:
Try like this.

jppinto
Sub TestFunction()
   
    Application.ScreenUpdating = False
    
    For Each ws In Worksheets
        ws.Select
        Columns("J:J").Select
        Selection.NumberFormat = "#,##0.00"
        Columns("M:M").Select
        Selection.NumberFormat = "#,##0.00"
        
        'your date column here
        Columns("F:F").Select
        Selection.NumberFormat = "mm/dd/yyyy"
    Next ws
    
    Application.ScreenUpdating = True
    
End Sub

Open in new window

0
 
rfedorovAuthor Commented:
to jppinto

Thank you, one little thing
to do the manupulation with formats i need for every sheet but the first one which is sheet called "count"
0
 
StephenJRCommented:
Anything wrong with mine?
0
 
jppintoCommented:
Here you go...

Please also check StephenJR's solution because it also works for what you want and he posted first.

jppinto
Sub TestFunction()
   
    Application.ScreenUpdating = False
    
    For Each ws In Worksheets
        If ws.name <> "count" Then
            ws.Select
            Columns("J:J").Select
            Selection.NumberFormat = "#,##0.00"
            Columns("M:M").Select
            Selection.NumberFormat = "#,##0.00"
            
            'your date column here
            Columns("F:F").Select
            Selection.NumberFormat = "mm/dd/yyyy"
        End If
    Next ws
    
    Application.ScreenUpdating = True
    
End Sub

Open in new window

0

Featured Post

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now