I need a VBA script that will allow me to reference a cell(s) in ALL worksheets automatica

I have a workbook that has almost 1500 worksheets. Each worksheet is not set up as a table. Some cells may contain data or text. I am needing to pull specific cells in all worksheets into one worksheet. Doing this will allow me to create a table with the data that I want. If the workbook was smaller I could simply use a formula to return a cell, but I can't do this for nearly 1500 sheets.



Can someone write me a script that will allow me to pull the data/text in a specific cell in all (almost 1500 worksheet) worksheets. I was hoping if someone did at least one, I could copy and paste or figure out how to duplicate that script to all the other cells I need.



In case you need this information. The cells that I am needing are as follows:

A12, A13, A14, A15, A16, A17, A18, A19, A20

B5, B6, B7, B8, B9, B12, B13, B14, B15, B16, B17, B18, B19, B20

F6, F7, F8, F9, F10

J6, J7, J8, J9, J10

Hi, and welcome to the forum.

Here's one approach
First list all the sheet names in column A of the Consolidation sheet using the following macro.

Please Login or Register  to view this content.

The enter the cell references on row 1 starting in B1. e.g. A12, A13

Then in B2 copied across and down all 1500 rows

Formula:

Please Login or Register  to view this content.

See attached

Try this:

Please Login or Register  to view this content.

The test book:

That WORKED!!!!!!! Thank you so much!

Now, I also need to return the survey question with its response. I also need to return A12, A13, A14, A15, A16, A17, A18, A19, A20, B12, B13, B14, B15, B16, B17, B18, B19, B20. These will be in the same row as the above data did. Doing this will allow the entire survey(sheet) to be returned to one row.

The catch to this... Not every survey have all the questions answered. Some individuals skipped the question which means the 4th question on one person survey may not be the 4th question on another survey. I would need the the question returned in one cell in the row and the questions answer in the next cell in that same row (question, response).

Is there a some kind of "IF" statement saying if a cell says "Provider explained things in a way that was easy to understand" then return the response to column "M", if not then leave blank.

I've updated the example so you can see what I'm wanting for the responses. Once it's all done, I will want to split the text and data from the cells-- see the tab "final product".

Try this:

Please Login or Register  to view this content.

Is there a some kind of "IF" statement saying if a cell says "Provider explained things in a way that was easy to understand" then return the response to column "M", if not then leave blank.

With this code we check the headers for an entry (anywhere in column A) and if a match is found, enter it

Please Login or Register  to view this content.

And, you're welcome

Rule 03: Cross-posting Without Telling Us

Your post does not comply with Rule 3 of our Forum RULES. Do not cross-post your question on multiple forums without telling us about your threads on other forums.

Post a link to any other forums where you have asked the same question. If you have fewer than 10 posts here, you will not be able to post a link, but you must still tell us where else you have asked the question.

Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!

Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).

If you have less than 10 posts, do not try to copy and paste the link. Instead, type the link out in your thread.

No further help to be offered, please, until the OP has complied with this request.

Mizzou.2018

Please read my response in post 9 then do as I have asked, and I will restore your last post.

DominicB

I posted this question in another forum- MrExcel.

Originally Posted by Mizzou.2018

I appreciate your reply, but unfortunately I'm new to this and wasn't able to duplicate your results. Any more instructions or help would be appreciated. I have included a document for same. This is the same file as what I'm working with. The only changes I made were to private information and reduced worksheets from nearly 1500 to 3.

We often find that when we give an answer to what is a trivial non representative example when the solution is used with the real world data it doesn't work because of factors like string length and other stuff which is important but hasn't been mentioned. It's therefore important to show us exactly what your workbook looks like.

You didn't tell us for instance that one cell contains up to three names which need to split into three cells on the summary. You also use merged cells which is generally not a good idea and should be avoided.

I'm attaching the INDIRECT solution. You'll need to adapt it slightly and use string slicing functions to split the column B text cells into three separate word cells.

Your sample has no results in Column N but Column I is now included:

Please Login or Register  to view this content.

Altered code worked on your sample:

Please Login or Register  to view this content.

Altered code worked on your sample:

Please Login or Register  to view this content.

*And, thanks for the compliment and for the rep!

Still some editing required - do you really need those comment text labels?

Please Login or Register  to view this content.

If it's every sheet but Ideal Table then this will run:

Please Login or Register  to view this content.

You rock, thank you!

I have copied the table into a new workbook and created a table. I am now needing to de-identify the visit number (column E) and MRN (column G) and place the new de-identified number in the following column (column F and H). I have already removed duplicates so I don't have to worry about duplicate visit numbers, so there will only be one of its kind. MRN's however, may have multiple entries depending if they submitted multiple surveys and I want to keep the duplicated MRN, I just want the numbers changed. All numbers will need to be changed. Duplicates need to stay duplicated just a changed/de identified.



How can I quickly change the visit numbers (currently 8 digits) to a random number (6 digits or anything other than 8 digits)?

How can I quickly change the MRN's (currently 9 digits) to a random number (8 digits or anything other than 9 digits) and still have the MRN's stay duplicated when that person submitted more than one surveys?

See attachment.

@xladept,

xladept

Because you're a genius, I'm posting my two new questions here. Maybe you could help.


https://www.excelforum.com/excel-pro...ml#post4980855

https://www.excelforum.com/excel-pro...ml#post4980852