Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / 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


    Code:
    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,440

    Re: auto copy specific data from one sheet to other

    Hi,

    Try,

    [vba]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[/vba]

    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,440

    Re: auto copy specific data from one sheet to other

    [vba]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[/vba]

  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,440

    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, 10:09
  2. Copy Auto Filter Range To Another Sheet
    By heizeer in forum EXCEL HELP
    Replies: 2
    Last Post: June 10th, 2008, 00:47
  3. Replies: 2
    Last Post: April 1st, 2008, 14:40
  4. copy data from a specific range to another sheet
    By ahkenZ in forum EXCEL HELP
    Replies: 5
    Last Post: January 6th, 2006, 14:44
  5. Open and copy a specific sheet
    By robinsma in forum EXCEL HELP
    Replies: 1
    Last Post: April 29th, 2005, 11: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