0

I have 30 sheets for each day of the month. In each of them there are same columns with different data, say in the range A1:A30. So my task is to copy this range from all the sheets and past in one master sheet in different adjacent columns, ex. A1:A30, B1:B30, C1:C30 an so on.

Community
  • 1
  • 1
Nurzhan
  • 9
  • 1
  • 1
    Ctrl + C and then Ctrl + V – Luuklag Sep 06 '17 at 08:29
  • @Luuklag that's not what I would call *automate*. – Pᴇʜ Sep 06 '17 at 08:31
  • @Nurzhan Welcome to Stack Overflow. Please notice, that this is not a free coding service. Therefore please show the code you already have and tell us where you got stuck and if you got any error messages. There are many tutorials out there (1) how to *[copy from one sheet to another](https://stackoverflow.com/questions/32908099/copy-data-from-one-sheet-to-another)* and (2) how to *[find the last used column](https://stackoverflow.com/questions/16882143/find-out-the-last-used-column-in-a-given-row-excel-vba)* which give you a decent idea how to start your own code. – Pᴇʜ Sep 06 '17 at 08:33
  • What code have you tried? – Jeremy Sep 06 '17 at 08:33
  • Please read: [Why is “Can someone help me?” not an actual question?](http://meta.stackoverflow.com/q/284236) – danieltakeshi Sep 06 '17 at 16:34
  • @Luuklag thanks, man! you're so smart! Thanks guys, these are my 1st steps in VBA, actually long time ago started but gave up for a while. But now realized how important VBA is for engineers. Which book you can recommend for beginners? I'm interested more in VBA. Because I still cannot fill logics behind VBA in some cases. – Nurzhan Sep 07 '17 at 09:39

2 Answers2

1

Download and install this AddIn.

https://www.rondebruin.nl/win/addins/rdbmerge.htm

That will do the work for you, as well as many other variations of what you are doing.

enter image description here

ASH
  • 20,759
  • 19
  • 87
  • 200
0

This is a general code for copying ranges:

Sub Copy_ranges()

   Dim NS As Worksheet

   Application.ScreenUpdating = False
   Application.EnableEvents = False

   Set NS = Sheets.Add

   i = 1
   refRange = "A1:D10"

   For Each sht In Worksheets
      If (sht.Name <> NS.Name) Then
         Set SheetRange = sht.Range(Right(refRange, Len(refRange) - InStr(refRange, "!")))
         SheetRange.Copy
         NS.Cells(i, 1).Value = sht.Name
         NS.Cells(i, 2).PasteSpecial xlPasteValues
         i = i + SheetRange.Rows.Count
      End If
   Next sht
   Application.ScreenUpdating = True
   Application.EnableEvents = True

End Sub

You may modify refRange to your range and the paste cycle to suit your needs:

Sub Copy_ranges()

    Dim NS As Worksheet

    Application.ScreenUpdating = False
    Application.EnableEvents = False

    Set NS = Sheets.Add

    i = 1
    refRange = "A1:A10"

    For Each sht In Worksheets
        If (sht.Name <> NS.Name) Then
          Set SheetRange = sht.Range(Right(refRange, Len(refRange) - InStr(refRange, "!")))
          SheetRange.Copy
          NS.Cells(1, i).Value = sht.Name
          NS.Cells(2, i).PasteSpecial xlPasteValues
          i = i + SheetRange.Columns.Count
        End If
    Next sht

    Application.ScreenUpdating = True
    Application.EnableEvents = True

End Sub
ru13r
  • 194
  • 8
  • Alexey, seems this is what I want, thanks! However 1 more problem appeared. When a new sheet is created with copied data all the data is in date format. How can I fix it to make the format general? – Nurzhan Sep 07 '17 at 12:20