get error VBA 400

Question

get error VBA 400

Abdullah S. Alshehri 0

I have an repeort to run with more than one sheet when I run it give error 400 as need the from VBA to collect all data in sheet in one sheet

please support to fix this issues

Abdullah S. Alshehri 0

here is the code i have used

Sub alldata()
Dim arr As Variant, lr1 As Long, lr2 As Long
Dim sh As Worksheet
Application.ScreenUpdating = False
Sheets("Report").Range("a2:am5000").ClearContents

For Each sh In Worksheets
    If sh.Name <> "Report" And sh.Name <> "Pivot Table" And sh.Name <> "Dashboard" Then
        lr1 = Sheets("Report").Cells(Rows.Count, 2).End(xlUp).Row + 1
        lr2 = sh.Cells(Rows.Count, 2).End(xlUp).Row
        
        If lr2 = 1 Then GoTo 1
        arr = sh.Range("a2:am" & lr2)
        Sheets("Report").Range("a" & lr1).Resize(lr2 - 1, 41) = arr
        Sheets("Report").Range(Cells(lr1, 41), Cells(lr1 + lr2, 41)) = sh.Name

    End If
1:
Next sh
Application.ScreenUpdating = True
End Sub

2 answers

Your answer

Abdullah S. Alshehri 0 Reputation points

2023-08-22T08:20:14.0433333+00:00

here is the code i have used

Sub alldata() Dim arr As Variant, lr1 As Long, lr2 As Long Dim sh As Worksheet Application.ScreenUpdating = False Sheets("Report").Range("a2:am5000").ClearContents For Each sh In Worksheets If sh.Name <> "Report" And sh.Name <> "Pivot Table" And sh.Name <> "Dashboard" Then lr1 = Sheets("Report").Cells(Rows.Count, 2).End(xlUp).Row + 1 lr2 = sh.Cells(Rows.Count, 2).End(xlUp).Row If lr2 = 1 Then GoTo 1 arr = sh.Range("a2:am" & lr2) Sheets("Report").Range("a" & lr1).Resize(lr2 - 1, 41) = arr Sheets("Report").Range(Cells(lr1, 41), Cells(lr1 + lr2, 41)) = sh.Name End If 1: Next sh Application.ScreenUpdating = True End Sub

Answer 1

AllenXu-MSFT 24,941 Moderator

Hi @Abdullah S. Alshehri,

The error code 400 in VBA typically means that there is an issue with the syntax of the code. It is difficult to provide a specific solution without seeing the code that is causing the error. However, based on the information provided, it sounds like you may need to consolidate data from multiple sheets into one sheet before running the report. You can do this using VBA code by copying the data from each sheet and pasting it into the consolidated sheet. Here is some sample code that you can modify to fit your specific needs:

Sub ConsolidateData()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim destRow As Long
    
    'Set the destination sheet
    Set destSheet = ThisWorkbook.Sheets("ConsolidatedData")
    
    'Loop through each sheet in the workbook
    For Each ws In ThisWorkbook.Worksheets
        'Skip the destination sheet
        If ws.Name <> destSheet.Name Then
            'Find the last row in the source sheet
            lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
            
            'Copy the data to the destination sheet
            ws.Range("A1:Z" & lastRow).Copy destSheet.Range("A" & destRow)
            
            'Update the destination row counter
            destRow = destSheet.Cells(destSheet.Rows.Count, "A").End(xlUp).Row + 1
        End If
    Next ws
End Sub

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Abdullah S. Alshehri 0

here is my code I have. made when I run the code it's give the error.


Sub alldata()
Dim arr As Variant, lr1 As Long, lr2 As Long
Dim sh As Worksheet
Application.ScreenUpdating = False
Sheets("Report").Range("a2:am5000").ClearContents

For Each sh In Worksheets
    If sh.Name <> "Report" And sh.Name <> "Pivot Table" And sh.Name <> "Dashboard" Then
        lr1 = Sheets("Report").Cells(Rows.Count, 2).End(xlUp).Row + 1
        lr2 = sh.Cells(Rows.Count, 2).End(xlUp).Row
        
        If lr2 = 1 Then GoTo 1
        arr = sh.Range("a2:am" & lr2)
        Sheets("Report").Range("a" & lr1).Resize(lr2 - 1, 41) = arr
        Sheets("Report").Range(Cells(lr1, 41), Cells(lr1 + lr2, 41)) = sh.Name

    End If
1:
Next sh
Application.ScreenUpdating = True
End Sub

Abdullah S. Alshehri 0

here the code i have used , when i have run the code it's give an error 400

ub alldata()
Dim arr As Variant, lr1 As Long, lr2 As Long
Dim sh As Worksheet
Application.ScreenUpdating = False
Sheets("Report").Range("a2:am5000").ClearContents

For Each sh In Worksheets
    If sh.Name <> "Report" And sh.Name <> "Pivot Table" And sh.Name <> "Dashboard" Then
        lr1 = Sheets("Report").Cells(Rows.Count, 2).End(xlUp).Row + 1
        lr2 = sh.Cells(Rows.Count, 2).End(xlUp).Row
        
        If lr2 = 1 Then GoTo 1
        arr = sh.Range("a2:am" & lr2)
        Sheets("Report").Range("a" & lr1).Resize(lr2 - 1, 41) = arr
        Sheets("Report").Range(Cells(lr1, 41), Cells(lr1 + lr2, 41)) = sh.Name

    End If
