PDA

View Full Version : [SOLVED] Macro that copies data and format Worksheet to worksheet



Zlerp
09-15-2014, 12:07 PM
Hello,

I am looking to create a macro that will use 2 worksheets in the same macro book. I want the data and format from one sheet to be copied to the other sheet if the values match in column A.
****************************
I do not want colums I,J,K,L,O, or P to be copied
****************************
For example.

in Worksheet 1 I have data in A4:P50 (some cells are highlighted)
in Worksheet 2 i have data in A4:P90

If A7 from worksheet 1 and A12 from worksheet 2 match, I want to copy A7:H7 and M7:N7 (format and all) to A9:H9 and M9:N9.

This should happen for all matching values in the A column from worksheet 1 to worksheet 2.
If the cells don't match the value should stay the same.

P.S. there will be more than 50 rows of data, so i need it to do this until the last row of data.

p45cal
09-15-2014, 03:16 PM
If A7 from worksheet 1 and A12 from worksheet 2 match, I want to copy A7:H7 and M7:N7 (format and all) to A9:H9 and M9:N9.Could you explain the logic of the red row numbers?

Zlerp
09-15-2014, 06:35 PM
Could you explain the logic of the red row numbers?

Worksheet 1 has data in A4:P50
Worksheet 2 ihashave data in A4:P90 (some cells are highlighted)
12278




If A9 from worksheet 1 and A7 from worksheet 2 match, I want to copy A7:H7 and M7:N7 (format and all) from worksheet 2 to A9:H9 and M9:N9 on worksheet 1.






This should happen for all matching values in the A column on worksheet 1 and worksheet 2.








If the cells don't match the value should stay the same.





































12278

p45cal
09-16-2014, 01:20 PM
try:
Sub blah()
Dim xx As Range
With Sheets("Worksheet2")
For Each cll In .Range(.Cells(4, 1), .Cells(3, 1).End(xlDown)).Cells
Set xx = Sheets("Worksheet1").Columns(1).Find(what:=cll.Value, LookAt:=xlWhole, LookIn:=xlFormulas, searchformat:=False)
If Not xx Is Nothing Then
cll.Resize(, 8).Copy xx
cll.Offset(, 12).Resize(, 2).Copy xx.Offset(, 12)
End If
Next cll
End With
End Sub

Zlerp
09-17-2014, 02:20 PM
try:
Sub blah()
Dim xx As Range
With Sheets("Worksheet2")
For Each cll In .Range(.Cells(4, 1), .Cells(3, 1).End(xlDown)).Cells
Set xx = Sheets("Worksheet1").Columns(1).Find(what:=cll.Value, LookAt:=xlWhole, LookIn:=xlFormulas, searchformat:=False)
If Not xx Is Nothing Then
cll.Resize(, 8).Copy xx
cll.Offset(, 12).Resize(, 2).Copy xx.Offset(, 12)
End If
Next cll
End With
End Sub

Seems to work Perfect!!! thanks for the help!