How do I make the VBA macro repeat for all worksheets (tech collect1..2..etc) except for Tech Database which is where the info will paste to?

Question

The first section is where I am copying the data from and the second section is where I am pasting the data to. Multiple worksheets will be added to the workbook ( tech collect) so when i run the code it should copy all info from "tech collect" to "tech database"

Sub Test()
'
' Test Macro
' test
'
' Keyboard Shortcut: Ctrl+a
'
Sheets("Technician Collection").Select
Range("B3").Select

Sheets("Tech Collection Database").Select
Range("B6").Select

Sheets("Technician Collection").Select

xb3 = (ActiveCell.Value)
xb4 = (ActiveCell.Offset(1, 0).Value)
xb5 = (ActiveCell.Offset(2, 0).Value)
xb6 = (ActiveCell.Offset(3, 0).Value)
xb7 = (ActiveCell.Offset(4, 0).Value)
xb8 = (ActiveCell.Offset(5, 0).Value)
xb9 = (ActiveCell.Offset(6, 0).Value)
xb10 = (ActiveCell.Offset(7, 0).Value)
xb11 = (ActiveCell.Offset(8, 0).Value)
xb12 = (ActiveCell.Offset(9, 0).Value)
xb15 = (ActiveCell.Offset(12, 0).Value)
xb16 = (ActiveCell.Offset(13, 0).Value)
xb17 = (ActiveCell.Offset(14, 0).Value)
xb18 = (ActiveCell.Offset(15, 0).Value)
xb19 = (ActiveCell.Offset(16, 0).Value)
xb20 = (ActiveCell.Offset(17, 0).Value)
xb21 = (ActiveCell.Offset(18, 0).Value)
xb22 = (ActiveCell.Offset(19, 0).Value)
xb24 = (ActiveCell.Offset(21, 0).Value)
xc24 = (ActiveCell.Offset(21, 1).Value)
xd24 = (ActiveCell.Offset(21, 2).Value)
xe24 = (ActiveCell.Offset(21, 3).Value)
xb25 = (ActiveCell.Offset(22, 0).Value)
xb26 = (ActiveCell.Offset(23, 0).Value)
xc26 = (ActiveCell.Offset(23, 1).Value)
xd26 = (ActiveCell.Offset(23, 2).Value)
xe26 = (ActiveCell.Offset(23, 3).Value)
xb27 = (ActiveCell.Offset(24, 0).Value)
xc27 = (ActiveCell.Offset(24, 1).Value)
xd27 = (ActiveCell.Offset(24, 2).Value)
xe27 = (ActiveCell.Offset(24, 3).Value)
xb28 = (ActiveCell.Offset(25, 0).Value)
xc28 = (ActiveCell.Offset(25, 1).Value)
xd28 = (ActiveCell.Offset(25, 2).Value)
xe28 = (ActiveCell.Offset(25, 3).Value)
xb30 = (ActiveCell.Offset(27, 0).Value)
xc30 = (ActiveCell.Offset(27, 1).Value)
xd30 = (ActiveCell.Offset(27, 2).Value)
xe30 = (ActiveCell.Offset(27, 3).Value)
xb32 = (ActiveCell.Offset(29, 0).Value)
xb34 = (ActiveCell.Offset(31, 0).Value)
xb36 = (ActiveCell.Offset(33, 0).Value)
xb40 = (ActiveCell.Offset(37, 0).Value)
xb41 = (ActiveCell.Offset(38, 0).Value)
xb42 = (ActiveCell.Offset(39, 0).Value)
xb43 = (ActiveCell.Offset(40, 0).Value)
xb44 = (ActiveCell.Offset(41, 0).Value)
xb45 = (ActiveCell.Offset(42, 0).Value)
xb46 = (ActiveCell.Offset(43, 0).Value)
xb47 = (ActiveCell.Offset(44, 0).Value)
xb48 = (ActiveCell.Offset(45, 0).Value)

