1

I am using some code (found via: http://www.extendoffice.com/documents/excel/1184-excel-merge-multiple-worksheets-into-one.html) to merge 5 sheets, each with 1000 rows.

Sub Combine()
Dim J As Integer
On Error Resume Next
Sheets(1).Select
Worksheets.Add
Sheets(1).Name = "Combined"
Sheets(2).Activate
Range("A1").EntireRow.Select
Selection.Copy Destination:=Sheets(1).Range("A1")
For J = 2 To Sheets.Count
Sheets(J).Activate
Range("A1").Select
Selection.CurrentRegion.Select
Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select
Selection.Copy Destination:=Sheets(1).Range("A65536").End(xlUp)(2)
Next
End Sub

This seems to work perfectly, other than I need to have the sheets combined using a 'paste as values' methodology (as my individual sheets have many formulas, and I just want to combine them as text versions).

Can this code be easily changed to 'paste as values'?

Thanks.

Community
  • 1
  • 1
egg
  • 373
  • 1
  • 7
  • 15

1 Answers1

1
Sub Combine()
Dim J As Integer
On Error Resume Next
Sheets(1).Select
Worksheets.Add
Sheets(1).name = "Combined"
Sheets(2).Activate
Range("A1").EntireRow.Select
Selection.Copy Destination:=Sheets(1).Range("A1")
For J = 2 To Sheets.Count
Sheets(J).Activate
Range("A1").Select
Selection.CurrentRegion.Select
Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Copy
Sheets(1).Range("A65536").End(xlUp)(2).PasteSpecial xlPasteValues
Next
End Sub

Does this work? If so, we can get working on removing .select to make this a little bit more "tight". I already did it on the .Copy line (can you see what I did?)

Edit: This almost gets there - I think you'll hit an issue on the pasting part, but I can fix that. Just tell me, in your original code, what is the CurrentRegion that you're selecting? What's trying to be copy/pasted?

Edit 2: Okay, I think I've got it finally. The issue is your using Sheets(1), Sheets(2). I don't know how your document is, but the following works with these assumptions: You have the "unchanging" sheet active (this is the sheet with your magical formulas). Just have this active and run the macro below. 

Sub Combine()
Dim J As Integer, noRows As Integer
Dim ws1 As Worksheet, ws2 As Worksheet, magicWS As Worksheet

' Note, you need to have the worksheet where you do all of your formulas open and be the active sheet.
Set magicWS = ActiveSheet

Set ws1 = Sheets.Add(after:=magicWS)
ws1.Name = "Combined"

On Error Resume Next

'Now, I assume that your main (unchanging) worksheet is the FAR LEFT most
'Then, the second worksheet is the new "Combined".  If you look along the bottom, every worksheet RIGHT of "Combined" will need
'to be added to this WS.

'First, let's get the headers from the third sheet:
ws1.Cells.Rows(1).Value = Sheets(3).Cells.Rows(1).Value

'Now, let's add the data to "Combined"!
For J = 3 To Sheets.Count
    noRows = Sheets(J).Range("A1").CurrentRegion.Rows.Count
    Sheets(J).Range("A1").CurrentRegion.Offset(1, 0).Resize(noRows - 1).Copy
    ws1.Range("A65536").End(xlUp)(2).PasteSpecial xlPasteValues
Next J

Application.CutCopyMode = False

End Sub
Community
  • 1
  • 1
BruceWayne
  • 22,923
  • 15
  • 65
  • 110
  • but maybe you can tell... I know nothing about vba - so dont understand the impact of removing the .select etc... I'll trust your judgement! :) – egg Aug 04 '15 at 19:57
  • 1
    Basically, `.Select` literally selects the cell/whatever it is (range, cell, etc.). You can see this happen if you step through your Macro with F8. Each time `.select` is used, Excel actually selects that cell. This can cause issues if you do anything with the mouse while your macro is running, or want to switch sheets, etc. It's better to use direct references to your data. I will update my solution to eliminate the `.select` as best I can. But it's very much worth checking that thread (and Google "Avoid select and activate with VBA"). – BruceWayne Aug 04 '15 at 20:28
  • 1
    many thanks Batman! Indeed, I'll check it out... I have the feeling this will be the start of a whole new adventure in Excel VBA for me :) – egg Aug 04 '15 at 20:31
  • regarding the latest update, to be honest, I don't know about the current region.. other than it works. Basically I have 5 sheets, each with the same structure and same amount of rows etc. Each sheet is customised for a different language (for website uploads). The original macro magically pulled each sheet and combined into one. Too be honest... you'll know better than me the rest... – egg Aug 04 '15 at 20:40
  • update: latest edit only copy/pastes the first sheet... something broke! – egg Aug 04 '15 at 20:42
  • Ah, sorry - I think I see what happened. Try the updated code I just put in there. – BruceWayne Aug 04 '15 at 20:45
  • thanks.. but i notice that it removes the first sheet (or renames?) and doesn't combine the rest... so no change. Regarding the header - the one from the first sheet would be fine. Just to be sure... the first sheet needs to stay untouched, as thats where the formula magic happens etc. The orignal tweak from you worked perfectly... maybe I can live without the elimination of the.select... ! :) – egg Aug 04 '15 at 20:53
  • 1
    Hey @egg, the `.select` issue is my own errors. If you plan on using VBA much in the future, I highly recommend looking in to that. Now I see what you're trying to do - I'll revisit my code. – BruceWayne Aug 04 '15 at 20:56
  • Thanks... im off to sleep now, but will check back tomorrow. Thanks again for helping out... you already solved my issue - and I'm grateful that you try to improve the code! – egg Aug 04 '15 at 20:58
  • No worries! I updated it (I missed your `sheets.add` at the beginning). This should work a little better, although I forsee an issue with the sheets. Using `Sheets(1)` doesn't refer necessarily to "Sheet1", but the first sheet along the tab bar on the bottom. What about if you start on your "Sheet1" (the one that doesn't change), add a sheet after that (this would become "Combined" sheet), and then throw everything on "Combined". Do your sheets have names at all? Or is it just "Sheet1" and "Sheet2"? – BruceWayne Aug 04 '15 at 21:08
  • Updated, please read the assumptions I made, this worked for me! – BruceWayne Aug 04 '15 at 21:25
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/85168/discussion-between-egg-and-brucewayne). – egg Aug 05 '15 at 06:26