Copy cells without error

[alexbat]

Trying to copy data from Input sheets to a Summary sheet with this code:

Dim Sh As Worksheet
   For Each Sh In ActiveWorkbook.Worksheets
        If Sh.Name Like "Inp*" Then
            With Sh
                .Range("A77:A146").Select
                Selection.SpecialCells(xlCellTypeFormulas, 7).Select
                Range(Selection, Selection.End(xlToRight)).Select
                Selection.Copy
                Sheets("TotalHCList").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues
            End With
        End If
    Next Sh

Getting error code 1004, Select method of Range Class failed,
for line

"With Sh.Range("A77:A146").Select"

What am I doing wrong?

 

[bewsh1987]

Hi there

you need your code to select the sheet first

Dim Sh As Worksheet
   For Each Sh In ActiveWorkbook.Worksheets
        If Sh.Name Like "Inp*" Then
            With Sh
                .select ' select sheet here
                .Range("A77:A146").Select
                Selection.SpecialCells(xlCellTypeFormulas, 7).Select
                Range(Selection, Selection.End(xlToRight)).Select
                Selection.Copy
                Sheets("TotalHCList").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues
            End With
        End If
    Next Sh

 

[alexbat]

I´m afraid that is not the solution... It only copies the first sheet named "Inp*" and a completely
wrong range, I2:W2

 

[bewsh1987]

I´m afraid that is not the solution... It only copies the first sheet named "Inp*" and a completely
wrong range, I2:W2

ok no probs thought that may of been the reason you was getting the error message as it need to select a sheet for this to work let me have a look at the code in detail

and this isnt what you initially asked the code work fine for me.

may need a little more detail on this

 

[alexbat]

I have multiple sheets named Input 1, Input 2 and so on... From each of these sheets I want to copy
A77:X146 but only those lines where cell A does not contain an error. Then paste the data from
each input-sheet into the sheet "TotalHClist", starting from cell A2 and in the next empty row.

Does this make sense?

 

[bewsh1987]

there is no issue with the code i made as may differnt types of errors i can and i will only select the lines that have values

the code then paste this into sheet TotalHCList starting from B2. have you changed the error to a true value like #N/A to N/A maybe

Dim Sh As Worksheet
   For Each Sh In ActiveWorkbook.Worksheets
        If Sh.Name Like "Inp*" Then
            With Sh
                .Select
                .Range("A77:A146").Select
                Selection.SpecialCells(xlCellTypeFormulas, 7).Select
                Range(Selection, Selection.End(xlToRight)).Select
                Selection.Copy
                Sheets("TotalHCList").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues
            End With
        End If
    Next Sh
End Sub

</PRE>

 

[njimack]

You don't need to select sheets or cells to work with them, particularly since you've already put in place the With/End With structure. I think the below should work for you:

Sub test()
Dim Sh As Worksheet
   For Each Sh In ActiveWorkbook.Worksheets
        If Sh.Name Like "Inp*" Then
            With Sh
                .Range("A77:A146").Resize(,.Range("A77").End(xlToRight).Column).SpecialCells(xlCellTypeFormulas, 7).Copy
            End With
                Sheets("TotalHCList").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlValues
        End If
    Next Sh
End Sub