Page 3 of 7 FirstFirst 12345 ... LastLast
Results 21 to 30 of 63

Thread: copying cells from sheet 1 to various other sheets

  1. #21
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,228
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

    Default Re: copying cells from sheet 1 to various other sheets

    What was the error message?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  2. #22
    Board Regular
    Join Date
    Aug 2019
    Posts
    80
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: copying cells from sheet 1 to various other sheets

    run time error 1004
    application-defined or object defined error

    also the sheet was empty, no data was copied across

  3. #23
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,228
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

    Default Re: copying cells from sheet 1 to various other sheets

    That suggests that is no data in Col D on "Sheet1"
    Did you make sure that the sheet name I highlighted in red is looking at the correct sheet?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  4. #24
    Board Regular
    Join Date
    Aug 2019
    Posts
    80
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: copying cells from sheet 1 to various other sheets

    Sheet 1 is the name and column D has all the company names

    Bd
    Bd
    Cannon
    LY
    Cannon
    Bd
    Cannon
    TSP
    NHDC
    Bd
    Bd
    NHDC
    NHDC
    Bd
    Bd
    Bd
    Bd
    Bd
    LY
    Bd
    Cannon
    Bd
    Bd
    Cannon
    Bd

  5. #25
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,228
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

    Default Re: copying cells from sheet 1 to various other sheets

    Does the tab show "Sheet1" or "Sheet 1"?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  6. #26
    Board Regular
    Join Date
    Aug 2019
    Posts
    80
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: copying cells from sheet 1 to various other sheets

    The original tab says Sheet1, it then creates a Sheet2 but is empty.

  7. #27
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,228
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

    Default Re: copying cells from sheet 1 to various other sheets

    Do you have blank cells in col D?
    If so try
    Code:
    Sub sbrown64()
       Dim Cl As Range
       Dim Ky As Variant
       Dim Ws As Worksheet
       
       Set Ws = Sheets("sheet1")
       With CreateObject("Scripting.dictionary")
          .CompareMode = vbTextCompare
          For Each Cl In Ws.Range("D2", Ws.Range("D" & Rows.Count).End(xlUp))
             If Cl.Value <> "" Then .item(Cl.Value) = Empty
          Next Cl
          For Each Ky In .Keys
             Sheets.Add(, Sheets(1)).Name = Ky
             Ws.Range("A1:D1").AutoFilter 4, Ky
             Ws.AutoFilter.Range.EntireRow.Copy Sheets(Ky).Range("A1")
          Next Ky
       End With
       Ws.AutoFilterMode = False
    End Sub
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  8. #28
    Board Regular
    Join Date
    Aug 2019
    Posts
    80
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: copying cells from sheet 1 to various other sheets

    run time error 1004

    cannot rename a sheet to the same name as another sheet, a referenced object library or a workbook referenced in VB.

  9. #29
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,228
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

    Default Re: copying cells from sheet 1 to various other sheets

    You said the other sheets didn't exist.
    What do you want to happen if the other sheets already exist?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  10. #30
    Board Regular
    Join Date
    Aug 2019
    Posts
    80
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: copying cells from sheet 1 to various other sheets

    Hi

    I have just deleted all the pages except the main page and re-run the code and it has created a page for all companies, however without any headers, it has created a separate page called Company with headers. If i then run the code a second time it comes up with the error message and creates a blank page.

    How do i run it more than once to update the pages as more data is entered?

    update
    just realised one of the companies has had two pages created with the same data?
    Last edited by sbrown64; Aug 24th, 2019 at 09:09 AM. Reason: update

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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