Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 322
  • 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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