👀 Views: 0 💬 Answers: 1 📅 Created: 2025-07-05

excel vba copy

This might be a silly question, but I tried several approaches but none seem to work... I've been working on this all day and I've looked through the documentation and I'm still confused about I've searched everywhere and can't find a clear answer. I'm trying to write a VBA macro that copies all non-blank cells from a specific range in 'Sheet1' to 'Sheet2'. However, I'm working with an behavior when executing my code, specifically: 'Method 'Range' of object '_Worksheet' failed'. I've tried multiple approaches, but none seem to work as intended. Here's the code snippet I'm currently using: ```vba Sub CopyNonBlankCells() Dim srcSheet As Worksheet Dim destSheet As Worksheet Dim cell As Range Dim destRow As Integer Set srcSheet = ThisWorkbook.Sheets("Sheet1") Set destSheet = ThisWorkbook.Sheets("Sheet2") destRow = 1 ' Loop through each cell in the defined range For Each cell In srcSheet.Range("A1:A100") If Not IsEmpty(cell.Value) Then destSheet.Cells(destRow, 1).Value = cell.Value destRow = destRow + 1 End If Next cell End Sub ``` I believe the range specified in `srcSheet.Range("A1:A100")` is correct, but when it gets to the line where I assign `destSheet.Cells(destRow, 1).Value = cell.Value`, I get the behavior. I've verified that 'Sheet2' exists and is not protected. Additionally, I've made sure the workbook is not in read-only mode. Any pointers on what might be causing this scenario? I've also added a check to ensure the destination cell is not read-only, but that didn't help either. What am I doing wrong? I'm working on a API that needs to handle this. How would you solve this? Cheers for any assistance! I've been using Vba for about a year now. Is there a simpler solution I'm overlooking? What's the best practice here?