Can anyone help a newbie with a simple macro?

Jo-AnneC

New Member
Joined
Jan 25, 2005
Messages
13
I hope someone can help me to get this to work. It only works on the first sheet in the workbook, so I must have done something really stupid.... I have been trying to get it to work for hours and hours....ughhh!

Sub TestFile()
For Each Sh In ThisWorkbook.Worksheets
With Sh
Range("B3:K12").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues
Range("N3").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues
Range("M8:M12").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues
Range("B19:N23").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End With
Next Sh
End Sub

Jo-Anne
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

martinee

Well-known Member
Joined
Nov 4, 2003
Messages
960
In your with block, you then need to reference your sheet with a dot (.) i.e.

With Sh
.Range("B3:K12")
'etc....
End With

HTH.
 

OdinsDream

Well-known Member
Joined
May 19, 2002
Messages
541
I don't think you can select anything on a worksheet that isn't currently active. Try doing this:

Code:
Sub TestFile()
For Each Sh In ThisWorkbook.Worksheets
Worksheets(Sh).Select
Range("B3:K12").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues
Range("N3").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues
Range("M8:M12").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues
Range("B19:N23").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Next Sh
End Sub
 

martinee

Well-known Member
Joined
Nov 4, 2003
Messages
960
Sh is probably declared as a worksheet, so you probably have to do something like:

Worksheets(Sh.Name).Activate

Or more simply:

Sh.Activate

HTH
 

OdinsDream

Well-known Member
Joined
May 19, 2002
Messages
541

ADVERTISEMENT

Ah, good catch, you're probably right there.
 

Nimrod

MrExcel MVP
Joined
Apr 29, 2002
Messages
6,259
Sub TestFile()
For Each Sh In ThisWorkbook.Worksheets
With Sh
.Range("N3:W12").Value = Range("B3:K12").Value
.Range("M8:M12").Copy
.Range("M8:M12").PasteSpecial Paste:=xlPasteValues
.Range("B19:N23").Copy
.Range("B19:N23").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End With
Next Sh
End Sub
 

Smitty

Legend
Joined
May 15, 2003
Messages
29,536

ADVERTISEMENT

Welcome to the Board

How about:

Code:
Sub TestFile()
    Dim Sh As Worksheet
        For Each Sh In ThisWorkbook.Worksheets
            Sh.Activate
            With Range("B3:K12")
                .Copy
                .PasteSpecial Paste:=xlPasteValues
            End With
            With Range("N3")
                .Copy
                .PasteSpecial Paste:=xlPasteValues
            End With
            With Range("M8:M12")
                .Copy
                .PasteSpecial Paste:=xlPasteValues
            End With
            With Range("B19:N23")
                .Copy
                .PasteSpecial Paste:=xlPasteValues
            End With
    Next Sh
    Application.CutCopyMode = False
End Sub
Hope that helps,

Smitty
 

Jo-AnneC

New Member
Joined
Jan 25, 2005
Messages
13
martinee said:
In your with block, you then need to reference your sheet with a dot (.) i.e.

With Sh
.Range("B3:K12")
'etc....
End With

HTH.
Thank you. I have tried this now, but still could not get it to work. Maybe have dodgy fingers...
 

Jo-AnneC

New Member
Joined
Jan 25, 2005
Messages
13
Whooo ha! It works and I've got a bit giddy
Thank you soooooooooooo much Smitty!

What a crew!
Will be playing about with the other things that people so kindly sent to me. Very grateful for all of your help.
 

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,456
Since you're just copying and "paste special-ing" as values, you don't need to actually copy at all... as in:
Code:
Sub TestFile()
Dim Sh As Worksheet
For Each Sh In ThisWorkbook.Worksheets
  With Sh
    .Range("B3:K12").Value = .Range("B3:K12").Value
    .Range("N3").Value = .Range("N3").Value
    .Range("M8:M12").Value = .Range("M8:M12").Value
    .Range("B19:N23").Value = .Range("B19:N23").Value
  End With
Next Sh
End Sub

Hope it helps,
Dan

Hiya Smitty! How's things treatin' you? :biggrin:
 

Forum statistics

Threads
1,147,676
Messages
5,742,555
Members
423,737
Latest member
tom_xls

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