Copy data from multiple worksheets into a single worksheet Hello, I\'d like to c

Question

Copy data from multiple worksheets into a single worksheet

Hello,

I'd like to copy data with the same column format from multiple worksheets into one worksheet. I could simply open each worksheet, select the desired rows, then copy/paste the rows onto my new worksheet, but I get easily confused and forget where I am. Is there a simplified way? The answers I've read referred to macros. I'm not familiar with using macros, but willing to try.

Thanks,

Nicole

PS I'm new to this ccommunity and not sure if it's for asking such basic questions. I've struggled with googling my question, but I thought this forum wouuld provide more informed pertinennt answers. Let me know if I should just start over with Excel for Dummies or if I'm in the right place.../..

Explanation / Answer

Welcome Nicole. You found a great place to ask your question. We will certainly try to help you get a solution. This is Excel for Dummies Central - with us older dummies trying to help newer dummies also become older dummies - but not as dummie about Excel.

Macros? Macros are Visual Basic for Applications (VBA) code that do things that sometimes simply cannot be done from the keyboard, or they may repeat a similar keyboard action but are 'smart' in how they do it, or sometimes they just do the heavy lifting on things that are done often in exactly the same way.

First, let's get our vocabulary on the same page. You mentioned worksheet/worksheets, and I suspect you mean workbooks, so...

Workbook - an Excel file that you open from a drive with Excel. They usually end with .xls, .xlsx or .xlsm although there are some others like .xlsb and .xlst and even other types of files like .csv files that can be opened with Excel.

Worksheet - one spreadsheet within an Excel workbook - a workbook can have from 1 to hundreds of worksheets.

Rows - are exactly that: the horizontal rows on a worksheet, they are referenced as numbers.

Columns - the vertical columns on a worksheet, normally seen referenced by letters from A through ZZ through XFD in Excel 2007 and later.

A Cell: one area on a worksheet defined by the intersection of a column and a row.

Range: one or more cells - this one gets complex because the cells in a range don't have to touch one another.

Now lets talk about your needs here.

You say you need to open several worksheets (books?) - is it the same group of workbooks all of the time, or might the list change from time to time? Or are we really talking about just 1 workbook with many sheets?

You say you need to choose one (and possibly more) sheets - is it always the same list of sheets, or again might it change from time to time?

Finally, or close to finally, when you copy rows, is it always from some specific row, such as row 2 down to where the information ends in a column? Or are you in a situation where on day 1 you might copy rows 2 to 20, and the next day need to copy from that same sheet from row 21 down to 36? And on a later day, begin at 37 and go to some other row?

If you can describe all of this to us, we can probably come up with a macro solution for you without too much pain and suffering on your part or ours.

Basically we need to know:

What rows and columns on a given sheet need to be copied to what rows/columns on what other sheet. Repeating that information for each sheet that is to be copied from.

Depending on what needs to be done each time, a macro might be built that can do the whole job for you with a single 'click' or three. If things are so variable that it can't be figured out in a macro, we might be able to set something up where you'd select the sheet and rows to be copied and then run a macro that would copy the information to where it needs to go and simply record what sheet and rows had been copied as a memory refresher for you.

Some things that a macro could ddo that might help in this case: they can find a particular entry in a column; they can determine what the last used cell (row) is in a column or on a worksheet, or even the last used column on any given row or the whole worksheet. They can even look at information and doo copy & paste without haaving to actually select the sheets that will be used, even to thee point that a sheet doesn't even have to be visibble to be used like that.

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.