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 11th, 2007, 07:28 AM
Authorized User
 
Join Date: Apr 2007
Posts: 41
Thanks: 0
Thanked 0 Times in 0 Posts
Default Copy specific data from one sheet to another

I had 2 Sheets in a excel file, Sheet1 & sheet2. I feed records in sheet1 having certain format. I want the records from sheet1 to get copied by using VBA to the sheet2's specific rows / columns.

Here in sheet2, what i need is when i enter the record no. of sheet1 in specific row / column of sheet2, that record of sheet1 should get copied into the sheet2.

Please help me for the same.



Yogesh
__________________
Yogesh
 
Old May 11th, 2007, 09:09 AM
Friend of Wrox
 
Join Date: Feb 2007
Posts: 163
Thanks: 0
Thanked 2 Times in 2 Posts
Default

This is pretty straight forward. This example assumes the rows are the same from sheet 1 to sheet 2.

Copy this code into Sheet2 (The sheet the data is to be on):
-----------------------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)

'If it's column A and not row 1, finds the value from sheet 1 and copies it to this sheet
  Dim oFind As Range
  If Target.Column = 1 And Target.Row > 1 Then
    Set oFind = Worksheets("Sheet1").Range("A:A").Find(Target.Valu e, LookIn:=xlValues) 'Source sheet name is Sheet1
    If Not oFind Is Nothing Then 'Does below copy if a corresponding value found on Sheet1 in column A
      oFind.EntireRow.Copy Destination:=Worksheets("Sheet2").Range(Target.Add ress(False, False)) 'Target is Sheet2
    End If
  End If

End Sub
-----------------------------------------------------------------------------

Hope this helps.

 
Old May 11th, 2007, 09:14 AM
Friend of Wrox
 
Join Date: Feb 2007
Posts: 163
Thanks: 0
Thanked 2 Times in 2 Posts
Default

P.S. Also of note is that the above code is set to skip value changes for row 1 which I use for headings. If you have 2 heading rows and your detail row starts on row 3 (Where you want data to start copying if found) thhen change the if/then to be:

If Target.Column = 1 and Target.Row > 2 'Note that if your compare data is in column C then column would = 3 instead of 1

Just a hopefully helpful clarification





Similar Threads
Thread Thread Starter Forum Replies Last Post
Open Workbook,Copy Sheet,Move Sheet, Close/Save ptrussell2009 Excel VBA 0 June 13th, 2008 02:28 PM
How to position to a specific sheet before saving pkipe Excel VBA 1 December 17th, 2007 10:36 PM
Copy specific data in specific cells of sheet2 yogeshyl Excel VBA 1 May 14th, 2007 07:40 AM
Removing a specific sheet from a loop in Excel sibo32000 Excel VBA 1 February 9th, 2007 01:54 PM
Marcro to copy data from one sheet to another Crupa Excel VBA 1 November 24th, 2004 04:12 AM





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