Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



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

Thread: auto copy specific data from one sheet to other

  1. #1
    Join Date
    4th July 2006
    Posts
    7

    auto copy specific data from one sheet to other

    Hi

    I just want to check that if it is possible to copy specific cells from one row to another sheet by using macro or any other script.

    Attached is a sheet which explains what exectly I want

    The sheet one is having Source data
    Sheet three is the result sheet

    If I put some code numbers (in number form) it should search the data from source sheet and update the same in result sheet in different rows

    Please help
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    13th April 2006
    Location
    Australia
    Posts
    234

    Re: auto copy specific data from one sheet to other

    Record what you can then tweak as need be.
    Below is a recorded macro of what I think your'e asking


    VB:
    Sub Macro2() 
         
        Sheets("results").Select 
        Selection.EntireRow.Insert 
        Sheets("source").Select 
        Rows("2:2").Select 
        Selection.Copy 
        Sheets("results").Select 
        Range("A2").Select 
        Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= _ 
        False, Transpose:=False 
        Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ 
        False, Transpose:=False 
        Range("A2").Select 
    End Sub 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    4th July 2006
    Posts
    7

    Re: auto copy specific data from one sheet to other

    No ... seems not working ...
    1 Its just copying the entire first row only
    2 I need some filtered data ... is it possible.
    I am attaching the file after the results ...
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    4th July 2006
    Posts
    7

    Re: auto copy specific data from one sheet to other

    can anyone help me out

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,347

    Re: auto copy specific data from one sheet to other

    Hi,

    Try,

    VB:
    Sub test() 
        Dim sWs     As Worksheet 
        Dim rWs     As Worksheet 
        Dim Dest    As Range 
        Dim sData   As Range 
        Dim lRow    As Long 
        Dim CodeNo  As String 
         
        Set sWs = Sheets("source") 
        Set rWs = Sheets("results") 
        lRow = sWs.Cells(Rows.Count, 1).End(xlUp).Row 
        Set sData = sWs.Range("A1:V" & lRow) 
        Set Dest = rWs.[a1] 
         
        CodeNo = Application.InputBox("Enter the Code #", "Code Search") 
        If CodeNo = "" Then Exit Sub 
        CodeNo = Application.WorksheetFunction.Text(CodeNo, "00000000000") 
        If Application.WorksheetFunction.CountIf(sWs.Columns(1), CodeNo) > 0 Then 
            rWs.Cells.Clear 
            With sData 
                .AutoFilter field:=1, Criteria1:=CodeNo 
                .CurrentRegion.Copy Dest 
                .AutoFilter 
            End With 
        End If 
    End Sub 
    
    
    HTH

  6. #6
    Join Date
    4th July 2006
    Posts
    7

    Re: auto copy specific data from one sheet to other

    its working ... but almost same issue ..
    its still writing only in the first row..

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,347

    Re: auto copy specific data from one sheet to other

    VB:
    Sub test_v1() 
        Dim sWs     As Worksheet 
        Dim rWs     As Worksheet 
        Dim Dest    As Range 
        Dim sData   As Range 
        Dim lRow    As Long 
        Dim CodeNo  As String 
         
        Set sWs = Sheets("source") 
        Set rWs = Sheets("results") 
        lRow = sWs.Cells(Rows.Count, 1).End(xlUp).Row 
        Set sData = sWs.Range("A1:V" & lRow) 
        Set Dest = rWs.Cells(Rows.Count, 1).End(xlUp).Offset(1) 
         
        CodeNo = Application.InputBox("Enter the Code #", "Code Search") 
        If CodeNo = "" Then Exit Sub 
        CodeNo = Application.WorksheetFunction.Text(CodeNo, "00000000000") 
        If Application.WorksheetFunction.CountIf(sWs.Columns(1), CodeNo) > 0 Then 
             
            With sData 
                .AutoFilter field:=1, Criteria1:=CodeNo 
                .Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count).Copy Dest 
                .AutoFilter 
            End With 
        End If 
    End Sub 
    
    

  8. #8
    Join Date
    4th July 2006
    Posts
    7

    Re: auto copy specific data from one sheet to other

    Thanks
    it seems working ...
    Just one querry . ... can I only pickup few cells of a row in the result sheet ...

    Currently its perfectly copying the entire row ... but if I want few specific colums... is it possible.

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,347

    Re: auto copy specific data from one sheet to other

    Yes, possible. Let's know which are the columns.

  10. #10
    Join Date
    4th July 2006
    Posts
    7

    Re: auto copy specific data from one sheet to other

    If you see in the attached sheet .... the colums from source sheet ... I need few colomns in Result sheet.

    Source Sheet Result Sheet
    Column A ---------> Column A
    Column B ---------> Column B
    Column E ---------> Column C
    Column D ---------> Column D
    Column F ---------> Column E

    Thanks for your help..
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Copy Specific Data From One Sheet To Another
    By dgetno in forum EXCEL HELP
    Replies: 3
    Last Post: August 14th, 2008, 09:09
  2. Copy Auto Filter Range To Another Sheet
    By heizeer in forum EXCEL HELP
    Replies: 2
    Last Post: June 9th, 2008, 23:47
  3. Replies: 2
    Last Post: April 1st, 2008, 13:40
  4. copy data from a specific range to another sheet
    By ahkenZ in forum EXCEL HELP
    Replies: 5
    Last Post: January 6th, 2006, 13:44
  5. Open and copy a specific sheet
    By robinsma in forum EXCEL HELP
    Replies: 1
    Last Post: April 29th, 2005, 10:24

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
  •  
porno