+ Reply to Thread
Results 1 to 6 of 6

macro to Copy Header and name to shet name

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    07-12-2018
    Location
    South Africa
    MS-Off Ver
    Office 2021
    Posts
    2,767

    macro to Copy Header and name to shet name

    i have division names on sheet "Unique branches"

    i have tried to write code to copy the division header and division name to the sheet with the same name in Cells Q1 & Q2 but cannot get it to do this


     Sub copy_BranchNanmestoSheets()
    clear_BranchNames
     Dim I As Long
        With Sheets("Unique Branches").Cells(2)
            .Parent.AutoFilterMode = False
            For I = 5 To Sheets.Count
                .AutoFilter 1, Sheets(I).Name
                .Copy Sheets(I).[q1]
                .AutoFilter
            Next
        End With
    End Sub
    Sub clear_BranchNames()
     Dim I As Long, wks As Worksheet
           For I = 5 To Sheets.Count
              With Worksheets(I).Range("Q1:Q2")
    .ClearContents
     End With
            Next I
       
    End Sub

    It would be appreciated if someone could kindly assist me
    Last edited by Howardc1001; 08-02-2021 at 08:16 AM. Reason: attached File

  2. #2
    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,804

    Re: macro to Copy Header and name to shet name

    Please see the yellow banner at the top of the page.
    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


  3. #3
    Forum Contributor
    Join Date
    07-12-2018
    Location
    South Africa
    MS-Off Ver
    Office 2021
    Posts
    2,767

    Re: macro to Copy Header and name to shet name

    My apologies -Forgot to upload attachment

  4. #4
    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,804

    Re: macro to Copy Header and name to shet name

    This seems to get the data copied across. Can't help feeling there's a better way, but ...

    Sub copy_BranchNanmestoSheets()
    clear_BranchNames
    Dim I As Long
    With Sheets("Unique Branches").Cells(2)
        .Parent.AutoFilterMode = False
        For I = 5 To Sheets.Count
            .AutoFilter 1, Sheets(I).Name
            .Resize(Application.WorksheetFunction.CountA(Columns(2))).SpecialCells(xlCellTypeVisible).Copy
            Sheets(I).Range("Q1").PasteSpecial xlPasteAll
            Application.CutCopyMode = False
            .AutoFilter
        Next 'I
    End With
    End Sub
    Sub clear_BranchNames()
    Dim I As Long, wks As Worksheet
    For I = 5 To Sheets.Count
        With Worksheets(I).Range("Q1:Q2")
            .ClearContents
        End With
    Next I
    
    End Sub

  5. #5
    Forum Contributor
    Join Date
    07-12-2018
    Location
    South Africa
    MS-Off Ver
    Office 2021
    Posts
    2,767

    Re: macro to Copy Header and name to shet name

    Thanks for the help

  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,804

    Re: macro to Copy Header and name to shet name

    You're welcome.

+ 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. [SOLVED] Copy data from an entry shet onto a master sheet
    By khanaran in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 05-11-2020, 08:58 AM
  2. Modify Macro to Run on Protected Shet
    By psanghvi in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 02-10-2012, 04:12 AM
  3. Modify Macro to Run on Protected Shet
    By psanghvi in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-09-2012, 07:08 AM
  4. copy data from shet to another based on date
    By kontti in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-04-2011, 07:38 AM
  5. Macro tweak to ignore header row. +macro to copy from one sheet to another
    By rain4u in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 04-16-2011, 03:21 PM
  6. [SOLVED] Macro to Copy Grid Header
    By Jason Heider in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-22-2006, 08:40 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