Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Excel replacing data

Posted on 2014-11-25
8
Medium Priority
?
133 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 27

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 27

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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

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 27

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 27

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Gain an elementary understanding of Blockchain technology.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
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…
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…

580 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