VBA - Retrieve data from multiple worksheets

[JeanRene]

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?

 

[KennyGreens]

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]

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.

 

[KennyGreens]

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?

 

[JeanRene]

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

 

[JeanRene]

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

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]

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

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

 

[JeanRene]

Thanks KennyGreens for your input and time!

 

[KennyGreens]

No problem, sorry I couldn't get it to you sooner before you found an answer!