VBA Help (Newbie)

[JMcAnarney]

Question,

What is the VBA code I would need to write in order to create a long list from all the sheets on my excel document. For example, I need to compile (similar to a data set) the cell range of L49 to AD49 down to L62 to AD62. I am doing it this way in order to compile out large data set in hopes to create a PIVOT table. The other way would be copying and pasting which would take far too long.

The code I have established combines ALL the sheets information which is what I do not want; just that specified range:

Sub Combine()
'UpdatebyExtendoffice20180205
Dim I As Long
Dim xRg As Range
On Error Resume Next
Worksheets.Add Sheets(1)
ActiveSheet.Name = "Combined"
For I = 2 To Sheets.Count
Set xRg = Sheets(1).UsedRange
If I > 2 Then
Set xRg = Sheets(1).Cells(xRg.Rows.Count + 1, 1)
End If
Sheets(I).Activate
ActiveSheet.UsedRange.Copy xRg
Next
End Sub



Any help?

 

[JMcAnarney]

Yes, sorry for confusion. I probably meant the long way as opposed to MACRO would be to copy and paste. I apologize for not being clear!

 

[Fluff]

If you want a link, I think you would need to loop through every cell in the range for every sheet, which is likely to be slow.
Why can't you just have the values on the master sheet?

 

[JMcAnarney]

Actually values are fine becuase if they were to change I would just need to rerun macro. Sorry for confusion

 

[Fluff]

Ok, how about

Sub JMcAnarney()
   Dim Ws As Worksheet, Nws As Worksheet
   
   If Evaluate("isref('Master Summary'!A1)") Then
      Set Nws = Sheets("Master Summary")
   Else
      Set Nws = Sheets.Add(Sheets(1))
      Nws.Name = "Master Summary"
   End If
   For Each Ws In Worksheets
      If Ws.Name <> "Master Summary" Then
         Nws.Range("A" & Rows.Count).End(xlUp).Offset(1).Resize(14, 19).Value = Ws.Range("L49:AD62").Value
      End If
   Next Ws
End Sub

 

[JMcAnarney]

THANK YOU!

 

[Fluff]

You're welcome & thanks for the feedback.