Macro for comapre and copy

[Arain]

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.

Please let me know if a macro can be written to do this procedure.

Your help will be life saver.

Regards

Arain

 

[Joel]

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

 

[Arain]

Joel,

Its not working at all. When i run the macro in sheet2 it just copies the
first column and paste it into 2nd column. Can you please help me out
figuring what i am doing wrong.

 

[Joel]

I made some changes to inlude sheett names in additional places in the code

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


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

Set HeaderRange = Sheets(FirstSheet).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 = Sheets(FirstSheet). _
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

 

[Arain]

Its breaking and giving error on this part

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

is there anyway i can send you the sheet i am working on

 

[Joel]

I tlooks like the code is wrapping on to lines Put the LastColumn)) on the
prevvious line after the commar.

 

[Arain]

i tried that but it still fails.

I tlooks like the code is wrapping on to lines Put the LastColumn)) on the
prevvious line after the commar.

 

[Joel]

i tried that but it still fails.

 

[Joel]

make sure that sheet 1 & 2 are either b oth text or both number on sheets 1 &
2. Also make sure the are no spaces in the header. It appears the data in
you header row do not match for some reason. Check to make sure there isn't
a single quote preceding the data in the header row on one worksheet and not
another(' means text).

 

[Arain]

i tried eveything even convert it to numbers on both sheet. there is
something wrong in this line of the code
Set HeaderRange = Sheets(FirstSheet).Range(Cells(1, "C"), Cells(1,
LastColumn))

It is giving runtime error.

i really appreciate your help joel i need this really bad.

 

[Joel]

try replacing "C" with 3. Also try to right click Lastcolumn and add to
watch a watch to find out the value. same thing with firstsheet - see if it
matches your worksheet name. Also close any other workbooks that may be
opened and make sure no cells are selected on any worksheets.

 

[Dave Peterson]

I'd try qualifying the ranges--including the cells() portion.

Set HeaderRange = Sheets(FirstSheet).Range(Sheets(FirstSheet).Cells(1, "C"), _
Sheets(FirstSheet).Cells(1, LastColumn))

Actually, I'd use this to save wear and tear on my fingers:

with sheets(firstsheet)
Set HeaderRange = .Range(.Cells(1, "C"), .Cells(1, LastColumn))
end with

 

[Arain]

please let me know if i can send you the sheet nothing is working i have
tried everything on my end and i am really getting frustrated please your
help will be great

 

[Joel]

you can email me at (e-mail address removed)

please let me know if i can send you the sheet nothing is working i have
tried everything on my end and i am really getting frustrated please your
help will be great