Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
Password Reminder
Register
| FAQ | Members List | 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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
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
Reply With Quote
  #2 (permalink)  
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
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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



All times are GMT -4. The time now is 03:52 PM.


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