Solved

Easy:  Copy names from one sheet to another. (EXCEL)

Posted on 2004-08-13
15
204 Views
Last Modified: 2010-05-02
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
0
Comment
Question by:efarhat
  • 6
  • 5
  • 3
  • +1
15 Comments
 
LVL 8

Expert Comment

by:bramsquad
ID: 11793264
unless you have two excel objects open, your going to have to store the data in a temp variable...or possibly look into programmatically saving the data to the clipboard

~b

0
 

Author Comment

by:efarhat
ID: 11793563
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.
0
 
LVL 15

Expert Comment

by:Colosseo
ID: 11793711
Hi can you explain again what you are after... maybe with before and after examples

thanks

scott
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
LVL 76

Expert Comment

by:GrahamSkan
ID: 11794100
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("Report").UsedRange
    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("Layout").Cells(r2, 1) = Trader
        ActiveWorkbook.Sheets("Layout").Cells(r2, ColCount + 1) = rng.Cells(r, 2)
        ColCount = (ColCount + 1) Mod 4
    Next r
End Sub

0
 

Author Comment

by:efarhat
ID: 11794269
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.)
0
 
LVL 15

Expert Comment

by:Colosseo
ID: 11794456
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
0
 
LVL 76

Accepted Solution

by:
GrahamSkan earned 50 total points
ID: 11794479
Sorry, I misunderstood. What I did would display the names in col 1 and the data in cols 2 to 5.

Try this instead.

Sub CopyTraders()
    Dim Trader As String
    Dim r As Integer
    Dim r2 As Integer
    Dim ColCount As Integer
    Dim rng As Range
   
    Set rng = ActiveWorkbook.Sheets("Report").UsedRange
    r2 = 1
    For r = 1 To rng.Rows.Count
        If rng.Cells(r, 1).Value <> "" Then
            Trader = rng.Cells(r, 1).Value
            ActiveWorkbook.Sheets("Layout").Cells(r2, ColCount + 1) = Trader
            ColCount = (ColCount + 1) Mod 4
            If ColCount = 0 Then
                r2 = r2 + 1
            End If
        End If
    Next r
End Sub

0
 

Author Comment

by:efarhat
ID: 11796314
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 ??
0
 
LVL 15

Expert Comment

by:Colosseo
ID: 11796776
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
0
 

Author Comment

by:efarhat
ID: 11796990
scott,

how can you define the id like that?  i received an error saying,  variable not defined
0
 

Author Comment

by:efarhat
ID: 11797188
Grahman Scam, your code worked. i had to make some modifications but it's working correctly.
0
 
LVL 15

Expert Comment

by:Colosseo
ID: 11797191
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
0
 
LVL 15

Expert Comment

by:Colosseo
ID: 11797212
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
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 11798991
efarhat, thanks for trying the code. I trust that your misspelling of my surname was accidental.
Graham
0
 

Author Comment

by:efarhat
ID: 11833915
indeed it was.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
How to create a duplicate finder Application 9 122
Validating VB6 Function 19 65
JSON Response and request in VB6 application 11 571
Publisher:   Unknown     VB.exe Application 1 27
The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question