Please help!

keli51

New Member
Joined
Sep 13, 2006
Messages
2
Hello, I need to know how to pull rows from 12 different worksheets into one master spreadsheet. Is this possible? Without copying and pasting of course. That would be too much work.
 

Some videos you may like

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,831
Office Version
  1. 365
Platform
  1. Windows
Well I think it's going to be impossible without some copying.:)

Do you mean you want to automate the copying/pasting rather than doing it manually?
Code:
Sub CompileWS()
Dim wsNew As Worksheet
Dim wsData As Worksheet
Dim LastRowSrc As Long
Dim LastRowDst As Long
    
    Set wsNew = Worksheets.Add
    LastRowDst = 1
    
    For Each wsData In Worksheets
    
        If wsData.Name <> wsNew.Name Then
            LastRowSrc = wsData.Range("A" & Rows.Count).End(xlUp).Row
            wsData.Range("A1").Resize(LastRowSrc).EntireRow.Copy wsNew.Range("A" & LastRowDst)
            LastRowDst = LastRowDst + LastRowSrc
        End If
            
    Next wsData
End Sub
 

keli51

New Member
Joined
Sep 13, 2006
Messages
2
Well I think it's going to be impossible without some copying.:)

Do you mean you want to automate the copying/pasting rather than doing it manually?
Code:
Sub CompileWS()
Dim wsNew As Worksheet
Dim wsData As Worksheet
Dim LastRowSrc As Long
Dim LastRowDst As Long
    
    Set wsNew = Worksheets.Add
    LastRowDst = 1
    
    For Each wsData In Worksheets
    
        If wsData.Name <> wsNew.Name Then
            LastRowSrc = wsData.Range("A" & Rows.Count).End(xlUp).Row
            wsData.Range("A1").Resize(LastRowSrc).EntireRow.Copy wsNew.Range("A" & LastRowDst)
            LastRowDst = LastRowDst + LastRowSrc
        End If
            
    Next wsData
End Sub
/quote]

Thanks for the quick reply.
I'm sorry I don't understand what this means???
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,831
Office Version
  1. 365
Platform
  1. Windows
This is VBA code that will go through every sheet in a workbook and copy the data to a newly created workbook.

It's generic code and would probably need to be altered to work with your situation.

How it would be altered would depend on what you actually want to do and how you have things set up, so more information on that would be helpful.:)

If you wanted to run this code.

1 Open the VBA Editor.(ALT+F11)

2 Create a new module. (Insert>Module)

3 Paste the code into the new module.

4 Goto Run>Run Sub/Userform.(F5)
 

Watch MrExcel Video

Forum statistics

Threads
1,109,028
Messages
5,526,336
Members
409,696
Latest member
EERS

This Week's Hot Topics

Top