Not Able to Specify Range I want as the range keeps changing Please Help Me

G

GohilaVasaN91

New Member
Joined
Jan 28, 2018
Messages
3
Hello Guys,

I am New to Excel Macros this is the first time I have created a big code which I want to use for a file every week and the data gets updated often Please find the full code that I have written below.

Code: 
Sub CallMacros()
        Unmerge
        TurningOffWrapText
        ShrinkToFit
        DeleteRow4
        DeleteColumnC
        DeleteColumnK
        FindnReplace
        FindnReplace2
        Filter
        AddSheetDailyHire
        DailyHireSeperation
        DeleteASheet
        InsertingColumn
        NamingK4
        DateConversion
        FillDate
        ConvertToValues
        DailyHirePivot
End Sub


Sub Unmerge()
    Dim wSheet As Worksheet
For Each wSheet In Worksheets
[A1:AA1000].Unmerge
Next wSheet
End Sub


Sub TurningOffWrapText()
    Cells.WrapText = False
End Sub


Sub ShrinkToFit()
    Range("A1:AA1000").ShrinkToFit = False
End Sub


Sub DeleteRow4()
Rows("4").Delete
End Sub


Sub DeleteColumnC()
Columns("C").Delete
End Sub


Sub DeleteColumnK()
Columns("K").Delete
End Sub


Sub FindnReplace()
    Dim target, cell As Range
    Dim i, k As String
    i = "DAILY HIRE"
    k = "DAILYHIRE"
    Set target = Sheets("MISCTrips").Range(Range("I1"), Range("I65536").End(xlUp))
    For Each cell In target
        If cell.Value = i Then cell.Value = k
    Next cell
End Sub


Sub FindnReplace2()
    Dim target, cell As Range
    Dim i, k As String
    i = "Client Name"
    k = "ClientName"
    Set target = Sheets("MISCTrips").Range(Range("A1"), Range("A5").End(xlUp))
    For Each cell In target
        If cell.Value = i Then cell.Value = k
    Next cell
End Sub


Sub Filter()
Range("I4").AutoFilter Field:=9, Criteria1:="<dailyhire>"
End Sub


Sub AddSheetDailyHire()


Dim newsheet
Set newsheet = Sheets.Add(After:=Sheets(Worksheets.Count), Count:=1, Type:=xlWorksheet)
newsheet.Name = "DailyHire"


End Sub


Sub DailyHireSeperation()


    Dim DbExtract, DuplicateRecords As Worksheet
    Set DbExtract = ThisWorkbook.Sheets("MISCTrips")
    Set DuplicateRecords = ThisWorkbook.Sheets("DailyHire")


    DbExtract.Range("A1:ZZ10000").SpecialCells(xlCellTypeVisible).Copy
    DuplicateRecords.Cells(1, 1).PasteSpecial




    End Sub
    
    Sub DeleteASheet()
    
    Application.DisplayAlerts = False
    Sheets("MISCTrips").Delete
    Application.DisplayAlerts = True
    
    End Sub
    
    Sub InsertingColumn()
    
    Range("K4").EntireColumn.Insert
    
    End Sub
    
    Sub NamingK4()


    RangeName = "Date"
    CellName = "K4"
    Set cell = Worksheets("DailyHire").Range(CellName)
    ThisWorkbook.Names.Add Name:=RangeName, RefersTo:=cell
    Application.Worksheets("DailyHire").Range("K4") = "Date"
    End Sub
    Sub DateConversion()
    
    Worksheets("DailyHire").Range("K5").Formula = "=LEFT(L5,11)"
    
    End Sub


Sub FillDate()
    
    Dim LastRow As Long
    LastRow = Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Range("K5:K" & LastRow).FillDown
    
End Sub


Sub ConvertToValues()


    With Range("K5:K1000000")
        .Cells.Copy
        .Cells.PasteSpecial xlPasteValues
        .Cells(1).Select
    End With
    
    Application.CutCopyMode = False
    
End Sub


