I have a spread sheet with column A and B being static but Column C1 has a
value 101 and D has 102 and so on till 900. What I want to do is that compare
C1 with another sheet that is in same format but missing number in between
for example its has C1=101 and D1=103. If C101 is there i need to copy all
value in that column to sheet 2 underneath C101 but if D1 is 103 then it
should put 0' in D1=102 from D2 - D25. let me know if there can be any macro
written to compare and copy the values.

If you dont understand anypart please feel free to ask as many questions




To explain in detail. The first sheet looks like this

c d e
101 103 104
2256 2223 3345

sheet 2 looks like this

C D E F
101 102 103 104

Now i want to check if 101 exist in sheet 1 copy all values underneath that
column to sheet 2 till C25

If 102 does not exist replace the cell with D25

if 103 exist copy all value undernead 103 to sheet 2 and so on.



Sub copycells()
Const FirstSheet = "sheet1"
Const SecondSheet = "sheet2"


Sheets(FirstSheet).Activate
LastColumn = Cells(1, Columns.Count).End(xlToLeft).Column

Set HeaderRange = Range(Cells(1, "C"), Cells(1, LastColumn))

For Each cell In HeaderRange

If cell = Sheets(SecondSheet).Cells(cell.Row, cell.Column) Then

Sheets(FirstSheet).Activate
LastRow = Cells(Rows.Count, cell.Column).End(xlUp).Row
Set RowRange = Range(Cells(2, cell.Column), Cells(LastRow, cell.Column))

RowRange.Copy Destination:=Sheets(SecondSheet).Cells(2, cell.Column)
Else

Sheets(SecondSheet).Activate
Cells(1, cell.Column) = cell

Set PasteRange = Sheets(SecondSheet).Range(Cells(2, cell.Column), _
Cells(25, cell.Column))

PasteRange.Select
Selection = 0

End If


Next cell



End Sub



Your help will be life saver.

Regards

Arain

Appended is my interpretation of your code in a condenced form. However, it
doesn't do anything more than what you already have. I don't understand what
you are trying to do. If you let us know how this fails to do what you want
then it will illustrate your situation.

Sub copycells()
Dim header As Range, r As Range
Dim c As Range, c1 As Range, c2 As Range

With Sheets("Sheet1")
'use c to define header range
Set c = .Cells(1, Columns.Count).End(xlToLeft)
Set header = .Range(.Range("C1"), c)
For Each c1 In header.Cells
Set c2 = Sheets("Sheet2").Range(c1.Address)
If c1.Value = c2.Value Then
'use c to define column containing data
Set c = .Cells(Rows.Count, c1.Column).End(xlUp)
Set r = .Range(c1(2), c)
r.Copy c2(2)
Else
c2.Value = c1.Value
c2(2).Resize(24, 1).Value = 0
End If
Next
End With
End Sub

Regards,
Greg

Greg,

Your macro copies all data till 103 from sheet 1 but when it reaches 103 it
sees 103 is missing and delets the column and put 0 in all the columns after
that like 104 had a 0 value and all the cells after that even though they had
data in sheet1.

"Greg Wilson" wrote:

Appended is my interpretation of your code in a condenced form. However, it
doesn't do anything more than what you already have. I don't understand what
you are trying to do. If you let us know how this fails to do what you want
then it will illustrate your situation.

Sub copycells()
Dim header As Range, r As Range
Dim c As Range, c1 As Range, c2 As Range

With Sheets("Sheet1")
'use c to define header range
Set c = .Cells(1, Columns.Count).End(xlToLeft)
Set header = .Range(.Range("C1"), c)
For Each c1 In header.Cells
Set c2 = Sheets("Sheet2").Range(c1.Address)
If c1.Value = c2.Value Then
'use c to define column containing data
Set c = .Cells(Rows.Count, c1.Column).End(xlUp)
Set r = .Range(c1(2), c)
r.Copy c2(2)
Else
c2.Value = c1.Value
c2(2).Resize(24, 1).Value = 0
End If
Next
End With
End Sub

Regards,
Greg