Solved

Excel replacing data

Posted on 2014-11-25
8
122 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 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
Easy, flexible multimedia distribution & control

Coming soon!  Ideal for large-scale A/V applications, ATEN's VM3200 Modular Matrix Switch is an all-in-one solution that simplifies video wall integration. Easily customize display layouts to see what you want, how you want it in 4k.

 

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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying 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

A high-level exploration of how our ever-increasing access to information has changed the way we do our jobs.
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.
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 …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

790 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