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.
 

venkat1926

Well-known Member
Joined
Aug 21, 2005
Messages
4,824
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
 

AliRizvi

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

venkat1926

Well-known Member
Joined
Aug 21, 2005
Messages
4,824
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

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

New Member
Joined
Apr 1, 2014
Messages
23
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

Well-known Member
Joined
Aug 21, 2005
Messages
4,824
send small extract of input data
 

venkat1926

Well-known Member
Joined
Aug 21, 2005
Messages
4,824
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
 

Forum statistics

Threads
1,082,114
Messages
5,363,244
Members
400,722
Latest member
DrewPop24

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top