CODEWITHSUNDEEP
vbaexcel
blau
blau

Reputation: 55

VBA copying visible cells only issues

I'm trying to execute my code but it gives me

Error 1004

Although I have used similar syntax before.

My Goal: I want to copy visible cells only from column that contains "MTD" in its first row. Visible cells I want to copy are in rows from 75 to 139 only. 

'searching for column with "MTD" in it 
Dim NumCol As Integer
Dim Column As Integer

Column = Workbooks("xx.xlsx").Sheets("Sheet1").UsedRange.Columns.Count
For i = 1 To Column
    If Workbooks("xx.xlsx").Sheets("Sheet1").Cells(1, i).Value = "MTD" Then NumCol= i
Next i

'copying visible cells only from Column with MTD and only in rows from 75 to 139
 Dim MyRange As Range

 'Line below is the debugged line
 Set MyRange = Workbooks("xx.xlsx").Sheets("Sheet1").Range(Cells(75, NumCol), Cells(139, NumCol))
 MyRange.SpecialCells(xlCellTypeVisible).Copy Destination:=Workbooks ("xy.xlsx").Sheets("Sheet2").Range("A2")

I also tried to use this, but 424 error appeared.

Dim MyRange As Range

Set MyRange = Workbooks("xx.xlsx").Sheets("Sheet1").Range(Workbooks("xx.xlsx").Sheets("Sheet1").Cells(75,  NumCol), Workbooks("xx.xlsx").Sheets("Sheet1").Cells(139, NumCol))

'This time it debugged this row (error 424)
MyRange.SpecialCells(xlCellTypeVisible).Copy Destination:=Workbooks("xy.xlsx").Sheets("Sheet2").Range("A2")

Any help would be appreciated.

Thank you :)

Upvotes: 2

Views: 901

Answers (2)

Anastasiya-Romanova 秀
Anastasiya-Romanova 秀

Reputation: 3368

Try to change this line

Set MyRange = Workbooks("xx.xlsx").Sheets("Sheet1").Range(Cells(75, NumCol), Cells(139, NumCol))
MyRange.SpecialCells(xlCellTypeVisible).Copy Destination:=Workbooks ("xy.xlsx").Sheets("Sheet2").Range("A2")

with

Set MyRange = Workbooks("xx.xlsx").Sheets("Sheet1").Range(Cells(75, NumCol), Cells(139, NumCol)).SpecialCells(xlCellTypeVisible)
MyRange.Copy Destination:=Workbooks ("xy.xlsx").Sheets("Sheet2").Range("A2")

I think that should work fine.

Upvotes: 1

user6432984
user6432984

Reputation:

If a Range object exists on a worksheet other than the ActiveWorksheet then it's cell references must also be qualified to that worksheet.

Sheet1.Range(Sheet1.Cells(1,1),Sheet1.Cells(1,10))

This is not a fully qualified reference:

Set MyRange = Workbooks("xx.xlsx").Sheets("Sheet1").Range(Cells(75, NumCol), Cells(139, NumCol))

Passing the Cells.Address as parameters to shoud fix the problem.

Set MyRange = Workbooks("xx.xlsx").Sheets("Sheet1").Range(Cells(75, NumCol).Address, Cells(139, NumCol).Address)

Sub Example()
    Dim NumCol As Integer
    Dim Column As Integer

    With Workbooks("xx.xlsx").Sheets("Sheet1")
        On Error Resume Next
        NumCol = WorksheetFunction.Match("MTD", Rows(1), 0)
        If Err.Number <> 0 Then
            MsgBox "Unable to locate MTD", vbCritical
        End If
        On Error GoTo 0
        'copying visible cells only from Column with MTD and only in rows from 75 to 139
        Dim MyRange As Range

        On Error Resume Next
        Set MyRange = .Sheets("Sheet1").Range(.Cells(75, NumCol), .Cells(139, NumCol)).SpecialCells(xlCellTypeVisible)
        If Err.Number <> 0 Then
            MsgBox "No cells were found", vbCritical
        End If
        On Error GoTo 0

        If Not MyRange Is Nothing Then

            MyRange.Copy Destination:=Workbooks("xy.xlsx").Sheets("Sheet2").Range("A2")

        End If
    End With
End Sub

Upvotes: 1

Related Questions