Solved

Excel cut/paste vba

Posted on 2013-01-24
8
314 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:W.E.B
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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:W.E.B
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
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 

Author Comment

by:W.E.B
ID: 38815336
Hello,
Please see attached sample.
Check sheet4.

that's the only thing it is doing.

thanks,
Book1.xlsx
0
 
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:W.E.B
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:W.E.B
ID: 38815843
Thank you very much.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

751 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