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!
Bookmarks