Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

This is my code:

'copy cells
Sub start()
'variable for all worksheets in the workbook
Dim sh1 As Worksheet
Dim sh2 As Worksheet
Dim sh3 As Worksheet
Dim sh4 As Worksheet
Dim sh5 As Worksheet
Dim sh6 As Worksheet
Dim sh7 As Worksheet
Dim sh8 As Worksheet
Dim sh9 As Worksheet
Dim sh10 As Worksheet
Dim sh11 As Worksheet
Dim sh12 As Worksheet
Dim sh13 As Worksheet
Dim sh14 As Worksheet
Dim sh15 As Worksheet
Dim sh16 As Worksheet
Dim sh17 As Worksheet
Dim sh18 As Worksheet
Dim sh19 As Worksheet
Dim sh20 As Worksheet
Dim sh21 As Worksheet
Dim sh22 As Worksheet
Dim sh23 As Worksheet
Dim sh24 As Worksheet
Dim sh25 As Worksheet
Dim sh26 As Worksheet
Dim sh27 As Worksheet
Dim sh28 As Worksheet
Dim sh29 As Worksheet
Dim sh30 As Worksheet
Dim sh31 As Worksheet
Dim sh32 As Worksheet
Dim sh33 As Worksheet
Dim sh34 As Worksheet
Dim sh35 As Worksheet
Dim sh36 As Worksheet
Dim sh37 As Worksheet
Dim sh38 As Worksheet
Dim sh39 As Worksheet
Dim sh40 As Worksheet
'variable for the workbook itself
Dim wkb As Workbook
'set workbook
Set wkb = Workbooks("macro2.xlsm")
With wkb 'create with statement to call on worksheets

Set sh1 = .Sheets("1")
Set sh2 = .Sheets("2")
Set sh3 = .Sheets("3")
Set sh4 = .Sheets("4")
Set sh5 = .Sheets("5")
Set sh6 = .Sheets("6")
Set sh7 = .Sheets("7")
Set sh8 = .Sheets("8")
Set sh9 = .Sheets("9")
Set sh10 = .Sheets("10")
Set sh11 = .Sheets("11")
Set sh12 = .Sheets("12")
Set sh13 = .Sheets("13")
Set sh14 = .Sheets("14")
Set sh15 = .Sheets("15")
Set sh16 = .Sheets("16")
Set sh17 = .Sheets("17")
Set sh18 = .Sheets("18")
Set sh19 = .Sheets("19")
Set sh20 = .Sheets("20")
Set sh21 = .Sheets("21")
Set sh22 = .Sheets("22")
Set sh23 = .Sheets("23")
Set sh24 = .Sheets("24")
Set sh25 = .Sheets("25")
Set sh26 = .Sheets("26")
Set sh27 = .Sheets("27")
Set sh28 = .Sheets("28")
Set sh29 = .Sheets("29")
Set sh30 = .Sheets("30")
Set sh31 = .Sheets("31")
Set sh32 = .Sheets("32")
Set sh33 = .Sheets("33")
Set sh34 = .Sheets("34")
Set sh35 = .Sheets("35")
Set sh36 = .Sheets("36")
Set sh37 = .Sheets("37")
Set sh38 = .Sheets("38")
Set sh39 = .Sheets("39")
Set sh40 = .Sheets("40")
Set shsum = .Sheets("sum") 'name sheet to copy to
'add more if there are more sheets example
'set sh41 = .sheets("41")

'name first range to copy
sh1.Range("b8").copy shsum.Range("a2")
sh1.Range("b5").copy shsum.Range("b2")
sh1.Range("b4").copy shsum.Range("c2")
sh1.Range("G13:G30").copy shsum.Range("d2:d18")
sh1.Range("h13:h30").copy shsum.Range("e2:e18")
sh1.Range("i13:i30").copy shsum.Range("f2:f18")
sh1.Range("j13:j30").copy shsum.Range("g2:g18")
sh1.Range("k13:k30").copy shsum.Range("h2:h18")

