Macro to copy data from multiple worksheets into 1 worksheet

[Amstrad]

I have a spreadsheet that has hundreds of different worksheets. Within each worksheet there is a single row of data from cell A2 to J2.

I want to copy this row from each worksheet into a single worksheet. I also want it to copy the name of the worksheet into column A and then paste the data next to it. That way, I will be able to see which worksheet the data is for.

Any help on how I can build a macro to loop through each worksheet and do this would be greatly appreciated.

Thanks for your help.

 

[My Aswer Is This]

Try this:

Dim rConstants As Range
Set rConstants = Range("A2", Cells(LastRow, "M")).SpecialCells(xlCellTypeConstants)
    rConstants.ClearContents

I'm not really a fan of helping people modify scripts that were written years ago and hoping they will do what they want. You never actually said what you want you just want this script modified.

But try this and see if it works. Now this will delete the values in the ranges this formula refers to.

 

[erobin2003]

Hello,
Admittedly I'm pretty new to macros, but I'm trying. This topic is almost exactly what I need for some spreadsheets, with some additions. My spreadsheets have to be locked, and we we already are using a macro to copy a master copy. Basically our users open the spreadsheet and use the first macro to make a copy of the Master each time we need a new worksheet, so I need that page to not be copied as well as it will be blank. I thought I had both issues addressed but I keep getting some errors that I'm not sure how to fix. I get an unknown identifier for what I thought would work for the 2 separate names (pretty sure it reads as "not "this" or "that""). I get a error saying "Cannot jump to 'ActiveSheet' because it's hidden" when trying to do the last paste for what I came up with to address the locked pages issue. I made sure the worksheet all the data is going to is unlocked and even tried it with the other sheets unlocked to test if it was jumping to another sheet and I still get the same error. I'm quite perplexed as I can't see where the code is going wrong.

Here is what I have (I've been testing the 2 issues separately, so I only have to change the ranges eventually, so forgive me if I miss something when combining them)

Dim Sht As Worksheet
For Each Sht In ActiveWorkbook.Worksheets
If Sht.Name <> ("MASTER COPY") And ("Master Control") Then
Sht.Select
Range("V8").Copy
Sheets("Master Control").Select
Range("AB2").PasteSpecial Paste:=xlPasteValues
Sht.Select
Range("A2:A14").Copy
Sheets("Master Control").Select
Range("AC2").PasteSpecial Paste:=xlPasteValues
Range("AB:AC").Copy
Range("A65536").End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste
Sht.Select
Range("AB:AC").Delete
Else
End If
Next Sht


End Sub

In red is the pasting part that keeps giving me the error. What I thought to do to get around the issue of the pages the data are on being locked is to have a cell out of the way display the name (in this test spreadsheet "V8"), copy that paste to part of the Control Master then go back and copy the data and paste that next to the name. Then copy both column and past at the beginning of the sheet. And it works up until pasting the columns with the name and data. I've tried (at least I think that's what I command I used did providing I was reading it correctly) telling it to go to the right sheet again, and even the paste values only command but still no luck. I've also tried various ways to organize the name part with parenthesis, but I still the unknown identifier issue. I also made sure that the sheets I don't want copied were named and saved prior to putting in the macro.

Also our data is in columns instead of a row, but I am really not sure there's away to get the tab name to copy for each data entry. I think I have an idea to help users sort it but unfortunately not automatically, but it's better than what we currently have.

I'm really hoping someone can help me understand where I went wrong, and help me get it working.

 

[erobin2003]

I realized in combining I missed that the "("MASTER COPY") And ("Master Control")" Should be "("MASTER COPY") Or ("Master Control")"

Sorry, I did figure out it needed to be "or" (at least I think that's the case), just for forgot to fix it when I combined it

 

[winderkent]

Hi All,

Need some help. I have modified the codes and managed to paste some of the stuffs I need. However I am getting #REF !, as these copied are using SUM function in their own sheets.

Anyone can advice what I can do? Thanks.

 

[Antho1965]

Ok I have a similar issue but my data is alpha based. I am a songwriter and have put together a spreadsheet of titles etc for future work.

I have several sheets in a workbook with the same 3 columns...

Number(A) - Title(B) - Basic Idea(C)

I have broken them down into genre types for my own benefit and have a master list of all the titles in all the worksheets. Country, Rock, Pop, etc...there are 12 tabs in all.

I already have a macro to automatically sort on input of a new item input into row B of the master list.

What I want to do is be able to add a song title to one of these 12 worksheets and have it automatically be added to the Master worksheet. I am guessing from that point the existing macro will then automatically sort it as if it was input into that sheet by hand...

Let me know if this is possible and I would like to learn more about macros etc so would it be possible to sort of explain what each line does...that way maybe I can do stuff on my own in the future.

Thanks

Tony

 

[hazz1234567]

How can I modify the code to get basically the same results as below only this time with more than one row to copy from each worksheet?

This should work for you.

Just add a sheet in to your workbook called Master. Make sure you save the file first or the sheet names will not pull through

Sub CombineData()
Dim Sht As Worksheet
For Each Sht In ActiveWorkbook.Worksheets
If Sht.Name <> "Master" Then
Sht.Select
Range("A:A").Insert
Range("A2").Formula = "=Mid(Cell(""filename"",B1),Find(""]"",Cell(""filename""))+1,255)"
Range("A2").Copy
Range("A2").PasteSpecial Paste:=xlPasteValues
Range("A2:K2").Copy
Sheets("Master").Select
Range("A65536").End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste
Sht.Select
Range("A:A").Delete
Else
End If
Next Sht
 
 
End Sub

 

[MondayFriday]

Hello - I have a similar situation where I need to copy data from multiple worksheets into one worksheet. All worksheets have different tab names and different amount of rows but the columns are the same amount and range from A to L. I need the Master worksheet to have the name from each tab inserted into column A when copying the data from each worksheet. I do not need the data to be deleted from the tabs

Thanks in advance for the help