Copy cells without error

alexbat

New Member
Joined
Dec 12, 2013
Messages
32
Trying to copy data from Input sheets to a Summary sheet with this code:

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?
 

Some videos you may like

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

bewsh1987

Board Regular
Joined
Sep 3, 2013
Messages
232
Hi there

you need your code to select the sheet first



Code:
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

New Member
Joined
Dec 12, 2013
Messages
32
I´m afraid that is not the solution... It only copies the first sheet named "Inp*" and a completely
wrong range, I2:W2
 

bewsh1987

Board Regular
Joined
Sep 3, 2013
Messages
232
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
 
Last edited:

alexbat

New Member
Joined
Dec 12, 2013
Messages
32

ADVERTISEMENT

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

Board Regular
Joined
Sep 3, 2013
Messages
232
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

Code:
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

Well-known Member
Joined
Jun 17, 2005
Messages
7,764
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:

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,657
Messages
5,597,391
Members
414,142
Latest member
Banyangt

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top