Page 2 of 7 FirstFirst 1234 ... LastLast
Results 11 to 20 of 63

Thread: copying cells from sheet 1 to various other sheets

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

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

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

    What is the name of the sheet containing your data?
    - 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. #13
    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 is called TEST2

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

    Sorry the workbook is called TEST2
    The sheets at the moment are called Sheet 1, BD and Cannon

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

    if I run this code, the data copies to the Cannon sheet

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

    Sub Copy_Cannon()
    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 = "Cannon" Then
    Rows(i).Copy Destination:=Sheets(3).Rows(Lastrowa)
    Lastrowa = Lastrowa + 1
    End If
    Next
    Cells(1, 1).Select
    Application.ScreenUpdating = True
    End Sub

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

    hello again

    I have just copied the code underneath the original code and changed Cannon to Bd and it seams to work?

    Sub Copy_Cannon()
    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 = "Cannon" Then
    Rows(i).Copy Destination:=Sheets(3).Rows(Lastrowa)
    Lastrowa = Lastrowa + 1
    End If
    Next
    Cells(1, 1).Select
    Application.ScreenUpdating = True
    End Sub

    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

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

    just noticed that now running it (2 times) the Cannon data was copied across twice but not the Bd, also I only know how to run it from going into the code and clicking the run button

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

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

    How about
    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))
             .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
    Value in red must match the name of the sheet with the data.
    Also remove any sheet that are already named with the company name, or you will get further errors.
    - 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. #20
    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

    this line appeared in yellow when i ran it
    Sheets.Add(, Sheets(1)).Name = Ky

    Again no data was copied to the new sheet

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
  •