+ Reply to Thread
Results 1 to 4 of 4

Macro that copies from multiple sheets to one column

  1. #1
    Registered User
    Join Date
    08-21-2007
    Posts
    14

    Macro that copies from multiple sheets to one column

    I have the following macro that takes every column from every sheet in a workbook and combines it into one column on a new spreadsheet:

    Sub MultiColsToA()
    Dim rCell As Range
    Dim lRows As Long
    Dim lCols As Long
    Dim lCol As Long
    Dim ws As Worksheet
    Dim wsNew As Worksheet

    lCols = Columns.Count
    lRows = Rows.Count
    Set wsNew = Sheets.Add()

    For Each ws In Worksheets
    With ws
    For Each rCell In .Range("B1", .Cells(1, lCols).End(xlToLeft))
    .Range(rCell, .Cells(lRows, rCell.Column).End(xlUp)).Copy _
    wsNew.Cells(lRows, 1).End(xlUp)(2, 1)
    Next rCell
    End With
    Next ws

    End Sub

    What I want it to do is when it goes to the next sheet I want it to start a new column. so if Sheet 3 is the new sheet I want Sheet 1 information in column A and Sheet 2 information in column B, not all of it in Column A. Also, I need to copy the values only, because what's in the cells are links and moving them to a different cell creates a #REF! error.

    Any help would be appreciated!
    Last edited by favoritepsalm1; 08-21-2007 at 08:00 AM.

  2. #2
    Forum Contributor
    Join Date
    03-24-2004
    Location
    Edam Netherlands
    Posts
    181
    HTML Code: 

  3. #3
    Registered User
    Join Date
    08-21-2007
    Posts
    14
    GREAT! Just one thing; can we make it copy of column A and then go to column B, etc instead of doing all of Row 1 and then Row 2? My columns in sheet 1 (cost code) correspond to a dollar value in sheet 2.

    Thank you so much Kaak!

  4. #4
    Forum Contributor
    Join Date
    03-24-2004
    Location
    Edam Netherlands
    Posts
    181
    I think that removing the if statement

    If Trim(Cell.Value) <> "" Then
    End if

    Will do the trick

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1