Solved

Excel replacing data

Posted on 2014-11-25
8
121 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
  • 4
  • 3
8 Comments
 
LVL 25

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 25

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
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 

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 25

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 500 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 25

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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

This article will shed light on the latest trends when it comes to your resume building needs. For far too long, the traditional CV format has monopolized the recruitment market.
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
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…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

813 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

17 Experts available now in Live!

Get 1:1 Help Now