Moving Data [Closed]

Report
Posts
2
Registration date
Thursday May 26, 2016
Status
Member
Last seen
May 26, 2016
-
Posts
1292
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
May 7, 2021
-
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)
                    .Offset(1).EntireRow.Copy
                     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


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
Rangx="E"&Rows.count

Range("E2",Rangx).End(xlUp).row

Give that a try!
Posts
1292
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
May 7, 2021
229
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.

Cheerio,
vcoolio.

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!