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?

 

[Fluff]

Where do you want to copy the 1st set of data to?

 

[JMcAnarney]

I would like to put it on a separate page called "Master Summary" which would be the first sheet of doc

 

[Fluff]

But which cell would it start in?

 

[JMcAnarney]

on the new page? A1. And the data it is pulling from is L49 to AD49 down to L62 to AD62. That help??

 

[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
         Ws.Range("L49:D49").Copy Nws.Range("A" & Rows.Count).End(xlUp).Offset(1)
      End If
   Next Ws
End Sub

 

[JMcAnarney]

Okay looks better here is what I changed it to get my data:

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
Ws.Range("L49:AD62").Copy Nws.Range("A" & Rows.Count).End(xlUp).Offset(1)
End If
Next Ws
End Sub


However the data needs to be an abolsute reference to the particular sheet it is pulling from; currently it is either just referenced to the mastersheet or it is just pulling the word its self hard coded.

 

[Fluff]

Do you need to keep the formulae, or are you happy to just have it converted to values?

 

[JMcAnarney]

What I would like is the following IF possible: all those lines = a sheet name so cell would ='121005.00'!B48 and so on and so forth for all cells.

Does that make sense?

 

[Fluff]

So you are not trying to copy data, as your OP states, you are trying to link cells on the master sheet to cells on the other sheets?