Move Column values from One Worksheet to Another worksheet.

[AliRizvi]

Please bare with me here, i am a newbie trying my hand at creating MACROs. I have a column A in Worksheet 2, which has 3 values. I would like to copy the values an add 1 to each cell and copy all three values to Worksheet 1 on the first empty cell in Column A.

Thanks in advance.

 

[venkat1926]

you have not given all the details . where are the numbers in sheet2. where do you want to copy in sheet1.

anyhow assume your data (trivial) is like this in sheet2

Sheet2

*	A	B	C	D
*	*	*	*
*	*	*
*	*	*	*
*	*	*
*	*	*
*	*	*	*

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: right"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

[TD="align: right"]2[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]

[TD="align: right"]3[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]

</tbody>

Excel tables to the web >> Excel Jeanie HTML 4


try this macro and sheet1

Sub test()
Dim cfind As Range, c As Range, j As Double
Worksheets("sheet1").Cells.Clear
With Worksheets("sheet2")
Set cfind = .Cells.SpecialCells(xlCellTypeConstants)
'MsgBox cfind.Address
For Each c In cfind
j = c + 1
With Worksheets("sheet1")
.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0) = j
End With
Next c


End With
End Sub

 

[AliRizvi]

Thanks Venkat, here is the scenario: the numbers are in column C of Worksheet 2, first i need to count number of rows, start from the 2nd row then copy each value from Column C of worksheet 2 to worksheet 1 Column D. So for example:


[TABLE="class: grid, width: 100"]
<tbody>[TR]
[TD]Worksheet 2 Column C
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[/TR]
[TR]
[TD]20
[/TD]
[/TR]
[TR]
[TD]21
[/TD]
[/TR]
[TR]
[TD]98
[/TD]
[/TR]
[TR]
[TD]99
[/TD]
[/TR]
[TR]
[TD]76
[/TD]
[/TR]
</tbody>[/TABLE]


Should be copied to worksheet 1 like :
[TABLE="class: grid, width: 100"]
<tbody>[TR]
[TD]Worksheet 1 Column D
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[/TR]
[TR]
[TD]21
[/TD]
[/TR]
[TR]
[TD]22
[/TD]
[/TR]
[TR]
[TD]99
[/TD]
[/TR]
[TR]
[TD]100
[/TD]
[/TR]
[TR]
[TD]77
[/TD]
[/TR]
</tbody>[/TABLE]

 

[venkat1926]

what is the need for "count number of rows, start from the 2nd row" ?

first you should have tried the macro I have given and then shd have responded. the macro I have given gives what you want.

 

[AliRizvi]

Thank you Mr Venkat, i only replied to provide you a more detailed question, since the initial post was little vague. Your code does work and i appreciate you helping me out. I am a newbie with VBA and still trying to get a hang of it.

 

[Ashwini Lambture]

what is the need for "count number of rows, start from the 2nd row" ?

first you should have tried the macro I have given and then shd have responded. the macro I have given gives what you want.

Hi, I have tried this code but it is giving me "run time error-13 ...type mismatch",...can u have solution on this error.........................

 

[venkat1926]

send small extract of input data

 

[venkat1926]

which line gives the error

 

[Ashwini Lambture]

which line gives the error

Hi,the line number of j=c+1

 

[venkat1926]

modified the macro (original data in sheet2 in col. C, C1 has header) copied to sheet1 col D

Sub test()
Dim cfind As Range, c As Range, j As Double
Worksheets("sheet1").Cells.Clear
With Worksheets("sheet2")
Set cfind = Range(.Range("C2"), .Range("C2").End(xlDown))


'MsgBox cfind.Address
For Each c In cfind
j = c + 1
With Worksheets("sheet1")
.Cells(Rows.Count, "D").End(xlUp).Offset(1, 0) = j
End With
Next c




End With
End Sub