Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Copy Data to multiple Worksheet Based on cell values

  1. #1
    Board Regular
    Join Date
    May 2011
    Location
    India
    Posts
    162
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Copy Data to multiple Worksheet Based on cell values

    Hi All,

    I have an workbook. and below sheets contains :-

    1) RAMSES : this is main sheet from where I need to copy only column F and paste in multiple sheets. based on cell value mentioned in column C.

    2) CTA : data needs to paste in this sheet if any cell value of column C in Ramses is mentioned as "CTA"

    3) IDF : data needs to paste in this sheet if any cell value of column C in Ramses is mentioned as "IDF"

    4) MED : data needs to paste in this sheet if any cell value of column C in Ramses is mentioned as "MED"

    5) NOE : data needs to paste in this sheet if any cell value of column C in Ramses is mentioned as "NOE"

    6) SWT : data needs to paste in this sheet if any cell value of column C in Ramses is mentioned as "SWT"

    7) WST : data needs to paste in this sheet if any cell value of column C in Ramses is mentioned as "WST"

    8) BLANK : data needs to paste in this sheet if any cell value of column C in Ramses is mentioned as "#N/A" or any Blank Cell in column C in Ramses.

    I have attached the sample file as well in below link.
    https://1drv.ms/x/s!Ap80Ku6M2Tw5gTbGRwA5Q6nGCIGO

    Thanks in advance for your valuable support.

    Regards,
    Rajender

  2. #2
    Board Regular
    Join Date
    Jul 2008
    Location
    France Alsace
    Posts
    1,206
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copy Data to multiple Worksheet Based on cell values

    It seems there is no header in tabs !!

  3. #3
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    15,316
    Post Thanks / Like
    Mentioned
    272 Post(s)
    Tagged
    22 Thread(s)

    Default Re: Copy Data to multiple Worksheet Based on cell values

    How about
    Code:
    Sub CopyFltr()
       Dim Ary As Variant
       Dim i As Long
       
       Ary = Array("CTA", "IDF", "MED", "NOE", "SWT", "WST", "#N/A")
       
       With Sheets("Ramses")
          If .AutoFilterMode Then .AutoFilterMode = False
          For i = 0 To UBound(Ary) - 1
             .Range("A1:F1").AutoFilter 3, Ary(i)
             .AutoFilter.Range.Columns(6).Copy Sheets(Ary(i)).Range("A1")
          Next i
          .Range("A1:F1").AutoFilter 3, Ary(i), xlOr, ""
          .AutoFilter.Range.Columns(6).Copy Sheets("Blank").Range("A1")
       End With
    End Sub
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 2003 & 2013 on Win 7

  4. #4
    Board Regular
    Join Date
    Jul 2008
    Location
    France Alsace
    Posts
    1,206
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copy Data to multiple Worksheet Based on cell values

    See next code
    To be launched from sheet RAMSES
    Code:
    Option Explicit
    Sub Treat()
    Dim WS  As Worksheet
    Const FR   As Integer = 2
    Const WSCol As String = "C"
    Const WkCol As String = "F"
    Const WgCode1 As String = "#N/A"
    Const WgCode2 As String = ""
    Const WsBlkName As String = "Blank"
    Dim I  As Integer, LR  As Integer
    Dim WsName As String
    
    
         LR = Cells(Rows.Count, "A").End(3).Row
         For I = FR To LR
            If (IsError(Cells(I, WSCol))) Then
                WsName = ""
            Else
                WsName = Cells(I, WSCol).Value
            End If
            If ((WsName <> WgCode1) And (WsName <> WgCode2)) Then
                If (IsSheetExists(WsName)) Then _
                    Cells(I, WkCol).Copy Destination:=Sheets(WsName).Cells(Rows.Count, 1).End(3)(2)
            Else
                Cells(I, WkCol).Copy Destination:=Sheets(WsBlkName).Cells(Rows.Count, 1).End(3)(2)
            End If
         Next I
         MsgBox (" Job Done")
    End Sub
    
    
    Function IsSheetExists(ByVal SheetName As String) As Boolean
        On Error Resume Next
        IsSheetExists = Len(Sheets(SheetName).Name)
        On Error GoTo 0
    End Function
    Last edited by PCL; May 21st, 2018 at 12:01 PM. Reason: Typo

  5. #5
    Board Regular
    Join Date
    May 2011
    Location
    India
    Posts
    162
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copy Data to multiple Worksheet Based on cell values

    Hi Fluff,

    Code is working but output is not 100% correct. 1 point to note is there are no headers in RAMSES sheet. (I will create headers in it if needed).
    Also if in cell C in RAMSES sheet there is not data for any sheet then its showing #REF error in respective output sheet.

    Note : All cell values in column F contains formula.

    Regards,
    Rajender

  6. #6
    Board Regular
    Join Date
    May 2011
    Location
    India
    Posts
    162
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copy Data to multiple Worksheet Based on cell values

    Hi PCL,

    When I am running the code, its showing me compile error.
    Sub or function not defined.

    Please suggest.

    Regards,
    Rajender

  7. #7
    Board Regular
    Join Date
    Jul 2008
    Location
    France Alsace
    Posts
    1,206
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copy Data to multiple Worksheet Based on cell values

    "Sub or function not defined." did you copy the full code included the Function one at the bottom ???
    For which statement is shown such message ??
    Last edited by PCL; May 21st, 2018 at 12:41 PM.

  8. #8
    Board Regular
    Join Date
    May 2011
    Location
    India
    Posts
    162
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copy Data to multiple Worksheet Based on cell values

    Hi PCL,

    I just noticed. I missed to copy that function code.
    I tested and tried. but its giving me #Ref error in respective sheets. My column F contains formula.


    What I will do Create another macro to copy paste column F2 to last row as value in RAMSES sheet , and run your code than.
    Also please suggest, if I need to show my output from column A1 than what adjustment I needed in given code(suppose in RAMSES sheet my data started from row 2)?

    Regards,
    Rajender

  9. #9
    Board Regular
    Join Date
    Jul 2008
    Location
    France Alsace
    Posts
    1,206
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copy Data to multiple Worksheet Based on cell values

    Another run
    Code:
    Option Explicit
    Sub Treat()
    Dim WS  As Worksheet
    Const FR   As Integer = 2
    Const WSCol As String = "C"
    Const WkCol As String = "F"
    Const WgCode1 As String = ""
    Const WsBlkName As String = "Blank"
    Dim i  As Integer, LR  As Integer
    Dim WsName As String
    
    
         LR = Cells(Rows.Count, "A").End(3).Row
         For i = FR To LR
            If (IsError(Cells(i, WSCol))) Then
                WsName = ""
            Else
                WsName = Cells(i, WSCol).Value
            End If
            If (WsName <> WgCode1) Then
                If (IsSheetExists(WsName)) Then _
                    Sheets(WsName).Cells(Rows.Count, 1).End(3)(2) = Cells(i, WkCol).Value
            Else
                Sheets(WsBlkName).Cells(Rows.Count, 1).End(3)(2) = Cells(i, WkCol).Value
            End If
         Next i
         MsgBox (" Job Done")
    End Sub
    
    
    Function IsSheetExists(ByVal SheetName As String) As Boolean
        On Error Resume Next: IsSheetExists = Len(Sheets(SheetName).Name): On Error GoTo 0
    End Function
    Last edited by PCL; May 21st, 2018 at 01:08 PM. Reason: Code Adjusment

  10. #10
    Board Regular
    Join Date
    May 2011
    Location
    India
    Posts
    162
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copy Data to multiple Worksheet Based on cell values

    Hi PCL,

    Thanks a lot. Code is working fine.
    Have a nice day to you .

    Regards,
    Rajender

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
  •