# Microsoft Office Application Help - Excel Help forum > Excel Programming / VBA / Macros >  >  Copy data from multiple sheets to single sheet

## maacmaac

I need help trying to copy data from multiple sheets to one single sheet.  I am pretty sure this is possible.  The problem I am running into is that the number of sheets at any given time is dynamic.  The numbering of the sheets is from 000 to 999 (they must be a three-digit code).  The other issue I am having is I only want to copy the rows in each sheet that have an "x" in column "A".  Also, the row in which the first "x" occurs can differ from sheet to sheet.  I have attached a copy of the spreadsheet that indicates how the spreadsheet is layed out.  Any help is appreciated.

----------


## rylo

Hi

Try this




```
Please Login or Register  to view this content.
```


rylo

----------


## stevekirk

hi rylo

what does this do please




```
Please Login or Register  to view this content.
```


steve

----------


## rylo

Steve

OutSH is a variable that is pointed to the output sheet.




```
Please Login or Register  to view this content.
```


will go to the first available cell in column A on the output sheet.




```
Please Login or Register  to view this content.
```


will grab 1 row and 17 columns




```
Please Login or Register  to view this content.
```


the value for the range grabbed by the whole of the previous string




```
Please Login or Register  to view this content.
```


basically the same sort of thing for the currently selected sheet (refer to a previous with statement) in that it takes a 1 row by 17 column block and looks at the values.

A statement along the lines of ....value = ....value effectively does a copy / pastevalues of a range of data.

I read back over that and it made sense to me.  But if it is still not clear, come back with questions.

rylo

----------


## stevekirk

thanks rylo

steve

----------


## maacmaac

I am having problems with my macro trying to consolidate data multiple sheets into one single sheet.  I think I am close but have two issues.  The first issue is the macro is only grabbing the "last row" of data from each sheet.  I want the macro to grab all data with the exception of row A.  The second issue is I want the macro to only grab data from sheets 000 to 999.  The macro is grabbing data from 4 sheets in the spreadsheet that I don't want included in the summary.  I have attached an example.  I think maybe I am making the macro more complicated than it needs to be.  Thanks for any help.




```
Please Login or Register  to view this content.
```

----------


## maacmaac

Still having trouble with macro.  It is working except I don't want to copy Row 1 of each sheet.  Can't figure out how to aviod copying this row.  Any help is appreciated.  I have attached spreadsheet again with new macro.



```
Please Login or Register  to view this content.
```

----------


## rylo

Hi

Try this




```
Please Login or Register  to view this content.
```


rylo

----------


## acsishere

The macro which you created doing a great job! 

I do needed this macro to consolidate sheets, which have both numerical & text based sheet names (just like 41,42,43 or CL4A, CL4C, CL8C, and alike).   Is it will work with text based sheets also.  Can you please explain me how to include no. of sheets (or sheet names) in that macro.

Besides, I need in column A automatically serial number to be pasted.

Thanks in advance.  I appreciate your help.

acsishere.

----------


## acsishere

The above macro which you created doing a great job! 

I do needed this macro to consolidate sheets, which have both numerical & text based sheet names (just like 41,42,43 or CL4A, CL4C, CL8C, and alike). Is it will work with text based sheets also?  Can you please explain me how to refer no. of sheets (or sheet names) in that macro.

Besides, I need that the macro should include the condition that, in column A automatically serial number to be pasted.

Can any one help.  Thanks in advance.

acsishere.

----------


## NBVC

acsishere,

Please do not post questions on other people's threads...

Start your own thread and post a link to this one if you feel it relevant.

Thank You.

----------


## acsishere

I am sorry for posting query on others questions.

Thanks for the guidance.  Errors only makes human being perfect.  

However, as per your suggestion, I posted a new thread which required your kind help.

Thanks.

acsishere.

----------


## Ananta

Hi Rylo,

I have been searching exactly for the same macro. But I am facing a problem in using this. I am trying to consolidate a lot of text fields; a few cells have text length more 1500 characters. When I run this macro I get this run time error "1004"

Could you please help me with a work around for this problem.

Thanks in advance
Ananta

----------


## rylo

Ananta

Please do not post your question in another thread.

Create a new post of your own, and if you think there is some relevance, then put a link to this post.

rylo

----------


## NBVC

kn427,

_Your post does not comply with Rule 2 of our Forum_ RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.

and

_Your post does not comply with Rule 3 of our Forum_ RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

----------


## Paul

PB,

Please do not post your question in another thread.

Create a new post of your own, and if you think there is some relevance, then put a link to this post.

Thank you.

----------