Sub DailyHirePivot()


    On Error Resume Next
    Application.DisplayAlerts = False
    Worksheets("PivotTable").Delete
    Sheets.Add After:=ActiveSheet
    ActiveSheet.Name = "PivotTable"
    Application.DisplayAlerts = True
    Set PSheet = Worksheets("PivotTable")
    Set DSheet = Worksheets("DailyHire")
    
    On Error Resume Next
    Application.DisplayAlerts = False
    Worksheets("ChennaiClients").Delete
    Sheets.Add After:=ActiveSheet
    ActiveSheet.Name = "ChennaiClients"
    Application.DisplayAlerts = True
    
    On Error Resume Next
    Application.DisplayAlerts = False
    Worksheets("NetworkClients").Delete
    Sheets.Add After:=ActiveSheet
    ActiveSheet.Name = "NetworkClients"
    Application.DisplayAlerts = True
    
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "DailyHire!R4C1:R3269C60", Version:=xlPivotTableVersion12).CreatePivotTable _
        TableDestination:="PivotTable!R2C2", TableName:="DailyHirePivot", DefaultVersion _
        :=xlPivotTableVersion12
    Sheets("PivotTable").Select
    Cells(2, 2).Select
    
    ActiveSheet.PivotTables("DailyHirePivot").TableStyle2 = "PivotStyleLight23"
    ActiveSheet.PivotTables("DailyHirePivot").ShowTableStyleRowStripes = True
    
    With ActiveSheet.PivotTables("DailyHirePivot").PivotFields("ClientName")
        .Orientation = xlRowField
        .Position = 1
    End With
    ActiveWindow.SmallScroll Down:=-3
    With ActiveSheet.PivotTables("DailyHirePivot").PivotFields("Date")
        .Orientation = xlColumnField
        .Position = 2
    End With
    ActiveSheet.PivotTables("DailyHirePivot").AddDataField ActiveSheet.PivotTables( _
        "DailyHirePivot").PivotFields("Date"), "Count of Date", xlCount
    With ActiveSheet.PivotTables("DailyHirePivot").PivotFields("Date")
        .Orientation = xlColumnField
        .Position = 1
    End With
    ActiveWindow.SmallScroll Down:=-12
    ActiveWindow.ScrollRow = 2
    ActiveWindow.ScrollRow = 3
    ActiveWindow.ScrollRow = 4
    ActiveWindow.ScrollRow = 5
    ActiveWindow.ScrollRow = 6
    ActiveWindow.ScrollRow = 7
    ActiveWindow.ScrollRow = 8
    ActiveWindow.ScrollRow = 9
    ActiveWindow.ScrollRow = 10
    ActiveWindow.ScrollRow = 11
    ActiveWindow.ScrollRow = 12
    ActiveWindow.ScrollRow = 13
    ActiveWindow.ScrollRow = 14
    ActiveWindow.ScrollRow = 15
    ActiveWindow.ScrollRow = 16
    ActiveWindow.ScrollRow = 17
    ActiveWindow.ScrollRow = 18
    ActiveWindow.ScrollRow = 19
    ActiveWindow.ScrollRow = 20
    ActiveWindow.ScrollRow = 21
    ActiveWindow.ScrollRow = 22
    ActiveWindow.ScrollRow = 23
    ActiveWindow.ScrollRow = 24
    ActiveWindow.ScrollRow = 25
    ActiveWindow.ScrollRow = 26
    ActiveWindow.ScrollRow = 27
    ActiveWindow.ScrollRow = 28
    ActiveWindow.ScrollRow = 29
    ActiveWindow.ScrollRow = 30
    ActiveWindow.ScrollRow = 31
    ActiveWindow.ScrollRow = 32
    ActiveWindow.ScrollRow = 33
    ActiveWindow.ScrollRow = 34
    ActiveWindow.ScrollRow = 35
    ActiveWindow.ScrollRow = 36
    ActiveWindow.ScrollRow = 35
    ActiveWindow.ScrollRow = 34
    ActiveWindow.ScrollRow = 32
    ActiveWindow.ScrollRow = 31
    ActiveWindow.ScrollRow = 30
    ActiveWindow.ScrollRow = 29
    ActiveWindow.ScrollRow = 28
    ActiveWindow.ScrollRow = 27
    ActiveWindow.ScrollRow = 26
    ActiveWindow.ScrollRow = 25
    ActiveWindow.ScrollRow = 24
    ActiveWindow.ScrollRow = 23
    ActiveWindow.ScrollRow = 22
    ActiveWindow.ScrollRow = 21
    ActiveWindow.ScrollRow = 20
    ActiveWindow.ScrollRow = 19
    ActiveWindow.ScrollRow = 18
    ActiveWindow.ScrollRow = 17
    ActiveWindow.ScrollRow = 16
    ActiveWindow.ScrollRow = 15
    ActiveWindow.ScrollRow = 14
    ActiveWindow.ScrollRow = 13
    ActiveWindow.ScrollRow = 12
    ActiveWindow.ScrollRow = 11
    ActiveWindow.ScrollRow = 10
    ActiveWindow.ScrollRow = 9
    ActiveWindow.ScrollRow = 8
    ActiveWindow.ScrollRow = 7
    ActiveWindow.ScrollRow = 6
    ActiveWindow.ScrollRow = 5
    ActiveWindow.ScrollRow = 4
    ActiveWindow.ScrollRow = 3
    ActiveWindow.ScrollRow = 2
    ActiveWindow.ScrollRow = 1
    ActiveWindow.ScrollRow = 2
    ActiveWindow.ScrollRow = 3
    ActiveWindow.ScrollRow = 4
    ActiveWindow.ScrollRow = 5
    ActiveWindow.ScrollRow = 6
    ActiveWindow.ScrollRow = 7
    ActiveWindow.ScrollRow = 8
    ActiveWindow.ScrollRow = 9
    ActiveWindow.ScrollRow = 10
    ActiveWindow.ScrollRow = 12
    ActiveWindow.ScrollRow = 13
    ActiveWindow.ScrollRow = 14
    ActiveWindow.ScrollRow = 15
    ActiveWindow.ScrollRow = 16
    ActiveWindow.ScrollRow = 17
    ActiveWindow.ScrollRow = 18
    ActiveWindow.ScrollRow = 19
    ActiveWindow.ScrollRow = 20
    ActiveWindow.ScrollRow = 21
    ActiveWindow.ScrollRow = 22
    ActiveWindow.ScrollRow = 23
    ActiveWindow.ScrollRow = 24
    ActiveWindow.ScrollRow = 25
    ActiveWindow.ScrollRow = 26
    ActiveWindow.ScrollRow = 27
    ActiveWindow.ScrollRow = 28
    ActiveWindow.ScrollRow = 29
    ActiveWindow.ScrollRow = 30
    ActiveWindow.ScrollRow = 31
    ActiveWindow.ScrollRow = 32
    ActiveWindow.ScrollRow = 34
    ActiveWindow.ScrollRow = 35
    ActiveWindow.ScrollRow = 36
    ActiveWindow.ScrollRow = 38
    ActiveWindow.ScrollRow = 39
    ActiveWindow.ScrollRow = 40
    ActiveWindow.ScrollRow = 41
    ActiveWindow.ScrollRow = 43
    ActiveWindow.ScrollRow = 45
    ActiveWindow.ScrollRow = 47
    ActiveWindow.ScrollRow = 48
    ActiveWindow.ScrollRow = 49
    ActiveWindow.ScrollRow = 50
    ActiveWindow.ScrollRow = 51
    ActiveWindow.ScrollRow = 52
    ActiveWindow.ScrollRow = 53
    ActiveWindow.ScrollRow = 54
    ActiveWindow.ScrollRow = 55
    ActiveWindow.ScrollRow = 56
    ActiveWindow.ScrollRow = 57
    ActiveWindow.ScrollRow = 58
    ActiveWindow.ScrollRow = 59
    ActiveWindow.ScrollRow = 60
    ActiveWindow.ScrollRow = 61
    ActiveWindow.ScrollRow = 62
    ActiveWindow.ScrollRow = 63
    ActiveWindow.ScrollRow = 64
    ActiveWindow.ScrollRow = 65
    ActiveWindow.ScrollRow = 66
    ActiveWindow.ScrollRow = 67
    ActiveWindow.ScrollRow = 68
    ActiveWindow.ScrollRow = 69
    ActiveWindow.ScrollRow = 70
    ActiveWindow.ScrollRow = 71
    ActiveWindow.ScrollRow = 72
    ActiveWindow.ScrollRow = 73
    ActiveWindow.ScrollRow = 74
    ActiveWindow.ScrollRow = 75
    ActiveWindow.ScrollRow = 76
    ActiveWindow.ScrollRow = 77
    ActiveWindow.ScrollRow = 78
    ActiveWindow.ScrollRow = 79
    ActiveWindow.ScrollRow = 80
    ActiveWindow.ScrollRow = 81
    ActiveWindow.ScrollRow = 82
    ActiveWindow.ScrollRow = 83
    ActiveWindow.ScrollRow = 84
    ActiveWindow.ScrollRow = 85
    ActiveWindow.ScrollRow = 86
    ActiveWindow.ScrollRow = 88
    ActiveWindow.ScrollRow = 89
    ActiveWindow.ScrollRow = 90
    ActiveWindow.ScrollRow = 91
    ActiveWindow.ScrollRow = 92
    ActiveWindow.ScrollRow = 93
    ActiveWindow.ScrollRow = 94
    ActiveWindow.ScrollRow = 95
    ActiveWindow.ScrollRow = 96
    ActiveWindow.ScrollRow = 97
    ActiveWindow.ScrollRow = 98
    ActiveWindow.ScrollRow = 99
    ActiveWindow.ScrollRow = 100
    ActiveWindow.ScrollRow = 101
    ActiveWindow.ScrollRow = 102
    ActiveWindow.ScrollRow = 103
    ActiveWindow.ScrollRow = 104
    ActiveWindow.ScrollRow = 105
    ActiveWindow.ScrollRow = 106
    ActiveWindow.ScrollRow = 107
    ActiveWindow.ScrollRow = 108
    ActiveWindow.ScrollRow = 109
    ActiveWindow.ScrollRow = 110
    ActiveWindow.ScrollRow = 111
    ActiveWindow.ScrollRow = 112
    ActiveWindow.ScrollRow = 113
    ActiveWindow.ScrollRow = 114
    ActiveWindow.ScrollRow = 115
    ActiveWindow.ScrollRow = 116
    ActiveWindow.ScrollRow = 117
    ActiveWindow.ScrollRow = 118
    ActiveWindow.ScrollRow = 119
    ActiveWindow.ScrollRow = 120
    ActiveWindow.ScrollRow = 121
    ActiveWindow.ScrollRow = 122
    ActiveWindow.ScrollRow = 123
    ActiveWindow.ScrollRow = 124
    ActiveWindow.ScrollRow = 125
    ActiveWindow.ScrollRow = 126
    ActiveWindow.ScrollRow = 127
    ActiveWindow.ScrollRow = 128
    ActiveWindow.ScrollRow = 129
    ActiveWindow.ScrollRow = 130
    ActiveWindow.ScrollRow = 131
    ActiveWindow.ScrollRow = 132
    ActiveWindow.ScrollRow = 133
    ActiveWindow.ScrollRow = 134
    ActiveWindow.ScrollRow = 135
    ActiveWindow.ScrollRow = 136
    ActiveWindow.ScrollRow = 137
    ActiveWindow.ScrollRow = 138
    ActiveWindow.ScrollRow = 139
    ActiveWindow.ScrollRow = 140
    ActiveWindow.ScrollRow = 141
    ActiveWindow.ScrollRow = 142
    ActiveWindow.ScrollRow = 143
    ActiveWindow.ScrollRow = 144
    ActiveWindow.ScrollRow = 145
    ActiveWindow.ScrollRow = 146
    ActiveWindow.ScrollRow = 147
    ActiveWindow.ScrollRow = 148
    ActiveWindow.ScrollRow = 149
    ActiveWindow.ScrollRow = 150
    ActiveWindow.ScrollRow = 151
    ActiveWindow.ScrollRow = 152
    ActiveWindow.ScrollRow = 153
    ActiveWindow.ScrollRow = 154
    ActiveWindow.ScrollRow = 155
    ActiveWindow.ScrollRow = 156
    ActiveWindow.ScrollRow = 157
    ActiveWindow.ScrollRow = 158
    ActiveWindow.ScrollRow = 159
    ActiveWindow.ScrollRow = 160
    ActiveWindow.ScrollRow = 161
    ActiveWindow.ScrollRow = 162
    ActiveWindow.ScrollRow = 163
    ActiveWindow.ScrollRow = 164
    ActiveWindow.ScrollRow = 165
    ActiveWindow.ScrollRow = 166
    ActiveWindow.ScrollRow = 167
    ActiveWindow.ScrollRow = 168
    ActiveWindow.ScrollRow = 169
    ActiveWindow.ScrollRow = 170
    ActiveWindow.ScrollRow = 171
    ActiveWindow.ScrollRow = 172
    ActiveWindow.ScrollRow = 173
    ActiveWindow.ScrollRow = 174
    ActiveWindow.ScrollRow = 175
    ActiveWindow.ScrollRow = 176
    ActiveWindow.ScrollRow = 177
    ActiveWindow.ScrollRow = 178
    ActiveWindow.ScrollRow = 179
    ActiveWindow.ScrollRow = 180
    ActiveWindow.ScrollRow = 181
    ActiveWindow.ScrollRow = 182
    ActiveWindow.ScrollRow = 183
    ActiveWindow.ScrollRow = 184
    ActiveWindow.ScrollRow = 185
    ActiveWindow.ScrollRow = 186
    ActiveWindow.ScrollRow = 187
    ActiveWindow.ScrollRow = 188
    ActiveWindow.ScrollRow = 189
    ActiveWindow.ScrollRow = 190
    ActiveWindow.ScrollRow = 191
    ActiveWindow.ScrollRow = 192
    ActiveWindow.ScrollRow = 193
    ActiveWindow.ScrollRow = 194
    ActiveWindow.ScrollRow = 195
    ActiveWindow.ScrollRow = 196
    ActiveWindow.ScrollRow = 197
    ActiveWindow.ScrollRow = 198
    ActiveWindow.ScrollRow = 199
    ActiveWindow.ScrollRow = 200
    ActiveWindow.ScrollRow = 201
    ActiveWindow.ScrollRow = 202
    ActiveWindow.ScrollRow = 203
    ActiveWindow.ScrollRow = 204
    ActiveWindow.ScrollRow = 205
    ActiveWindow.ScrollRow = 206
    ActiveWindow.ScrollRow = 207
    ActiveWindow.ScrollRow = 208
    ActiveWindow.ScrollRow = 209
    ActiveWindow.ScrollRow = 210
    ActiveWindow.ScrollRow = 211
    ActiveWindow.ScrollRow = 212
    ActiveWindow.ScrollRow = 213
    ActiveWindow.ScrollRow = 214
    ActiveWindow.ScrollRow = 215
    ActiveWindow.ScrollRow = 216
    ActiveWindow.ScrollRow = 217
    ActiveWindow.ScrollRow = 218
    ActiveWindow.ScrollRow = 219
    ActiveWindow.ScrollRow = 220
    ActiveWindow.ScrollRow = 221
    ActiveWindow.ScrollRow = 222
    ActiveWindow.ScrollRow = 223
    ActiveWindow.ScrollRow = 224
    ActiveWindow.ScrollRow = 225
    ActiveWindow.ScrollRow = 226
    ActiveWindow.ScrollRow = 227
    ActiveWindow.ScrollRow = 228
    ActiveWindow.ScrollRow = 227
    ActiveWindow.ScrollRow = 226
    ActiveWindow.ScrollRow = 225
    ActiveWindow.ScrollRow = 224
    ActiveWindow.ScrollRow = 223
    ActiveWindow.ScrollRow = 222
    ActiveWindow.ScrollRow = 221
    ActiveWindow.ScrollRow = 220
    ActiveWindow.ScrollRow = 219
    ActiveWindow.ScrollRow = 218
    ActiveWindow.ScrollRow = 216
    ActiveWindow.ScrollRow = 215
    ActiveWindow.ScrollRow = 214
    ActiveWindow.ScrollRow = 213
    ActiveWindow.ScrollRow = 212
    ActiveWindow.ScrollRow = 211
    ActiveWindow.ScrollRow = 210
    ActiveWindow.ScrollRow = 209
    ActiveWindow.ScrollRow = 208
    ActiveWindow.ScrollRow = 207
    ActiveWindow.ScrollRow = 205
    ActiveWindow.ScrollRow = 204
    ActiveWindow.ScrollRow = 201
    ActiveWindow.ScrollRow = 200
    ActiveWindow.ScrollRow = 198
    ActiveWindow.ScrollRow = 197
    ActiveWindow.ScrollRow = 194
    ActiveWindow.ScrollRow = 193
    ActiveWindow.ScrollRow = 191
    ActiveWindow.ScrollRow = 189
    ActiveWindow.ScrollRow = 188
    ActiveWindow.ScrollRow = 186
    ActiveWindow.ScrollRow = 183
    ActiveWindow.ScrollRow = 181
    ActiveWindow.ScrollRow = 179
    ActiveWindow.ScrollRow = 177
    ActiveWindow.ScrollRow = 175
    ActiveWindow.ScrollRow = 173
    ActiveWindow.ScrollRow = 170
    ActiveWindow.ScrollRow = 168
    ActiveWindow.ScrollRow = 166
    ActiveWindow.ScrollRow = 165
    ActiveWindow.ScrollRow = 162
    ActiveWindow.ScrollRow = 160
    ActiveWindow.ScrollRow = 159
    ActiveWindow.ScrollRow = 157
    ActiveWindow.ScrollRow = 155
    ActiveWindow.ScrollRow = 153
    ActiveWindow.ScrollRow = 151
    ActiveWindow.ScrollRow = 148
    ActiveWindow.ScrollRow = 146
    ActiveWindow.ScrollRow = 144
    ActiveWindow.ScrollRow = 142
    ActiveWindow.ScrollRow = 140
    ActiveWindow.ScrollRow = 137
    ActiveWindow.ScrollRow = 135
    ActiveWindow.ScrollRow = 132
    ActiveWindow.ScrollRow = 131
    ActiveWindow.ScrollRow = 129
    ActiveWindow.ScrollRow = 127
    ActiveWindow.ScrollRow = 125
    ActiveWindow.ScrollRow = 123
    ActiveWindow.ScrollRow = 121
    ActiveWindow.ScrollRow = 119
    ActiveWindow.ScrollRow = 116
    ActiveWindow.ScrollRow = 113
    ActiveWindow.ScrollRow = 109
    ActiveWindow.ScrollRow = 106
    ActiveWindow.ScrollRow = 102
    ActiveWindow.ScrollRow = 98
    ActiveWindow.ScrollRow = 94
    ActiveWindow.ScrollRow = 91
    ActiveWindow.ScrollRow = 89
    ActiveWindow.ScrollRow = 85
    ActiveWindow.ScrollRow = 82
    ActiveWindow.ScrollRow = 79
    ActiveWindow.ScrollRow = 76
    ActiveWindow.ScrollRow = 72
    ActiveWindow.ScrollRow = 69
    ActiveWindow.ScrollRow = 66
    ActiveWindow.ScrollRow = 62
    ActiveWindow.ScrollRow = 59
    ActiveWindow.ScrollRow = 56
    ActiveWindow.ScrollRow = 53
    ActiveWindow.ScrollRow = 49
    ActiveWindow.ScrollRow = 46
    ActiveWindow.ScrollRow = 42
    ActiveWindow.ScrollRow = 39
    ActiveWindow.ScrollRow = 36
    ActiveWindow.ScrollRow = 32
    ActiveWindow.ScrollRow = 30
    ActiveWindow.ScrollRow = 29
    ActiveWindow.ScrollRow = 27
    ActiveWindow.ScrollRow = 25
    ActiveWindow.ScrollRow = 23
    ActiveWindow.ScrollRow = 20
    ActiveWindow.ScrollRow = 18
    ActiveWindow.ScrollRow = 16
    ActiveWindow.ScrollRow = 15
    ActiveWindow.ScrollRow = 14
    ActiveWindow.ScrollRow = 12
    ActiveWindow.ScrollRow = 10
    ActiveWindow.ScrollRow = 9
    ActiveWindow.ScrollRow = 8
    ActiveWindow.ScrollRow = 6
    ActiveWindow.ScrollRow = 5
    ActiveWindow.ScrollRow = 4
    ActiveWindow.ScrollRow = 3
    ActiveWindow.ScrollRow = 2
    ActiveWindow.ScrollRow = 1
    
    With ActiveSheet.PivotTables("DailyHirePivot").PivotFields("Collection Branch")
        .Orientation = xlPageField
        .Position = 1
    End With
    ActiveWindow.SmallScroll Down:=-6
    ActiveSheet.PivotTables("DailyHirePivot").PivotFields("Collection Branch"). _
        CurrentPage = "(All)"
    With ActiveSheet.PivotTables("DailyHirePivot").PivotFields("Collection Branch")
        .PivotItems("BANGALORE").Visible = False
        .PivotItems("DELHI").Visible = False
        .PivotItems("HYDERABAD").Visible = False
        .PivotItems("KOLKATA").Visible = False
        .PivotItems("MUMBAI").Visible = False
        .PivotItems("PUNE").Visible = False
    End With
    
    With ActiveSheet.PivotTables("DailyHirePivot").PivotFields("Collection Branch"). _
        EnableMultiplePageItems = True
    Range("B3:ZZ1000").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 6
    ActiveWindow.ScrollColumn = 7
    ActiveWindow.ScrollColumn = 8
    ActiveWindow.ScrollColumn = 9
    ActiveWindow.ScrollColumn = 10
    ActiveWindow.ScrollColumn = 11
    ActiveWindow.ScrollColumn = 12
    ActiveWindow.ScrollColumn = 13
    ActiveWindow.ScrollColumn = 14
    ActiveWindow.ScrollColumn = 15
    ActiveWindow.ScrollColumn = 16
    ActiveWindow.ScrollColumn = 17
    ActiveWindow.ScrollColumn = 18
    ActiveWindow.ScrollColumn = 19
    ActiveWindow.ScrollColumn = 20
    ActiveWindow.ScrollColumn = 21
    Range(Selection, Selection.End(xlToLeft)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Copy
    Sheets("ChennaiClients").Select
    ActiveSheet.Paste
    
    Worksheets("ChennaiClients").Columns("A:AZ").AutoFit
    
    ActiveSheet.Range("B3", ActiveSheet.Range("B3").End(xlDown)).Select
    Selection.Font.Bold = True
    
    End With
    
        Sheets("PivotTable").Activate
        
     With ActiveSheet.PivotTables("DailyHirePivot").PivotFields("Collection Branch")
        .Orientation = xlPageField
        .Position = 1
    End With
    
    Set pt = Worksheets("PivotTable").PivotTables("DailyHirePivot")
    Set Field = pt.PivotFields("Collection Branch")
    NewCat = Worksheets("PivotTable").Range("C1").Value
    
    With pt
Field.ClearAllFilters
Field.CurrentPage = All
pt.RefreshTable
End With


ActiveSheet.PivotTables("DailyHirePivot").PivotFields("Collection Branch"). _
        CurrentPage = "(All)"
    With ActiveSheet.PivotTables("DailyHirePivot").PivotFields("Collection Branch")
        .PivotItems("BANGALORE").Visible = True
        .PivotItems("CHENNAI").Visible = False
        .PivotItems("DELHI").Visible = True
        .PivotItems("HYDERABAD").Visible = True
        .PivotItems("KOLKATA").Visible = True
        .PivotItems("MUMBAI").Visible = True
        .PivotItems("PUNE").Visible = True
    End With
    
    ActiveSheet.PivotTables("DailyHirePivot").PivotFields("Collection Branch"). _
    EnableMultiplePageItems = True
    
    Range("B3:ZZ1000").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 6
    ActiveWindow.ScrollColumn = 7
    ActiveWindow.ScrollColumn = 8
    ActiveWindow.ScrollColumn = 9
    ActiveWindow.ScrollColumn = 10
    ActiveWindow.ScrollColumn = 11
    ActiveWindow.ScrollColumn = 12
    ActiveWindow.ScrollColumn = 13
    ActiveWindow.ScrollColumn = 14
    ActiveWindow.ScrollColumn = 15
    ActiveWindow.ScrollColumn = 16
    ActiveWindow.ScrollColumn = 17
    ActiveWindow.ScrollColumn = 18
    ActiveWindow.ScrollColumn = 19
    ActiveWindow.ScrollColumn = 20
    ActiveWindow.ScrollColumn = 21
    Range(Selection, Selection.End(xlToLeft)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Copy
    Sheets("NetworkClients").Select
    ActiveSheet.Paste
    
    ActiveWindow.SmallScroll Down:=-15
    Selection.Copy
    Sheets("NetworkClients").Select
    ActiveSheet.Paste
    
    Worksheets("NetworkClients").Columns("A:AZ").AutoFit
    
    With ActiveSheet.Range("B1:AZ200")
    
  
       ActiveSheet.Range("B3", ActiveSheet.Range("B3").End(xlDown)).Select
       Selection.Font.Bold = True
       
        End With
End Sub

As You can see I have a huge file where I turn off Merge Shrink to fit and Wrap text.

Then I remove a fer blank rows and Columns.

Then I replace a few words which have space as I dont Know how to enter Space when coding

After that I filter To search the file for Daily Hires alone in Column "I"

Then I add a New Sheet called Daily Hire and I try to Separate Daily Hire data from sheet1 to The new Sheet

This is where I am finding the Issue: My code is
Code: 
Sub DailyHireSeperation()


    Dim DbExtract, DuplicateRecords As Worksheet
    Set DbExtract = ThisWorkbook.Sheets("MISCTrips")
    Set DuplicateRecords = ThisWorkbook.Sheets("DailyHire")


    DbExtract.Range("A1:ZZ10000").SpecialCells(xlCellTypeVisible).Copy
    DuplicateRecords.Cells(1, 1).PasteSpecial




    End Sub

As you can see I am giving the range A1:ZZ10000 But I get error also I dont want the whole cells there to be selected rather I want only the datas within the range to be coppied

Same thing goes when I try with this code below
Code: 
Sub FindnReplace()
    Dim target, cell As Range
    Dim i, k As String
    i = "DAILY HIRE"
    k = "DAILYHIRE"
    Set target = Sheets("MISCTrips").Range(Range("I1"), Range("I65536").End(xlUp))
    For Each cell In target
        If cell.Value = i Then cell.Value = k
    Next cell
End Sub

I dont know how to Give Range as the Range Keeps Changing for each file and I am not able to give Range like "I10000000" also as it says error and also takes a lot of time. I want to add a loop in both these senarios so that the selection is entire I column till the last Data in the Row.

Please Help Me it is very Important for me.:(</dailyhire>
 
Last edited by a moderator:

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
Sort by date Sort by votes
With the last code you posted, what happens with the code below as it looks like your error issue is probably because you are aren't qualifying all the ranges with the sheet.

Code: 
Sub FindnReplace()
    Dim target As Range, cell As Range
    Dim i As String, k As String
    i = "DAILY HIRE"
    k = "DAILYHIRE"
    Set target = Sheets("MISCTrips").Range(Sheets("MISCTrips").Range("I1"), Sheets("MISCTrips").Range("I" & Rows.Count).End(xlUp))
    For Each cell In target
        If cell.Value = i Then cell.Value = k
    Next cell
End Sub

Btw, I have also declared the variables correctly and also can you please use code tags in future as it makes your code easier to read which is one of the reasons why I haven't done anything with your first code.
 
Upvote 0
Dear Mark,

Many Thanks for your update I am truly very thank full for the correction done here it has helped me greatly.

I saw that after a few alterations with the code I was able to get the result with my Home system (Excel 2015) But the same when tried in my Office didnt work as they are using (Excel 2007) which I was unable to change due to IT issues.

I have found that there is a cell range in Excel 2007 above which the code to auto filter and copy dont work. As a round about I found that I can delete the unwanted Data in the field and then take the rest.

Hence I used the below code which dont delete the visible cells can you assist me with this please.

Code: 
Sub Test()
    Dim Rng As Range
    With ActiveSheet("MISCTrips").Range.AutoFilter(Field:=9, Criteria1:="HOTEL")
        Set Rng = .Offset(4).Delete(.Rows.Count - 1).SpecialCells(xlCellTypeVisible)
        .AutoFilter
        Rng.Delete
    End With
End Sub
 
Upvote 0
Untested and a bit of a guess as you don't state anything about your ranges or headers.

Rich (BB code): 
Sub Test()
    With Sheets("MISCTrips").Range("I1:I" & Sheets("MISCTrips").Range("I" & Rows.Count).End(xlUp).Row)
        .AutoFilter Field:=1, Criteria1:="HOTEL"
        .Offset(4).Resize(.Rows.Count - 4).SpecialCells(xlCellTypeVisible).EntireRow.Delete
        .AutoFilter
    End With
End Sub

although I suspect you might hit the same issue.
 
Last edited:
Upvote 0
Thank You Mark as you have guessed I am facing the same issue. as my range even after filtering is a lot more and worst case scenario is this data keeps growing may be I must some how implement a newer version of Excel in our office.
 
Upvote 0
There are other methods but don't know if I will get to post anything today.
 
Upvote 0
You must log in or register to reply here.

Similar threads

An Quala
  • Question Question
Should I name it Active.Sheet or sheet name?
Replies
8
Views
921
MARK858
MARK858
S
  • Question Question
Need code that adds multiple new rows to table to include a check box in column "L" for each new row
Replies
0
Views
544
sspatriots
S
E
  • Question Question
Help Cleaning Up / Optimizing VBA Code
Replies
6
Views
545
offthelip
O
C
  • Solved
Change pivot field with a variable in VBA
Replies
4
Views
183
cogswel__cogs
C
A
  • Question Question
Problem With My Loop(s) Being Able To Match Data?
Replies
2
Views
609
Ark68
A

Forum statistics

Threads
1,224,822
Messages
6,181,164
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top