?
Solved

Excel Spreadsheet Automate Manual Process with Macros - Populate rows/columns from single cells

Posted on 2012-08-20
7
Medium Priority
?
311 Views
Last Modified: 2012-08-22
We have an excel spreadsheet that we receive from our client that needs to be manually cleaned up in order to be processed by our system.

The cells of E2, E3, E4 and E5 should become columns populating each row of data. Also, Columns A, B, I, J, K & L are not necessary. This is currently a manual process of cleanup. I was hoping that there was some macro type functions that might be able to do this instead.

I have included a sample of what the original spreadsheet (SpreadsheetDownloaded.xlsx) looks like upon receipt as well as how we would like the data to look for import (EndingExcelColumns.xlsx).

I have filled the spaces of the spreadsheet that we do not need with the red diagonal pattern - hopefully it isn't too obnoxious for reading. I thought it would be an easy way to show which parts of the spreadsheet are garbage versus which are needed.

Please let me know if I need to give any more information and thank you in advance for your help/support.

Sincerely,
KLB
SpreadsheetDownloaded.xlsx
EndingExcelColumns.xlsx
0
Comment
Question by:CPKGDevTeam
  • 3
  • 2
  • 2
7 Comments
 
LVL 13

Expert Comment

by:Ryan
ID: 38313751
All you're doing is simply deleting specific rows and columns?  You can actually just record a macro yourself.

The code you need is simply.


Public Sub DeleteExcess()
Range("I:L").Delete
Range("A:B").Delete
Range("1:8").Delete
Range("H:J").Delete
End Sub
0
 
LVL 8

Accepted Solution

by:
Elton Pascua earned 1000 total points
ID: 38314599
Try the attached file. It formats the opened file and creates a new file with your desired format (leaving the original untouched).

1. Click the button.
2. Browse for the file.
bookformatter.xlsm
0
 

Author Comment

by:CPKGDevTeam
ID: 38318100
I apologize. I can currently only access EE through my iPhone. Comcast claims a DNS error when I use my office network to get into the site. I will test as soon as I am able to get the macro excel file. Thank you for your patience and your support.

Sincerely,
KLB
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:CPKGDevTeam
ID: 38320434
Thanks techfanatic, your code looks great. I was able to use it successfully on the example I sent you. Then I tried to use it on some more of my workbooks and I received a "Run-time error '9': Subscript out of range".

When I debug this line is where I get a failure:
wbTarget.Worksheets("Sheet1").Copy After:=wb.Worksheets("Formatter")

If you could provide some further help, I would be very appreciative. Thank you for what you have already given to me. I have never coded for excel before and I am breaking down your example to learn from it.

Thank you,
KLB
0
 
LVL 13

Expert Comment

by:Ryan
ID: 38320510
You're missing a worksheet named "Sheet1" or "Formatter"
0
 
LVL 8

Expert Comment

by:Elton Pascua
ID: 38320752
MrBullwinkle is right. I thought your files always have "Sheet1" as the sheet containing the raw data that needs to be formatted.

Is there something common with the files, i.e.:
1. Common sheet name
2. Common sheet location.
3. Common information in sheets?

I can make this more dynamic but I would need more time. :)
0
 

Author Closing Comment

by:CPKGDevTeam
ID: 38320868
The issue is that the workbooks that we get from the client have their own macros and are protected. The original needs to be first copy and pasted into a new workbook with out the macros that have full rights to change.

This also gives me a 'Sheet1', whereas the macro enabled original excel file from the client does not have a 'Sheet1', or the ability to add sheets or rename.

Thank you for your help, this was perfect.

Sincerely,
KLB
0

Featured Post

Technology Partners: 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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
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 …
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

850 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