Excel cut/paste vba

Hello,
I need to run below codes on all sheets ( except first 3 sheets)
I also need to add on
A79 "Please note xxxxxxxxx"
H79 "Please note xxxxxxxxx"

Any help is appreciated.

Sub test1()
    Dim wks As Worksheet
    For Each wks In Worksheets
    If wks.Name <> "Sheet1" And wks.Name <> "Sheet2" And wks.Name <> "Sheet3" Then
    Range("A7:F7").Select
    Selection.Copy
    Range("H7:M7").Select
    ActiveSheet.Paste
   
   Range("A78:F147").Select
    Selection.Cut
    Range("H8:M79").Select
    ActiveSheet.Paste
    End If
Next wks
    Application.CutCopyMode = False
MsgBox "Done"
End Sub
W.E.BAsked:
Who is Participating?
 
Shanan212Commented:
So I am assuming you dont want the A79 to be copied on to H9?

Then use this

Sub test1()
    Dim wks As Worksheet
    
    For Each wks In Worksheets
    If wks.Name <> "Sheet1" And wks.Name <> "Sheet2" And wks.Name <> "Sheet3" Then
        wks.Activate
        Range("A7:F7").Select
        Selection.Copy
        Range("H7:M7").Select
        ActiveSheet.Paste

        Range("A78:F147").Select
        Selection.Cut
        Range("H8:M79").Select
        ActiveSheet.Paste
        Application.CutCopyMode = False
        Range("A79").Value = "Please note xxxxxxxxx"
        Range("H79").Value = "Please note xxxxxxxxx"
    End If
    Next wks
    
    MsgBox "Done"
End Sub 

Open in new window

0
 
Shanan212Commented:
Is this what you want?

Range("A79").value = "Please note xxxxxxxxx"
Range("H79").value = "Please note xxxxxxxxx"

Open in new window


Inset above after these lines

    Range("H7:M7").Select
    ActiveSheet.Paste

Open in new window

0
 
W.E.BAuthor Commented:
Hello,
What about my code,
it doesn't seem to work.
it only works on one sheet. (Active sheet).

thanks
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
Shanan212Commented:
Works for me...Is your worksheets named correctly?

Didnt modify it except add the code above

Sub test1()
    Dim wks As Worksheet
    
    For Each wks In Worksheets
    If wks.Name <> "Sheet1" And wks.Name <> "Sheet2" And wks.Name <> "Sheet3" Then
        Range("A7:F7").Select
        Selection.Copy
        Range("H7:M7").Select
        ActiveSheet.Paste
        Range("A79").Value = "Please note xxxxxxxxx"
        Range("H79").Value = "Please note xxxxxxxxx"
        Range("A78:F147").Select
        Selection.Cut
        Range("H8:M79").Select
        ActiveSheet.Paste
        Application.CutCopyMode = False
    End If
    Next wks
    
    MsgBox "Done"
End Sub

Open in new window

0
 
W.E.BAuthor Commented:
Hello,
Please see attached sample.
Check sheet4.

that's the only thing it is doing.

thanks,
Book1.xlsx
0
 
Shanan212Commented:
Sub test1()
    Dim wks As Worksheet
    
    For Each wks In Worksheets
    If wks.Name <> "Sheet1" And wks.Name <> "Sheet2" And wks.Name <> "Sheet3" Then
        wks.Activate
        Range("A7:F7").Select
        Selection.Copy
        Range("H7:M7").Select
        ActiveSheet.Paste
        Range("A79").Value = "Please note xxxxxxxxx"
        Range("H79").Value = "Please note xxxxxxxxx"
        Range("A78:F147").Select
        Selection.Cut
        Range("H8:M79").Select
        ActiveSheet.Paste
        Application.CutCopyMode = False
    End If
    Next wks
    
    MsgBox "Done"
End Sub

Open in new window


Try this

added below code

wks.Activate

Open in new window

0
 
W.E.BAuthor Commented:
Hello,

 Range("A79").Value = "Please note xxxxxxxxx"

this note is pasting at H9.

Thanks
0
 
W.E.BAuthor Commented:
Thank you very much.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.