?
Solved

Excel replacing data

Posted on 2014-11-25
8
Medium Priority
?
128 Views
Last Modified: 2014-11-26
I have 30 excel spread sheets with different data on each. I have a VB script that I run to move that data to a new spread sheet every month. Now I need to change a few lines on the script on all 30 spread sheets. I there a way to use some thing like find and replace to do this for all 30 at the same time or will I have to go in to all 30 sheets and replace the data 1 at a time. any help with this would be great. I am using Excel 2007. Thank you
0
Comment
Question by:jodyreid
[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
  • 3
8 Comments
 
LVL 26

Expert Comment

by:ProfessorJimJam
ID: 40465062
Yes, there is the option of find and replace in VBA module.  I didn't see any code attached, perhaps could be easy if you attach the code.
0
 

Author Comment

by:jodyreid
ID: 40465088
I did not attach any code but here is the code that I need to replace. Thank you
Code-to-be-replaced.txt
Replacment-code.txt
0
 
LVL 26

Expert Comment

by:ProfessorJimJam
ID: 40465167
where do the the code to be replaced exist?  is it in module1 or is it in worksheet object module? or in class module?
0
Office 365 Training for IT Pros

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

 

Author Comment

by:jodyreid
ID: 40465190
It is a Private Sub CommandButton1_Click(). When I press the button it runs the VB code.
0
 
LVL 26

Expert Comment

by:ProfessorJimJam
ID: 40465229
so, is it exactly the same in all 30 workbooks?
0
 
LVL 27

Accepted Solution

by:
Glenn Ray earned 2000 total points
ID: 40466022
Another possibility would be to add the original VBA code to your Personal Macro Workbook, replace the block of code, tweak anything else as necessary to generalize for all your sheets, and then just run the code from the PMW.  It would be available to you whenever you open Excel.

BTW, here is a slightly-cleaner version of the new code:
    ' Copy Totals from AI10 to AI37
    SourceSheet.Range("AI10:AI37").Copy
    TargetFile.Activate
    TargetSheet.Range("C4").Select
    Selection.PasteSpecial Paste:=xlPasteValues

    ' copy Charge codes from B10 to B37
    SourceSheet.Range("B10:B37").Copy
    TargetFile.Activate
    TargetSheet.Range("B4").Select
    Selection.PasteSpecial Paste:=xlPasteValues
   
    'Copy Number and paste to new data file
    SourceSheet.Range("F5").Copy
    TargetFile.Activate
    TargetSheet.Range("A4:A31").Select
    Selection.PasteSpecial Paste:=xlPasteValues

    ' copy Date of services
    SourceSheet.Range("U2").Copy
    TargetFile.Activate
    TargetSheet.Range("E4:E31").Select
    Selection.PasteSpecial Paste:=xlPasteValues
    
    SourceSheet.Range("E4").Copy
    TargetFile.Activate
    TargetSheet.Range("E4:E31").Select
    Selection.NumberFormat = "m/d/yyyy"

Open in new window


Regards,
-Glenn
0
 

Author Closing Comment

by:jodyreid
ID: 40466683
Thank you.
0
 
LVL 26

Expert Comment

by:ProfessorJimJam
ID: 40467726
Nice Glenn,  your solution is effective and easy.  i almost went the long way, and when i saw your post, i just discarded it.
0

Featured Post

Office 365 Training for IT Pros

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

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

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 …
When asking a question in a forum or creating documentation, screenshots are vital tools that can convey a lot more information and save you and your reader a lot of time
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

741 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