Combine 3 spreadsheets

Vbanoob98

New Member
Hey!

I need to combine 3 spreadsheets into a big one but I dont know how long each one will be. What I have so far is a short macro that merges them together and then deletes the blank rows between each other, but his seems very clunky.

Anyone has a quick fix for this?

Thanks!
 

Vbanoob98

New Member
I need to be able to pick the name of the spreadsheets that should be combined. So I do have a vba code that combines all but I want to be able to write the names of the ones I want
 

mohadin

Active Member
Hi
with no details about your sheets layout, Try to amend this code to meet yours
Code:
Sub cons()
    Dim c As New Collection
    Dim a, b As Variant
    ReDim d(1 To ThisWorkbook.Sheets.Count)
    i = 1
1   sh = InputBox("enter sheet name", "collect sheet name", , , 2)
    If sh <> "" Then
        Sheets(sh).Activate
        a = Cells(6, 1).CurrentRegion.Offset(1).Value
        c.Add a
        d(i) = Array(c)
        i = i + 1
        GoTo 1
    End If
    ActiveWorkbook.Sheets.Add(After:=Worksheets(Worksheets.Count)).Name = "whatever"
    Sheet1.Range("a5:q5").Copy Sheets("whatever").Range("a1")
    l = 1
    For i = 1 To UBound(d)
        Sheets("whatever").Range("a" & l + 1, "q" & l + UBound(d(i)(0)(i)) - 1) = d(i)(0)(i)
        l = l + UBound(d(i)(0)(i)) - 1
    Next
End Sub
 
Last edited:

Vbanoob98

New Member
Cant seem to make it work.

I have 3 sheets. Sheet1, sheet2 and sheet3. I need to append all rows with data to a new spreadsheet
 

mohadin

Active Member
well
Try to change
Code:
 a = Cells(6, 1).CurrentRegion.Offset(1).Value
To
Code:
a = Cells(1, 1).CurrentRegion.Value
 

Vbanoob98

New Member
I think I need to keep studying. Can't figure it out

The code asks me to input something and then it crashes saying object not found

My data is in a1:b2 in every sheet
 

mohadin

Active Member
Aha
asking you to enter the sheets name one by one
the sheet name you enter should be correct
Ok?
If not working
you may upload a sample file and give the link dropbox one drive
 
Last edited:

Vbanoob98

New Member
Yes. I enter the names but it returns a object requiered error.

On Sheet1.Range(a5:q5).Copy Sheets ("whatever").Range("a1")
 

mohadin

Active Member
All right
This line can be deleted cause it copy the headers from what is called sheet1 to the new add sheet
and as you mentioned earlier no need for that line of code
Good luck
 

Vbanoob98

New Member
Oh its working now. But its copying from Column A to Column Q, how can I select which column to stop at?

I'm trying to understand the code but it seems very high level
 

Some videos you may like

This Week's Hot Topics

Top