vba for copy all sheets from a closed workbook to current opened sheet

[john5599]

Hi
I have workbook with 20 sheets. I want to copy all these sheets to a current open worksheet. Can someone help me to solve this problem.

Thanks in advance

 

[alansidman]

You can accomplish this by using Power Query.

https://www.google.com/search?q=Pow...i57j69i65l2.7829j0j4&sourceid=chrome&ie=UTF-8

 

[john5599]

I am using excel 2007, can it possible with excel 2007

 

[john5599]

Hello Sir,
I am using excel 2007, can it possible with excel 2007 ?

 

[alansidman]

No. It is only available on 2010 and newer.

 

[john5599]

Hi
Can someone provide a macro to solve this problem

 

[alansidman]

create a new worksheet called "Master" then try this macro

Option Explicit


Sub John()
    Dim ws As Worksheet, s1 As Worksheet
    Set s1 = Sheets("Master")    'Create this sheet before running the macro
    Dim lr As Long
    For Each ws In Worksheets
        If ws.Name <> "Master" Then
            ws.Range("A1").CurrentRegion.Copy
            lr = s1.Range("A" & Rows.Count).End(xlUp).Row
            s1.Range("A" & lr + 1).PasteSpecial xlPasteValues
        End If
    Next ws


End Sub