End With
End Sub

In order to obtain the data on the 40 sheets, I would need to enter each sheet with same information you see and that is a ton of typing.

  • Is there a way I can do some sort of array for the sheets?
  • I then need to skip 18 rows down for each additional sheet so that there is no data overlap in the sum worksheet. This is because g13 through k30 has 18 lines of data that need to be filled in. b8, b5 and b4 are the item name (X), number (Y) and a number (Z) associated with the items that take up g13 through k30 in each spreadsheet. I need to make sure that each spreadsheet's data copied into the sum worksheet contains X, Y and Z items in the first row, followed by the associated items that are 18 rows long.
share|improve this question

1 Answer 1

Option Explicit

'copy cells
Sub start()
Dim ws As Worksheet, wsum As Worksheet
Dim wb As Workbook
Dim vws As Variant 'Need to use a Variant for iterator
Dim i As Integer, j As String, k As String

i = 0
Set wb = Workbooks("macro2.xlsm")
Set wsum = wb.Sheets("sum")

'Iterate through the sheets
For Each vws In wb.Sheets
If vws.Name <> "sum" Then 
j = CStr(i + 2)
k = CStr(i + 18)
vws.Range("b8").Copy wsum.Range("a" & j)
vws.Range("b5").Copy wsum.Range("b" & j)
vws.Range("b4").Copy wsum.Range("c" & j)
vws.Range("G13:G30").Copy wsum.Range("d" & j & ":d" & k)
vws.Range("h13:h30").Copy wsum.Range("e" & j & ":e" & k)
vws.Range("i13:i30").Copy wsum.Range("f" & j & ":f" & k)
vws.Range("j13:j30").Copy wsum.Range("g" & j & ":g" & k)
vws.Range("k13:k30").Copy wsum.Range("h" & j & ":h" & k)
i = i + 18
End If
Next
End Sub

Heavily refactored to use loops and string concatenation. Only compile tested, so it may not work; any defects are yours to keep and if you are dissatisfied I will refund the full purchase price. Thank you for shopping at McSoftware's.

This is very basic (no pun intended) and should be something you can learn. A few explanatory points to explain how I came up with this:

  • For Each vws In wb.Sheets iterates through each sheet in the workbook. "Iterate" means "step through one by one and handle each element separately in a loop".
  • If you need more than a dozen or so variables in a function, you're doing it wrong, and should immediately stop and try something else. Asking a question on SuperUser is not the best thing you could have done, but it's better than copy and pasting your code 48 more times, so I applaud you on at least realizing that there was a problem.
  • String concatenation means taking two smaller strings and putting them together. For example, "he" & "llo" would yield the concatenated string "hello". The & operator is used to concatenate strings in Visual Basic.
  • You can probably further reduce the repetitiveness of this code by adding a loop and creating a constant array of letters for "d", "e", "f", "g", "h" -- but since there were only five of them, the effort of writing a loop is almost the same as doing it one by one. If you end up with lots more columns, definitely write a loop with an array or dictionary of letters.
  • I used conversion from numbers to strings with the CStr() function to convert the integer i plus a constant derived from your original code. So on the first iteration of the loop it'll put vws.Range("b8") in the sum sheet's range A2 because 0 + 2 = 2. At the end of the loop, notice the i = i + 18 to increment the counter, so that on the second iteration that same cell in the second worksheet is copied into the sum sheet's range A20 instead. You may have to adjust the numbers because I can't tell from your source data whether this will accidentally overwrite/trample on rows or not.

References

Before you attempt anything more exotic in Visual Basic (for Applications), go read a book on programming.

  • A very basic book that takes you through step by step (probably what you need) here - Excel VBA in Easy Steps by Ed Robinson.
  • An oldie but goodie, VBA Developer's Handbook 2nd Edition by Ken Getz.

You might also gain additional perspective and insight into being a proficient programmer if you try a more modern and general-purpose programming language, such as Python or Java or C++, just to learn concepts that will help you write good code in any language.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.