+ Reply to Thread
Results 1 to 6 of 6

changing cell properties in columns in each worksheet, only first ws is changed

  1. #1
    Forum Contributor
    Join Date
    02-27-2020
    Location
    Rotterdam, The Netherlands
    MS-Off Ver
    2013
    Posts
    425

    changing cell properties in columns in each worksheet, only first ws is changed

    Hi,

    This macro should change cell properties in the columns of each worksheet, but somehow it only changes cell properties in the first worksheet.
    How to overcome this?
    [

    CODE]Sub OpmaakKolommenTest()

    Dim Werkblad As Worksheet

    On Error Resume Next
    For Each Werkblad In ActiveWorkbook.Worksheets

    Columns("A:A").Select
    Selection.NumberFormat = "000000000"
    Columns("C:C").Select
    Selection.NumberFormat = "00000000"
    Columns("E:F").Select
    Selection.NumberFormat = "dd/mm/yyyy"

    Next Werkblad
    End Sub[/CODE]

  2. #2
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,612

    Re: changing cell properties in columns in each worksheet, only first ws is changed

    Please Login or Register  to view this content.
    Ben Van Johnson

  3. #3
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,461

    Re: changing cell properties in columns in each worksheet, only first ws is changed

    Think that needs to be:

    Please Login or Register  to view this content.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  4. #4
    Forum Contributor
    Join Date
    02-27-2020
    Location
    Rotterdam, The Netherlands
    MS-Off Ver
    2013
    Posts
    425

    Re: changing cell properties in columns in each worksheet, only first ws is changed

    Thank you for your input.
    Somehow the cell properties are only adjusted for the sheet i have opened the moment i run the macro. I would like to apply the adjustments for all tabs. How can i do this?

  5. #5
    Forum Contributor
    Join Date
    02-27-2020
    Location
    Rotterdam, The Netherlands
    MS-Off Ver
    2013
    Posts
    425

    Re: changing cell properties in columns in each worksheet, only first ws is changed

    I solved it by adding .select in the With block.

  6. #6
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,461

    Re: changing cell properties in columns in each worksheet, only first ws is changed

    Did you see post #3? You do NOT need to select the sheets.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Changing Columns/Series in Pivot Tables when Filter is changed
    By mdp22 in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 09-18-2018, 01:49 PM
  2. Changing Columns/Series in Pivot Tables when Filter is changed
    By mdp22 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-17-2018, 09:05 AM
  3. Changing multiple cell properties, a little advice needed!
    By DanielPodo in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-20-2015, 08:39 PM
  4. Replies: 0
    Last Post: 06-05-2012, 03:26 PM
  5. How to stop cell properties changing.
    By richardlaw in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-28-2009, 04:27 PM
  6. Cell changing it's properties
    By jtpeters in forum Excel - New Users/Basics
    Replies: 0
    Last Post: 12-27-2005, 06:09 PM
  7. [SOLVED] How to stop Excel from changing the cell properties?
    By Ikke in forum Excel General
    Replies: 5
    Last Post: 05-04-2005, 06:06 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1