PDA

View Full Version : Solved: copy data between multiple sheets



JohnyG
12-16-2008, 06:54 AM
I have multiple worksheets. The very first one is the ?Sheet1? which is the Field Master rest all are the worksheets contains different activities that we need to record in ?Sheet1?.

If someone enter any value in Activity!Column B then macro will check the existence of that value in Sheet1!Column A and if found then copy the value of Activity!ColumnC to Sheet1!Column F and Activity!ColumnD to Sheet1!Column G

For example

If I enter invoice number in (Activity!ColumnB) then first the macro will search the value(?Invoice Number?) in sheet1 if found then copy the value ?Pulled? (Activity!Col C) and ?Yes? (Activity!Col E) from sheet 2 and paste it in Sheet1 ColumnF and ColumnG.

JohnyG
12-17-2008, 03:21 AM
Someone pls help.

JohnyG
12-17-2008, 03:45 AM
Ok i will try to explain it in simple language.

I have two worksheets ?Sheet1? and ?Sheet2? (both from same workbook). Need a code to compare the value of Sheet2!Column B to Sheet1!ColA. If found then copy the values from Sheet2!Column C and D to Sheet1!Col E and F. If not then do nothing.

Benzadeus
12-17-2008, 03:54 AM
You don't need a macro to do this for you. I just used =PROCV function (well, my Excel is in Portuguese, I don't know if it has other name in your version).

See if my workbook as example helps you.

JohnyG
12-17-2008, 04:15 AM
Hi Benzadeus,

Really appreciate your help.

However in "Activity1" Column C and Column E are drop down. User do not need to type in Pulled/Generated or Yes/No. Those fields will be a dropdown list . My question here is how can i put in formula in cell it has a drop down list.

Thanks again for all your help

Benzadeus
12-17-2008, 05:04 AM
And now?

JohnyG
12-17-2008, 05:12 AM
Thanks a lot for taking interest in my problem.

However, the solution is still not working. I am not able to get the value (Pulled/Generated and Yes/No)trasferred from "Actovity1" to "Sheet1".

Secondly if i select yes or no (Activity1 sheet) then the formula disappears automatically ...

Please advice

JohnyG
12-17-2008, 06:52 AM
I am trying to use the following code to achieve the solution. However, it the below listed code compares the value and transfered not matching records into "unmatched" worksheet.

What i want is:
Compare Sheet2!columnB with Sheet1!ColumnA if it matched then copy the values of col C and Col E (both from Sheet2) to Col F and Col G (Sheet1)

Sub macro4()
Dim rngData As Range, rngItem As Range, rngComb As Range, rngOut As Range
Dim rngnotMatch As Range

Application.ScreenUpdating = False


With Worksheets("Sheet1")
Set rngData = .Range("A2:A" & .Range("A65536").End(xlUp).Row) '.SpecialCells(xlCellTypeConstants)
End With

With Worksheets("Sheet2")
Set rngComb = .Range("B2:B" & .Range("B65536").End(xlUp).Row)
End With

With Worksheets("Unmatched")
Set rngnotMatch = .Range("A" & .Range("A65536").End(xlUp).Row + 1)
End With

'For Each rngItem In rngData
For Each rngItem In rngData
If rngItem = " " Then Exit Sub
Set rngOut = rngComb.Find(What:=rngItem)

If Not rngOut Is Nothing Then
rngOut.Offset(0, 5).Value = rngItem.Offset(0, 1).Value
rngOut.Offset(0, 6).Value = rngItem.Offset(0, 3).Value
Else
Range(rngItem, rngItem.Offset(0, 5)).Copy rngnotMatch
Set rngnotMatch = rngnotMatch.Offset(1, 0)
End If
Next rngItem

Application.ScreenUpdating = True
End With

End Sub

Benzadeus
12-17-2008, 08:26 AM
Question 1: Will data at "Sheet1", row A repeat?

Question 2: When changing data at "Sheet1" (any row), will this change propagate trought the activities?

Benzadeus
12-18-2008, 02:44 AM
Well, assuming the two propositions above as false, here's what I've done:


Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Sh.Name = "Sheet1" Then
' If Not Intersect(Target, Union(Sh.Columns("C"), Sh.Columns("E"))) Is Nothing Then
' End If
Else
If Not Intersect(Target, Sh.Columns("C")) Is Nothing Then
Target.Offset(, 1) = Sheets("Sheet1").Cells(WorksheetFunction.Match(Target, Sheets("Sheet1").Columns("A"), 0), "F")
Target.Offset(, 3) = Sheets("Sheet1").Cells(WorksheetFunction.Match(Target, Sheets("Sheet1").Columns("A"), 0), "G")
ElseIf Not Intersect(Target, Sh.Columns("D")) Is Nothing Then
Sheets("Sheet1").Cells(WorksheetFunction.Match(Target.Offset(, -1), Sheets("Sheet1").Columns("A"), 0), "F") = Target
ElseIf Not Intersect(Target, Sh.Columns("F")) Is Nothing Then
Sheets("Sheet1").Cells(WorksheetFunction.Match(Target.Offset(, -3), Sheets("Sheet1").Columns("A"), 0), "G") = Target
End If
End If
End Sub


Please look at book.

JohnyG
12-19-2008, 07:32 AM
Thanks Benzadeus,

That did the trick... Thanks a lot



Regards