Can anyone help a newbie with a simple macro?

[Jo-AnneC]

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

 

[martinee]

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]

I don't think you can select anything on a worksheet that isn't currently active. Try doing this:

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]

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]

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

 

[Nimrod]

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]

Welcome to the Board

How about:

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]

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]

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]

Since you're just copying and "paste special-ing" as values, you don't need to actually copy at all... as in:

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?