Move Column values from One Worksheet to Another worksheet.

AliRizvi

New Member
Joined
Aug 9, 2014
Messages
4
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.
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
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

*ABCD
1****
21***
3****
4*2**
5***3
6****

<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>
</tbody>


Excel tables to the web >> Excel Jeanie HTML 4


try this macro and sheet1

Code:
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
 
Upvote 0
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:


Worksheet 2 Column C
1
3
4
5
20
21
98
99
76

<tbody>
</tbody>


Should be copied to worksheet 1 like :
Worksheet 1 Column D
2
4
5
6
21
22
99
100
77

<tbody>
</tbody>
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.........................
 
Upvote 0
modified the macro (original data in sheet2 in col. C, C1 has header) copied to sheet1 col D

Code:
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
 
Upvote 0

Forum statistics

Threads
1,213,533
Messages
6,114,179
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top