Combine 3 spreadsheets

[Vbanoob98]

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]

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]

Hi
with no details about your sheets layout, Try to amend this code to meet yours

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

 

[Vbanoob98]

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]

well
Try to change

 a = Cells(6, 1).CurrentRegion.Offset(1).Value

To

a = Cells(1, 1).CurrentRegion.Value

 

[Vbanoob98]

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]

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

 

[Vbanoob98]

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

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

 

[mohadin]

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]

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