I'm trying to loop through my 89 worksheets and extract some data to consolidate them. I want to skip worksheet "global" and "34". I want to get the data from column A to M except row 1. I want to get those data (that are not in tables) and put them at the same place (in my code I tried to put them in a table but its optional.
I'm getting an error 400. Any suggestions?
Sub data()
Dim ws As Worksheet
Dim count As Long
Dim x As Long
Set tblref = Sheets("Global").ListObjects("ref_global")
For Each ws In ThisWorkbook.Worksheets
If Not ws.Name = "Global" Then
If Not ws.Name = "34" Then
numrows = ws.Range("A1", ws.Range("A1").End(xlDown)).Rows.count
ws.Range("a2").Select
For x = 2 To numrows
ws.Range("cells(x,1):cells(x,14)").Select
Selection.Copy
Dim lrow As ListRow
Set lrow = tblref.ListRows.Add
With lrow
.PasteSpecial
End With
Next x
End If
End If
Next ws
End Sub
CodePudding user response:
You can't use the Select method of a range on an inactive sheet. There are also several other errors in your code (like this: ws.Range("cells(x,1):cells(x,14)").Select
is not correct), and, you don't need to select or activate any ranges/sheets to perform this.
SSomething like this maybe closer to what you want to achieve:
Sub data()
Dim ws As Worksheet, tblRef As Variant, numrows As Long
Dim count As Long
Dim x As Long
Set tblRef = Sheets("Global").ListObjects("ref_global")
For Each ws In ThisWorkbook.Worksheets
If ws.Name <> "Global" And ws.Name <> "34" Then
Dim lrow As ListRow
numrows = ws.Range("A1", ws.Range("A1").End(xlDown)).Rows.count
For x = 2 To numrows
Set lrow = tblRef.ListRows.Add
lrow.Range.Value = ws.Range(ws.Cells(x, 1), ws.Cells(x, 14)).Value
Next x
End If
Next ws
End Sub
CodePudding user response:
Consolidate Data
Option Explicit
Sub ConsolidateData()
Const sFirstCellAddress As String = "A1"
Const sColumnsCount As Long = 14
Const dName As String = "Global"
Const dtblName As String = "ref_global"
Const eName As String = "34"
Dim dtbl As ListObject
Set dtbl = ThisWorkbook.Worksheets(dName).ListObjects(dtblName)
Dim dCell As Range: Set dCell = dtbl.Range.Offset(dtbl.Range.Rows.Count)
Application.ScreenUpdating = False
Dim sws As Worksheet
Dim srg As Range
Dim srCount As Long
For Each sws In ThisWorkbook.Worksheets
If StrComp(sws.Name, dName, vbTextCompare) <> 0 Then
If StrComp(sws.Name, eName, vbTextCompare) <> 0 Then
Set srg = sws.Range(sFirstCellAddress).CurrentRegion
srCount = srg.Rows.count - 1
If srCount > 0 Then
Set srg = srg.Resize(srCount, sColumnsCount).Offset(1)
dCell.Resize(srCount, sColumnsCount).Value = srg.Value
Set dCell = dCell.Offset(srCount)
End If
End If
End If
Next sws
Application.ScreenUpdating = True
MsgBox "Data consolidated.", vbInformation
End Sub