Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old May 14th, 2007, 05:53 AM
Authorized User
Join Date: Apr 2007
Posts: 41
Thanks: 0
Thanked 0 Times in 0 Posts
Default Copy specific data in specific cells of sheet2

I have 2 sheets namelt sheet1 and sheet2. Now i had prepared a report in which there are several columns or fields/headings. The first column is S.No.

In sheet2 i have another report, where some data is same as that of sheet1. I have to re-enter the common data in sheet2 manually. But i want to do it automatically through excel VBA.

The idea is that both sheets has the first columns S.No. If the S.No. typed in Sheet2 Column , then the code should compare the s.no. of both sheets, if they are same then all the data / record of S.No.1 (Sheet1) should get copied into the specific cells of sheet2.

For example: Sheet1 Data

1 Genecel GLM 100 28/03/2007 100
2 Genecel GLM 69.4 28/03/2007 69.4
3 Genecel GLM spl 75 22/02/2007 75
4 Genecel UN spl 50 22/02/2007 50

Sheet2 Data

S.No. Product Inv.No. Inv.Date

If the S.No. in sheet2 typed by the user is 2, then the records of S.No.2 (Sheet1) should get copied in the cells of sheet2.

Old May 14th, 2007, 07:40 AM
Friend of Wrox
Join Date: Sep 2005
Posts: 812
Thanks: 1
Thanked 53 Times in 49 Posts

Here is a simple code for that
Function Snippet_For_Copy(sSearchString)

 If Trim(sSearchString) = "" Then Exit Function

  With sheets(1).Columns("A:A")
        Set rFindCell = .Find(sSearchString, LookIn:=xlValues, LookAt:=xlWhole)
        If Not rFindCell Is Nothing Then
        sheets(1).Rows(rFindCell.Row).EntireRow.Copy _
                        Destination:=Range("A" & ActiveCell.Row)
          End If
   End With

End Function

You can trigger the function using Worksheet_SelectionChange event

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

 If IsNumeric(Target) = False Then Exit Sub
  If Trim(Target) = "" Then Exit Sub

 Application.EnableEvents = False

  Snippet_For_Copy Target.Value

  Application.EnableEvents = True
End Sub



Similar Threads
Thread Thread Starter Forum Replies Last Post
Search specific data from specific columns yogeshyl SQL Language 1 January 16th, 2008 11:12 AM
Copy specific data from one sheet to another yogeshyl Excel VBA 2 May 11th, 2007 09:14 AM
How to Get Specific Cell Value of Data Grid in C# akumarp2p C# 1 February 1st, 2007 08:17 AM
dispaly specific data from access datbase priyankkgupta Classic ASP Basics 3 December 1st, 2006 10:35 PM
show data for a specific user momo BOOK: Professional ASP.NET 2.0 and Special Edition; ISBN: 978-0-7645-7610-2; ISBN: 978-0-470-04178-9 1 July 17th, 2006 09:08 PM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.