Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
| Search | Today's Posts | Mark Forums Read
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
Location: Silvassa, Dadra & Nagar Haveli, India.
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


SR.NO. PRODUCT NAME STOCK DATE CONSUMED QTY.
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
1

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.





Yogesh
__________________
Yogesh
 
Old May 14th, 2007, 07:40 AM
Friend of Wrox
Points: 3,060, Level: 23
Points: 3,060, Level: 23 Points: 3,060, Level: 23 Points: 3,060, Level: 23
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Sep 2005
Location: , , .
Posts: 812
Thanks: 1
Thanked 53 Times in 49 Posts
Default

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

Cheers
Shasur


http://www.vbadud.blogspot.com




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.