LinkBack URL
About LinkBacks
I have a workbook with 260 sheets. I need to combine the data from the same range from sheets 2 thru 260 with the data on sheet 1. Are there any easier ways to do that other than copying and pasting from each individual sheet?
I have a workbook with 260 sheets. I need to combine the data from the same range from sheets 2 thru 260 with the data on sheet 1. Are there any easier ways to do that other than copying and pasting from each individual sheet?
Are you looking to combine them such as all the values in A1 from all sheets are added together in the master? Or are you looking to do something else? Your request is a little bit confusing.
Alan עַם יִשְׂרָאֵל חַי
Change an Ugly Report with Power Query
Database Normalization
Complete Guide to Power Query
Man's Mind Stretched to New Dimensions Never Returns to Its Original Form
I am looking to add the data from A3 to D46 on sheets 2 thru 260 to sheet 1, below row 46 in sheet 1.
I am still not sure of what you want. From your description, there are two scenarios that I can envision..
1. Copy all the sheets and paste them consecutively in the master starting with Row 47. ie. Sheet 2 pasted to row 47, then paste sheet 3 below that.
2. Combine the values of sheets 2 thru 260 beginning in row 47. ie. if A3 on sheet 2 is 10 and A3 on sheet3 is 15, and we only combined the two sheets the value in A47 would be 25.
Which scenario are you trying to achieve.
Item 1 requires a VBA solution, looping through each sheet and pasting.
Item 2 can be achieved using the built in Excel functionality Consolidate which is found on the Ribbon.
Please clarify.
I am trying to achieve scenario 1.
This code assumes that there is data in your "Master" sheet in cells A1 through A46 or at least the last cell holding data in column A is 46
Try this:
How to install your new code
Please Login or Register to view this content.
- Copy the Excel VBA code
- Select the workbook in which you want to store the Excel VBA code
- Press Alt+F11 to open the Visual Basic Editor
- Choose Insert > Module
- Edit > Paste the macro into the module that appeared
- Close the VBEditor
- Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)
To run the Excel VBA code:
- Press Alt-F8 to open the macro list
- Select a macro in the list
- Click the Run button
Last edited by alansidman; 01-09-2014 at 11:34 AM.
When I run the macro it simply copies the data from sheet 1, 259 times as opposed to copying sheets 2 thru 260. Could I be doing something wrong or could the code need adjusted?
My apologies. I double checked and tested my code. It had an error in it.
This should work.
Please Login or Register to view this content.
That worked. Thank you very much!
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks