VBA - Retrieve data from multiple worksheets

JeanRene

New Member
Joined
Dec 30, 2014
Messages
44
Hello,
I have files with data on multiple worksheets, each worksheet being a day of a month. I would like to have a macro which will retrieve all data from the 30 or 31 worksheets into a unique worksheet. In each worksheet I have data starting from cells B2 and C2. Some worksheets may have blank data, in that case no data is to be retrieved.
Can anyone advise on some vba code?
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

KennyGreens

Board Regular
Joined
Aug 8, 2018
Messages
142
Hi there, is it assumed that there is always equal amounts of data in both column B and C in each sheet? For example on the first sheet of data, if there are 30 used rows in column B will there always be 30 used rows in column C?
 

JeanRene

New Member
Joined
Dec 30, 2014
Messages
44
Hello!
No, amount of data from one sheet to another can be different. One day I can have 200 rows of data, the next 400, then 0, then 500, etc. Also I forgot to say that the worksheets where data are, are named with a number: 1 for the first day of the month, then 2,3 etc until 30 or 31.
 
Last edited:

KennyGreens

Board Regular
Joined
Aug 8, 2018
Messages
142
Hello!
No, amount of data from one sheet to another can be different. One day I can have 200 rows of data, the next 400, then 0, then 500, etc. Also I forgot to say that the worksheets where data ara are name with a number: 1 for the first day of the month, then 2,3 etc until 30 or 31.

When looking at each individual sheet I mean, will columns B and C on a single sheet always go down the same number of rows? If there is data from B2:B30 on any given sheet, will the same sheet have data from C2:C30? Or can some sheets have data from B2:B30 and C2:C100 on the same sheet?
 
Last edited:

JeanRene

New Member
Joined
Dec 30, 2014
Messages
44

ADVERTISEMENT

Ah, yes, in the same sheet, the number of rows is equal on column B and C :)
 

JeanRene

New Member
Joined
Dec 30, 2014
Messages
44
Wandering through the forum I found a code that was nearly what I was looking for. I only tweaked it a bit to adapt to my need and it works perfect. Here it is

Code:
Sub consold()
Dim sh As Worksheet, lr As Long
    For Each sh In ThisWorkbook.Sheets
        If IsNumeric(sh.Name) Then ' If your master sheet is not named Master then change this.
            lr = sh.Cells.Find("*", , xlFormulas, xlPart, xlByRows, xlPrevious).Row
            sh.Range("O7:P" & lr).Copy Sheets("Master").Cells(Rows.Count, 1).End(xlUp)(2) 'Edit Master name
        End If
    Next
End Sub


Based on code found here
https://www.mrexcel.com/forum/excel...s-into-one-master-data-sheet-please-help.html
 

KennyGreens

Board Regular
Joined
Aug 8, 2018
Messages
142

ADVERTISEMENT

I apologize for a delay in a response, something came up. You can try something like this:

Code:
Dim i As Integer
Dim lrow As Long
Dim newSh As Worksheet
Dim shc As Integer

Set newSh = Sheets.Add(after:=Sheets(Sheets.Count)) 'Adds a new sheet to be pasted to
newSh.Name = "Total"

shc = Sheets.Count - 1

For i = 1 To shc
    Sheets(i).UsedRange.Copy 'This assumes each sheet only has data in columns B and C
    If IsEmpty(Sheets("Total").Range("B2")) = True Then
        Sheets("Total").Range("B2").PasteSpecial xlPasteValues
    Else
        lrow = Sheets("Total").Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row
        Sheets("Total").Range("B" & lrow + 1).PasteSpecial xlPasteValues
    End If
Next
Application.CutCopyMode = False
 

Watch MrExcel Video

Forum statistics

Threads
1,129,816
Messages
5,638,496
Members
417,029
Latest member
lingx86

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