Running my macro on all worksheets

Question

I have to prepare a short Macro. I multiple worksheets in a workbook and I wrote a macro to import a range from a different workbook, but I want my macro to loop over all worksheets. I read a lot about it but still I think I am missing some basic knowledge on vba. Could someone please help me to put this macro in a loop?

Sub AddHeader()
    current = ActiveWorkbook.Name
    Range("A1:C96").Select
    Selection.Cut Destination:=Range("A55:C150")
    Windows("MIP_Ordering_Header.xlsx").Activate
    Range("A1:H54").Select
    Selection.Copy
    Windows(current).Activate
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
    ActiveSheet.Paste
    Cells(53, 1).Value2 = "Plate Name:" & ActiveSheet.Name
End Sub

This link explains how to loop through all worksheets in a workbook: http://support.microsoft.com/kb/142126 — Kevin, Nov 08 '14 at 12:44
Thanks I tried that, but it seems not to be sufficient just just encloise my code in this loop. Do I have to change variables? — Benedikt, Nov 08 '14 at 13:08
You would avoid using Select. [see this answer](http://stackoverflow.com/a/10717999/445425) — chris neilsen, Nov 08 '14 at 23:32

Kevin · Accepted Answer · 2014-11-08T20:12:26.620

-1

I would do something similar to below. Copy the range first, and then loop through the sheets in the active workbook:

Sub AddHeader()

Dim WS_Count As Integer
Dim I As Integer

'first get the data to copy
Windows("MIP_Ordering_Header.xlsx").Activate
Range("A1:H54").Select
Selection.Copy
ActiveWorkbook.Close

'get all the active sheets
WS_Count = ActiveWorkbook.Worksheets.Count

' Begin the loop.
For I = 1 To WS_Count
    'refer to the curent workbook using the "I" variable
    ActiveWorkbook.Worksheets(I).Range("A1").Select
    'the rest of your code...
Next End Sub

Edit: How to copy paste with variables:

Sub CopyPaste()

Dim src1 As Variant
Dim src2 As Variant

src1 = Sheets(1).Range("A1:A2").Value
src2 = Sheets(1).Range("A4:A5").Value

Sheets(1).Range("B1:B2").Value = src1
Sheets(1).Range("B4:B5").Value = src2 End Sub

edited Nov 08 '14 at 20:12

answered Nov 08 '14 at 14:04

Kevin

2,566
1
11
12

Thanks, it works, but only if I also put the "copy" part into the loop. Because otherwise the selection gets overwritten. Can I have two "selections" at a time? – Benedikt Nov 08 '14 at 17:51
You can't have two selections at one time, but see my edit to see how to assign ranges to a variant. The example is basically a copy paste. – Kevin Nov 08 '14 at 20:10
Hmmm I answered the question and the user is happy - thanks for the -1 – Kevin Nov 10 '14 at 12:42
Thought it was my fault, but as new user I can't even vote... so I don't know who voted you down...sorry... – Benedikt Nov 11 '14 at 21:53
@Benedikt - not your fault at all. I was hoping to teach and not just write the code for you. Obviously someone didn't like that... – Kevin Nov 12 '14 at 12:58

Running my macro on all worksheets

1 Answers1