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.
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,303
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
76,303
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)
 

Forum statistics

Threads
1,141,142
Messages
5,704,529
Members
421,353
Latest member
jekoxien15

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top