Sheets("Tech Collection Database").Select
ActiveCell.Value = xb3
ActiveCell.Offset(0, 1).Value = xb4
ActiveCell.Offset(0, 2).Value = xb5
ActiveCell.Offset(0, 3).Value = xb6
ActiveCell.Offset(0, 4).Value = xb7
ActiveCell.Offset(0, 5).Value = xb8
ActiveCell.Offset(0, 6).Value = xb9
ActiveCell.Offset(0, 7).Value = xb10
ActiveCell.Offset(0, 8).Value = xb11
ActiveCell.Offset(0, 9).Value = xb12
ActiveCell.Offset(0, 10).Value = xb15
ActiveCell.Offset(0, 11).Value = xb16
ActiveCell.Offset(0, 12).Value = xb17
ActiveCell.Offset(0, 13).Value = xb18
ActiveCell.Offset(0, 14).Value = xb19
ActiveCell.Offset(0, 15).Value = xb20
ActiveCell.Offset(0, 16).Value = xb21
ActiveCell.Offset(0, 17).Value = xb22
ActiveCell.Offset(0, 18).Value = xb24
ActiveCell.Offset(0, 19).Value = xc24
ActiveCell.Offset(0, 20).Value = xd24
ActiveCell.Offset(0, 21).Value = xe24
ActiveCell.Offset(0, 22).Value = xb25
ActiveCell.Offset(0, 23).Value = xb26
ActiveCell.Offset(0, 24).Value = xc26
ActiveCell.Offset(0, 25).Value = xd26
ActiveCell.Offset(0, 26).Value = xe26
ActiveCell.Offset(0, 27).Value = xb27
ActiveCell.Offset(0, 28).Value = xc27
ActiveCell.Offset(0, 29).Value = xd27
ActiveCell.Offset(0, 30).Value = xe27
ActiveCell.Offset(0, 31).Value = xb28
ActiveCell.Offset(0, 32).Value = xc28
ActiveCell.Offset(0, 33).Value = xd28
ActiveCell.Offset(0, 34).Value = xe28
ActiveCell.Offset(0, 35).Value = xb30
ActiveCell.Offset(0, 36).Value = xc30
ActiveCell.Offset(0, 37).Value = xd30
ActiveCell.Offset(0, 38).Value = xe30
ActiveCell.Offset(0, 39).Value = xb32
ActiveCell.Offset(0, 40).Value = xb34
ActiveCell.Offset(0, 41).Value = xb36
ActiveCell.Offset(0, 42).Value = xb40
ActiveCell.Offset(0, 43).Value = xb41
ActiveCell.Offset(0, 44).Value = xb42
ActiveCell.Offset(0, 45).Value = xb43
ActiveCell.Offset(0, 46).Value = xb44
ActiveCell.Offset(0, 47).Value = xb45
ActiveCell.Offset(0, 48).Value = xb46
ActiveCell.Offset(0, 49).Value = xb47
ActiveCell.Offset(0, 50).Value = xb48

End Sub

The first section is where I am copying the data from and the second section is where I am pasting the data to

You may also want to see [This](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) — Siddharth Rout, Jul 02 '15 at 20:58
Some stuff for you to Google: dim as worksheet, dim as Range, For Each loops, If/Then/Else, Sheet.Name, Set Range =, Range1.Value = Range2.Value — puzzlepiece87, Jul 02 '15 at 21:26

score 0 · Answer 1 · answered Jul 02 '15 at 22:13

0

This is what I have used to step though every sheet except for one:

For Each Sh In MyWkBk.Sheets
    If Sh.Name <> "Reference" Then
        '....
        'Your Code Here
        '....
    End If
Next

answered Jul 02 '15 at 22:13

SeanC

15,695
5
45
66

How do I make the VBA macro repeat for all worksheets (tech collect1..2..etc) except for Tech Database which is where the info will paste to?

1 Answers1