1:
Next sh
Application.ScreenUpdating = True
End Sub

Abdullah S. Alshehri 0 Reputation points

2023-08-22T08:19:46.0933333+00:00

.....

Answer 2

Tanay Prasad 2,250

Hi,

Here are some steps you can try-

Ensure that your workbook is not protected or doesn't restrict the code to run in any way.
Make sure that your VBA code is compatible with your current version of Excel.
Make sure your device has enough memory available to run your task.
Debug your code and find out what's wrong, identify potential errors, and fix them.

If these methods do not work for you, here is an article for your help.

Best Regards

Abdullah S. Alshehri 0

the Debug it's show here , i have tried to fix but later it's give an error 1004

 Sheets("Report").Range("a" & lr1).Resize(lr2 - 1, 41) = arr

here the full code

ub alldata()
Dim arr As Variant, lr1 As Long, lr2 As Long
Dim sh As Worksheet
Application.ScreenUpdating = False
Sheets("Report").Range("a2:am5000").ClearContents

For Each sh In Worksheets
    If sh.Name <> "Report" And sh.Name <> "Pivot Table" And sh.Name <> "Dashboard" Then
        lr1 = Sheets("Report").Cells(Rows.Count, 2).End(xlUp).Row + 1
        lr2 = sh.Cells(Rows.Count, 2).End(xlUp).Row
        
        If lr2 = 1 Then GoTo 1
        arr = sh.Range("a2:am" & lr2)
        Sheets("Report").Range("a" & lr1).Resize(lr2 - 1, 41) = arr
        Sheets("Report").Range(Cells(lr1, 41), Cells(lr1 + lr2, 41)) = sh.Name

    End If
1:
Next sh
Application.ScreenUpdating = True
End Sub

Abdullah S. Alshehri 0

here the Debug give when I open the code and try to run

     Sheets("Report").Range("a" & lr1).Resize(lr2 - 1, 41) = arr

here the full code

ub alldata()
Dim arr As Variant, lr1 As Long, lr2 As Long
Dim sh As Worksheet
Application.ScreenUpdating = False
Sheets("Report").Range("a2:am5000").ClearContents

For Each sh In Worksheets
    If sh.Name <> "Report" And sh.Name <> "Pivot Table" And sh.Name <> "Dashboard" Then
        lr1 = Sheets("Report").Cells(Rows.Count, 2).End(xlUp).Row + 1
        lr2 = sh.Cells(Rows.Count, 2).End(xlUp).Row
        
        If lr2 = 1 Then GoTo 1
        arr = sh.Range("a2:am" & lr2)
        Sheets("Report").Range("a" & lr1).Resize(lr2 - 1, 41) = arr
        Sheets("Report").Range(Cells(lr1, 41), Cells(lr1 + lr2, 41)) = sh.Name

    End If
1:
Next sh
Application.ScreenUpdating = True
End Sub

Tanay Prasad 2,250

Try this code once-

Sub alldata()
    Dim arr As Variant
    Dim lr1 As Long, lr2 As Long, lc As Long
    Dim sh As Worksheet
    Dim reportSheet As Worksheet

    Application.ScreenUpdating = False
    Set reportSheet = ThisWorkbook.Sheets("Report")
    reportSheet.Range("A2:AM5000").ClearContents

    For Each sh In Worksheets
        If sh.Name <> "Report" And sh.Name <> "Pivot Table" And sh.Name <> "Dashboard" Then
            lr2 = sh.Cells(Rows.Count, 2).End(xlUp).Row
            If lr2 > 1 Then ' Only process if there's data in the source sheet
                lc = 41 ' Always 41 columns
                lr1 = reportSheet.Cells(reportSheet.Rows.Count, 2).End(xlUp).Row + 1
                arr = sh.Range("A2").Resize(lr2 - 1, lc).Value
                reportSheet.Range("A" & lr1).Resize(lr2 - 1, lc).Value = arr
                reportSheet.Range(reportSheet.Cells(lr1, lc + 1), reportSheet.Cells(lr1 + lr2 - 1, lc + 1)).Value = sh.Name
            End If
        End If
    Next sh
    
    Application.ScreenUpdating = True
End Sub
Sub alldata()

Abdullah S. Alshehri 0 Reputation points

2023-08-22T08:16:21.0733333+00:00

it's not work give an error as pic atthached
Tanay Prasad 2,250 Reputation points

2023-08-23T07:19:02.18+00:00

https://community.spiceworks.com/how_to/195135-how-to-fix-run-time-error-1004-in-excel

I suggest you refer here to fix this error.
Tanay Prasad 2,250 Reputation points

2023-08-23T07:19:50.0866667+00:00

Let me know if something works out.
Abdullah S. Alshehri 0 Reputation points

2023-08-23T08:58:50.7533333+00:00

not work with me
Abdullah S. Alshehri 0 Reputation points

2023-08-23T09:01:28.82+00:00

even I have do the code above it's give an error in below line

reportSheet.Range("A" & lr1).Resize(lr2 - 1, lc).Value = arr

Share via

get error VBA 400

2 answers

Your answer