Runtime Error when trying to Loop Worksheets

0 votes
Runtime Error 1004 was encountered, but the issue has not been resolved.

However, it doesn't loop to another sheet or function when I click run in a different sheet. It only functions when I click run on the XD1111X sheet. I would value any advice.
Mar 19, 2023 in Others by narikkadan
• 63,420 points
194 views

1 answer to this question.

0 votes

Copy To Multiple Worksheets

A Quick Fix

Option Explicit

Sub LoopWorksheetsAndRunVBA()
    
    Dim ws As Worksheet

    For Each ws In ThisWorkbook.Sheets(Array("XD1111X", "XD2222X", "XD3333X"))
        EachVeh ws
    Next ws

End Sub

Sub EachVeh(ByVal dws As Worksheet)

    Dim Response As Long: Response = MsgBox("Clear data?", vbYesNo, "CAUTION")
    If Response = vbNo Then Exit Sub
    
    Dim sws As Worksheet: Set sws = dws.Parent.Sheets("CALCULATE")
    Dim srg As Range
    Set srg = Intersect(sws.Range("A1:A5000"), sws.UsedRange)
    
    Dim dfCell As Range: Set dfCell = dws.Range("A2")
    Dim dName As String: dName = dws.Name
    dws.Range("A2:F1000").ClearContents

    Dim sCell As Range

    For Each sCell In srg.Cells
        If StrComp(CStr(sCell.Value), dName, vbTextCompare) = 0 Then
            sCell.Resize(, 7).Copy dfCell
            Set dfCell = dfCell.Offset(1)
        End If
    Next sCell

End Sub
answered Mar 19, 2023 by Kithuzzz
• 38,010 points

Related Questions In Others

0 votes
1 answer

I am trying to run following command But I end up with an error :

Hii Nishant, You are running this command inside ...READ MORE

answered Apr 6, 2020 in Others by Niroj
• 82,880 points
1,587 views
0 votes
0 answers
0 votes
1 answer

It shows black screen when trying to load Map on device with ionic 2 Google Map Native plugin

In order to answer your question, start ...READ MORE

answered Feb 8, 2022 in Others by Rahul
• 9,670 points
1,432 views
0 votes
1 answer

Python Error when I'm converting Excel to JSON

The "Birth Year" value in the p ...READ MORE

answered Feb 5, 2023 in Others by narikkadan
• 63,420 points
510 views
0 votes
1 answer

How can I stop my vba code from giving me an error 424?

Object Variables in Loops The main issue was ...READ MORE

answered Feb 9, 2023 in Others by narikkadan
• 63,420 points
524 views
0 votes
1 answer

Sort Excel worksheets based on name, which is a date

Sorting sheets of a workbook are rather ...READ MORE

answered Oct 17, 2022 in Others by narikkadan
• 63,420 points
372 views
0 votes
1 answer

VBA Loop To Import Changing File Names

You can use a FOR loop and ...READ MORE

answered Jan 12, 2023 in Others by narikkadan
• 63,420 points
569 views
0 votes
1 answer

Trying to run different macros based on value of cell in a range

This demonstrates one approach to loop through ...READ MORE

answered Jan 19, 2023 in Others by narikkadan
• 63,420 points
273 views
0 votes
1 answer

Type mismatch error when referring to array element by location vba

We utilise MID to parse the string. ...READ MORE

answered Feb 11, 2023 in Others by Kithuzzz
• 38,010 points
536 views
0 votes
1 answer

VBA runtime error 1004 when copy and pasting

Try this: Sub CopyWorksheet() Dim ...READ MORE

answered Feb 23, 2023 in Others by Kithuzzz
• 38,010 points
632 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP