Page 1 of 7 123 ... LastLast
Results 1 to 10 of 63

Thread: copying cells from sheet 1 to various other sheets

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

    Default copying cells from sheet 1 to various other sheets

    Hi

    I have a spreadsheet which has over 1000 lines, all for varying companies. What I would like to do is create an option so when you run it, it will copy the relevant lines to their respective sheet. The code listed below is some I have seen on here, which works to a point. It will run for one company, bit if I press run again it adds the same ones' to sheet 2. The other point is how to make it do if for ALL companies and not just the one? I hope you can help.

    Regards

    Stephen

    Sub Copy_Bd()
    Application.ScreenUpdating = False
    Dim i As Integer
    Dim Lastrow As Long
    Dim Lastrowa As Long

    Sheets(1).Activate
    Lastrow = Cells(Rows.Count, "F").End(xlUp).Row
    Lastrowa = Sheets(2).Cells(Rows.Count, "F").End(xlUp).Row + 1

    For i = 1 To Lastrow
    If Cells(i, 4).Value = "Bd" Then
    Rows(i).Copy Destination:=Sheets(2).Rows(Lastrowa)
    Lastrowa = Lastrowa + 1
    End If
    Next
    Cells(1, 1).Select
    Application.ScreenUpdating = True
    End Sub

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

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

    Do the sheets have the same name as the values in column D?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  3. #3
    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

    column D is the company name, so it will vary all the time.

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

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

    Yes, but do the sheets the data needs to be copied have the same name as the company?
    So if you had MrExcel in column D, would there be a sheet called MrExcel?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  5. #5
    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

    ideally that would be good

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

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

    Do the sheets for each company 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

  7. #7
    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

    not yet, just sheet 1 and 2

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

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

    Ok, try this
    Code:
    Sub sbrown64()
       Dim Cl As Range
       Dim Ky As Variant
       Dim Ws As Worksheet
       
       Set Ws = Sheets(1)
       With CreateObject("Scripting.dictionary")
          .CompareMode = vbTextCompare
          For Each Cl In Ws.Range("D2", Ws.Range("D" & Rows.Count).End(xlUp))
             .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

  9. #9
    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

    it auto created a sheet 2 and came up with this error message

    Sub sbrown64()
    Dim Cl As Range
    Dim Ky As Variant
    Dim Ws As Worksheet

    Set Ws = Sheets(1)
    With CreateObject("Scripting.dictionary")
    .CompareMode = vbTextCompare
    For Each Cl In Ws.Range("D2", Ws.Range("D" & Rows.Count).End(xlUp))
    .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

    run time error 1004 also no data was copied

  10. #10
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,177
    Post Thanks / Like
    Mentioned
    470 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

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
  •