VBA - disable page jumping when code runs

[CollegeExcel]

Hi all,

As the title mentions.

I build a VBA macro which filters data based on Month, and copy/paste it into a new sheet. However every time I run this code, it jumps to the page where it should paste. It just tiring to click back on the main sheet every time.

Wondering what function I can use to disable that? Would appreciate any ideas.

Here is a snippet:

Code:

Sub copy_data_2_new_sheets()


Dim count_col As Long
Dim count_row As Long
Dim reg As String
Dim check As Long


check = 0
reg = Sheet1.Cells(2, 9).Text


On Error GoTo oops
Sheets.Add(After:=Sheets(Sheets.Count)).Name = reg

check = 1

oops:

If check = 0 Then
    Application.DisplayAlerts = False
    ActiveSheet.Delete
    Application.DisplayAlerts = False


End If



Sheets(reg).Cells.ClearContents
Sheet1.Activate
count_col = WorksheetFunction.CountA(Range("A4", Range("A4").End(xlToRight)))
count_row = WorksheetFunction.CountA(Range("A4", Range("A4").End(xlDown)))

ActiveSheet.Range("A4").AutoFilter Field:=2, Criteria1:=reg

Sheet1.Range(Cells(4, 1), Cells(count_row, count_col)). _
SpecialCells(xlCellTypeVisible).Copy
Sheets(reg).Cells(1, 1).PasteSpecial xlPasteValues
Application.CutCopyMode = False
Sheet1.ShowAllData
Sheet1.AutoFilterMode = False

Sheets(reg).Activate
Cells.Select
Cells.EntireColumn.AutoFit
Range("A2").Select



End Sub

 

[Fluff]

Hi & welcome to MrExcel.
How about

Sub copy_data_2_new_sheets()
   Dim reg As String
   
   reg = Sheet1.Cells(2, 9).Text
   
   If Not Evaluate("isref('" & reg & "'!A1)") Then
      Sheets.Add(, Sheets(Sheets.count)).Name = reg
   End If
   
   Sheets(reg).Cells.ClearContents
   With Sheet1
      .Range("A4").AutoFilter Field:=2, Criteria1:=reg
      .AutoFilter.Range.Copy
      Sheets(reg).Cells(1, 1).PasteSpecial xlPasteValues
      .ShowAllData
      .AutoFilterMode = False
   End With
   Application.CutCopyMode = False

   Sheets(reg).Cells.EntireColumn.AutoFit
End Sub

 

[CollegeExcel]

Hi & welcome to MrExcel.
How about

Sub copy_data_2_new_sheets()
   Dim reg As String
  
   reg = Sheet1.Cells(2, 9).Text
  
   If Not Evaluate("isref('" & reg & "'!A1)") Then
      Sheets.Add(, Sheets(Sheets.count)).Name = reg
   End If
  
   Sheets(reg).Cells.ClearContents
   With Sheet1
      .Range("A4").AutoFilter Field:=2, Criteria1:=reg
      .AutoFilter.Range.Copy
      Sheets(reg).Cells(1, 1).PasteSpecial xlPasteValues
      .ShowAllData
      .AutoFilterMode = False
   End With
   Application.CutCopyMode = False

   Sheets(reg).Cells.EntireColumn.AutoFit
End Sub

Thank you!

 

[CollegeExcel]

Hi & welcome to MrExcel.
How about

Sub copy_data_2_new_sheets()
   Dim reg As String
 
   reg = Sheet1.Cells(2, 9).Text
 
   If Not Evaluate("isref('" & reg & "'!A1)") Then
      Sheets.Add(, Sheets(Sheets.count)).Name = reg
   End If
 
   Sheets(reg).Cells.ClearContents
   With Sheet1
      .Range("A4").AutoFilter Field:=2, Criteria1:=reg
      .AutoFilter.Range.Copy
      Sheets(reg).Cells(1, 1).PasteSpecial xlPasteValues
      .ShowAllData
      .AutoFilterMode = False
   End With
   Application.CutCopyMode = False

   Sheets(reg).Cells.EntireColumn.AutoFit
End Sub

I also have a question that it used "reg" to filter

Hi & welcome to MrExcel.
How about

Sub copy_data_2_new_sheets()
   Dim reg As String
  
   reg = Sheet1.Cells(2, 9).Text
  
   If Not Evaluate("isref('" & reg & "'!A1)") Then
      Sheets.Add(, Sheets(Sheets.count)).Name = reg
   End If
  
   Sheets(reg).Cells.ClearContents
   With Sheet1
      .Range("A4").AutoFilter Field:=2, Criteria1:=reg
      .AutoFilter.Range.Copy
      Sheets(reg).Cells(1, 1).PasteSpecial xlPasteValues
      .ShowAllData
      .AutoFilterMode = False
   End With
   Application.CutCopyMode = False

   Sheets(reg).Cells.EntireColumn.AutoFit
End Sub

This is a different question related to this code.


How do I make it filter based on "Contains"? The issue is that I have a dynamic year that varies so unless I define it in my list...it doesn't know what to pick.
Hope this make sense. Please let me know and I can further clarify.

 

[Fluff]

Use it like

.Range("A4").AutoFilter Field:=2, Criteria1:="*" & reg & "*"

 

[CollegeExcel]

Use it like

.Range("A4").AutoFilter Field:=2, Criteria1:="*" & reg & "*"

You're awesome! Exactly what I needed.

 

[Fluff]

You're welcome & thanks for the feedback.