Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 326
  • Last Modified:

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
0
W.E.B
Asked:
W.E.B
  • 4
  • 4
1 Solution
 
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
 
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
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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
 
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
 
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.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now