Getting error 400 looping through worksheets to get datas

Question

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

On which line do you get the error? – tinazmu May 03 '22 at 03:01 — tinazmu, May 03 '22 at 03:01

score 1 · Accepted Answer · answered May 03 '22 at 03:35

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

score 1 · Answer 2 · answered May 03 '22 at 08:47

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

Thank you for your time vbasic2008. I tried the code from tinazmu first as it was shorter and it worked. — grigri, May 03 '22 at 13:16

Getting error 400 looping through worksheets to get datas

2 Answers2

Consolidate Data