Solved

Excel replacing data

Posted on 2014-11-25
8
120 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
 

Author Comment

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

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Companies keep a much closer eye on costs today, so changing to new Technology – Microsoft Office 365 is the smartest move to take.
An overview on how to enroll an hourly employee into the employee database and how to give them access into the clock in terminal.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

911 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

24 Experts available now in Live!

Get 1:1 Help Now