Moving Data

DonJuan692006 Posts 2 Registration date Thursday May 26, 2016 Status Member Last seen May 26, 2016 - May 26, 2016 at 11:40 AM
vcoolio Posts 1362 Registration date Thursday July 24, 2014 Status Moderator Last seen September 29, 2022 - May 26, 2016 at 10:53 PM
Hello Peeps,

I'm having some trouble with using a macro to copy some data from one sheet across multiple. I have a master sheet that contains training data for my divisional personnel. Columns A-E contain the personnel information with column E identifying what work group the personnel belong to; these are labeled as Section 1, Section 2, etc through Section 5. What I'm trying to do is have individual sheets for each section. I want the macro to look in column E in my master sheet, find every row that belongs to personnel in a particular section, and copy the contents of that row into the corresponding sheet for that section. The code I'm using is below. The problem I'm running into is that when I run the macro I'm getting an error saying "Compile error: Variable not defined" and when the debugger comes up, it's highlighting the "Sub MoveStuff()" line. Any help would be massively appreciated.

Option Explicit

Sub MoveStuff()
Dim ar As Variant, i As Integer

ar = [{"Day Staff","Section 1","Section 2","Section 3","Section 4","Section 5";Day Staff,Section 1,Section 2,Section 3,Section 4,Section 5}]
Application.ScreenUpdating = False

    For i = 1 To UBound(ar, 2)
      Sheets(ar(1, i)).UsedRange.Offset(1).ClearContents
        With Sheet1
            .AutoFilterMode = False
                With Range("E2", Range("E" & Rows.Count).End(xlUp))
                    .AutoFilter 1, ar(2, i)
                     Sheets(ar(1, i)).Range("A" & Rows.Count).End(xlUp)(2).PasteSpecial xlPasteValues
                    ActiveSheet.AutoFilterMode = False
                    Sheets(ar(1, i)).Columns.AutoFit
                End With
        End With
    Next i
Application.ScreenUpdating = True
Application.CutCopyMode = False
MsgBox "All done!", vbExclamation

End Sub

2 replies

Blocked Profile
May 26, 2016 at 05:07 PM
Just looking at a quick glance, I don't think your RANGE() is correct:

Range("E2", Range("E" & Rows.Count).End(xlUp))

Typically, Range is defined by a NAME. Set the second range up as another variable, initialized as a range.

Like so:
dim Rangx as Range


Give that a try!
vcoolio Posts 1362 Registration date Thursday July 24, 2014 Status Moderator Last seen September 29, 2022 252
May 26, 2016 at 10:53 PM
Greetings Gentlemen,

Just wondering Ryan, is "Day Staff" your Master (or Input) sheet? Is it defined in the Project Explorer as Sheet1? (Project Explorer lists all the sheets to the left of the code field in the code Module).

If "Day Staff" is your Input sheet and it is defined as sheet1, then it is your Active Sheet, so:-

- Remove "Day Staff" from the array (ar), lines 6 & 7.
- Add quotation marks around the second part of the array: i.e. "Section 1", "Section 2" etc. so that it is the same as the first part of the array. This will ensure that Excel recognises this part of the array as Alphanumeric text.
- Change "E2" in line 14 to "E1" as you need to filter on the headings and not the first row of data which I assume starts on row 2 (other wise the offset(1) in line 16 will ignore your first row of data).

I reckon that should sort it out for you.