efarhat
asked on
Easy: Copy names from one sheet to another. (EXCEL)
This is what I was looking for when I meant copy column data over. I only wanted to copy the names into a another sheet but I want them to be in 4 columns instead of one long column? 4 cols of 15 person each. There is spacing problems too.
[code]
John Smith data
data
Paul Rucks data
Steve Parker data
data
data
[/code]
How do I get a list of only the names?
Sub CopyTraders()
Dim Trader As Range
Dim Traders As Range
Dim ColCount As Integer
Sheets("Report").Select
Range("A1").Select
Set Traders = Range(ActiveCell, ActiveCell.End(xlDown))
Sheets("Layout").Select
Range("A1").Select
ColCount = 1
For Each Trader In Traders
If ColCount > 3 Then ' set this value to the number of columns you require
ColCount = 1
ActiveCell.Offset(1, 0).Select
End If
ActiveCell.Offset(0, (ColCount - 1) * 2).Value = Trader
ColCount = ColCount + 1
Next Trader
End Sub
[code]
John Smith data
data
Paul Rucks data
Steve Parker data
data
data
[/code]
How do I get a list of only the names?
Sub CopyTraders()
Dim Trader As Range
Dim Traders As Range
Dim ColCount As Integer
Sheets("Report").Select
Range("A1").Select
Set Traders = Range(ActiveCell, ActiveCell.End(xlDown))
Sheets("Layout").Select
Range("A1").Select
ColCount = 1
For Each Trader In Traders
If ColCount > 3 Then ' set this value to the number of columns you require
ColCount = 1
ActiveCell.Offset(1, 0).Select
End If
ActiveCell.Offset(0, (ColCount - 1) * 2).Value = Trader
ColCount = ColCount + 1
Next Trader
End Sub
ASKER
i don't understand? it's one excel app one with 2 sheets. copy data from one sheet and move it to the other. that code right there works except the logic is incorrect.
Hi can you explain again what you are after... maybe with before and after examples
thanks
scott
thanks
scott
Sub CopyTraders()
Dim Trader As String
Dim Traders As Range
Dim r As Integer
Dim r2 As Integer
Dim ColCount As Integer
Dim rng As Range
Set rng = ActiveWorkbook.Sheets("Rep ort").Used Range
ColCount = 1
For r = 1 To rng.Rows.Count
Debug.Print rng.Cells(r, 1).Value
If rng.Cells(r, 1).Value <> "" Then
r2 = r2 + 1
Trader = rng.Cells(r, 1).Value
End If
ActiveWorkbook.Sheets("Lay out").Cell s(r2, 1) = Trader
ActiveWorkbook.Sheets("Lay out").Cell s(r2, ColCount + 1) = rng.Cells(r, 2)
ColCount = (ColCount + 1) Mod 4
Next r
End Sub
Dim Trader As String
Dim Traders As Range
Dim r As Integer
Dim r2 As Integer
Dim ColCount As Integer
Dim rng As Range
Set rng = ActiveWorkbook.Sheets("Rep
ColCount = 1
For r = 1 To rng.Rows.Count
Debug.Print rng.Cells(r, 1).Value
If rng.Cells(r, 1).Value <> "" Then
r2 = r2 + 1
Trader = rng.Cells(r, 1).Value
End If
ActiveWorkbook.Sheets("Lay
ActiveWorkbook.Sheets("Lay
ColCount = (ColCount + 1) Mod 4
Next r
End Sub
ASKER
Scott,
thanks for reading my post. let me explain. if you scroll up to the first post, you'll see names in one column and data in other columns. i want to copy ONLY the names which are in the first column. Although, the names don't follow one by one (row by row). From the example at the top, "John Smith" is on row 1 and "Paul Rucks" is on row 3.
Once you copy all the names, I want them to be displayed in Sheet2, one after another.
ie.
John Smith
Paul Rucks
Steve Parker
but there are a lot of names, 50 total and I want to display them in 4 columns, names per column. (i know the last column will only have 5.)
thanks for reading my post. let me explain. if you scroll up to the first post, you'll see names in one column and data in other columns. i want to copy ONLY the names which are in the first column. Although, the names don't follow one by one (row by row). From the example at the top, "John Smith" is on row 1 and "Paul Rucks" is on row 3.
Once you copy all the names, I want them to be displayed in Sheet2, one after another.
ie.
John Smith
Paul Rucks
Steve Parker
but there are a lot of names, 50 total and I want to display them in 4 columns, names per column. (i know the last column will only have 5.)
Hi there
I dont think I totally understand what you mean by I want to display them in 4 columns, names per column. (i know the last column will only have 5.)
Anyway the code below will go needs you to specify the start and end rows to search it will then go through each row in sheet1 checking the value in column A.
If the value is not empty then it is moved to sheet2
Give it ago and if i have missed something (which i think i have) then let me know :)
Regards
Scott
Sub get_Names()
Dim int_Start_Cell
Dim int_Stop_Cell
int_Start_Cell = 1
int_Stop_Cell = 8
' Select cell A1 on sheet2
Sheets("Sheet2").Select
Range("A1").Select
' Loop from the starting cell to the end cell
For int_Current_Cell = int_Start_Cell To int_Stop_Cell
' If the value in the cell is not empty
If Sheets("Sheet1").Range("A" & int_Current_Cell).Value <> "" Then
' Output the name to Sheet2
ActiveCell.Value = Sheets("Sheet1").Range("A" & int_Current_Cell).Value
' Move to the next cell
ActiveCell.Offset(1, 0).Select
End If
Next
End Sub
I dont think I totally understand what you mean by I want to display them in 4 columns, names per column. (i know the last column will only have 5.)
Anyway the code below will go needs you to specify the start and end rows to search it will then go through each row in sheet1 checking the value in column A.
If the value is not empty then it is moved to sheet2
Give it ago and if i have missed something (which i think i have) then let me know :)
Regards
Scott
Sub get_Names()
Dim int_Start_Cell
Dim int_Stop_Cell
int_Start_Cell = 1
int_Stop_Cell = 8
' Select cell A1 on sheet2
Sheets("Sheet2").Select
Range("A1").Select
' Loop from the starting cell to the end cell
For int_Current_Cell = int_Start_Cell To int_Stop_Cell
' If the value in the cell is not empty
If Sheets("Sheet1").Range("A"
' Output the name to Sheet2
ActiveCell.Value = Sheets("Sheet1").Range("A"
' Move to the next cell
ActiveCell.Offset(1, 0).Select
End If
Next
End Sub
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Colosseo,
You don't have int_Current_Cell defined.
when i sent int_start_cell = 4 and int_current_cell =4 it still doesn't go into the loop. not sure why.
For int_Current_Cell = int_Start_Cell To int_Stop_Cell ??
You don't have int_Current_Cell defined.
when i sent int_start_cell = 4 and int_current_cell =4 it still doesn't go into the loop. not sure why.
For int_Current_Cell = int_Start_Cell To int_Stop_Cell ??
hi
try
int_Start_Cell = 4
int_Stop_Cell = 5
int_Current_Cell id defined in the for statement
For int_Current_Cell = int_Start_Cell To int_Stop_Cell
Scott
try
int_Start_Cell = 4
int_Stop_Cell = 5
int_Current_Cell id defined in the for statement
For int_Current_Cell = int_Start_Cell To int_Stop_Cell
Scott
ASKER
scott,
how can you define the id like that? i received an error saying, variable not defined
how can you define the id like that? i received an error saying, variable not defined
ASKER
Grahman Scam, your code worked. i had to make some modifications but it's working correctly.
Hi efarhat
sorry about that thats my fault, you're one of those crazy people who uses Option Explicit :)
try this code instead... with Option Explicit you have to Dimensionalise(spelling) every variable which is good for error checking and is good practise
Sub get_Names()
Dim int_Current_Cell
Dim int_Start_Cell
Dim int_Stop_Cell
int_Start_Cell = 1
int_Stop_Cell = 8
' Select cell A1 on sheet2
Sheets("Sheet2").Select
Range("A1").Select
' Loop from the starting cell to the end cell
For int_Current_Cell = int_Start_Cell To int_Stop_Cell
' If the value in the cell is not empty
If Sheets("Sheet1").Range("A" & int_Current_Cell).Value <> "" Then
' Output the name to Sheet2
ActiveCell.Value = Sheets("Sheet1").Range("A" & int_Current_Cell).Value
' Move to the next cell
ActiveCell.Offset(1, 0).Select
End If
Next
End Sub
Regards
Scott
sorry about that thats my fault, you're one of those crazy people who uses Option Explicit :)
try this code instead... with Option Explicit you have to Dimensionalise(spelling) every variable which is good for error checking and is good practise
Sub get_Names()
Dim int_Current_Cell
Dim int_Start_Cell
Dim int_Stop_Cell
int_Start_Cell = 1
int_Stop_Cell = 8
' Select cell A1 on sheet2
Sheets("Sheet2").Select
Range("A1").Select
' Loop from the starting cell to the end cell
For int_Current_Cell = int_Start_Cell To int_Stop_Cell
' If the value in the cell is not empty
If Sheets("Sheet1").Range("A"
' Output the name to Sheet2
ActiveCell.Value = Sheets("Sheet1").Range("A"
' Move to the next cell
ActiveCell.Offset(1, 0).Select
End If
Next
End Sub
Regards
Scott
I just tried Grahams code....
I now know what you meant by I want to display them in 4 columns, names per column. (i know the last column will only have 5.)
I also know my code doesnt do that :)
Scott
I now know what you meant by I want to display them in 4 columns, names per column. (i know the last column will only have 5.)
I also know my code doesnt do that :)
Scott
efarhat, thanks for trying the code. I trust that your misspelling of my surname was accidental.
Graham
Graham
ASKER
indeed it was.
~b