Solved

Excel cut/paste vba

Posted on 2013-01-24
8
285 Views
Last Modified: 2013-01-24
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
Comment
Question by:Wass_QA
  • 4
  • 4
8 Comments
 
LVL 13

Expert Comment

by:Shanan212
ID: 38815232
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
 

Author Comment

by:Wass_QA
ID: 38815247
Hello,
What about my code,
it doesn't seem to work.
it only works on one sheet. (Active sheet).

thanks
0
 
LVL 13

Expert Comment

by:Shanan212
ID: 38815291
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
 

Author Comment

by:Wass_QA
ID: 38815336
Hello,
Please see attached sample.
Check sheet4.

that's the only thing it is doing.

thanks,
Book1.xlsx
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 13

Expert Comment

by:Shanan212
ID: 38815508
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
 

Author Comment

by:Wass_QA
ID: 38815591
Hello,

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

this note is pasting at H9.

Thanks
0
 
LVL 13

Accepted Solution

by:
Shanan212 earned 250 total points
ID: 38815762
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
 

Author Closing Comment

by:Wass_QA
ID: 38815843
Thank you very much.
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

A2 = A1 That kind of cell reference is relative.  If you copy it from A2 to B2, then B2 will get this: B2 = B1 That's all fine and good, but if you then insert a new row above row 2, you'll find: A3 = A1 B3 = B1 This is intentional. …
Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now