Paseting filtered table info to different sheets

Gr2020

New Member
Joined
May 1, 2020
Messages
1
Office Version
  1. 2016
Platform
  1. Windows
Hi,

I have no clue of what am doing so your help is greatly appreciated. Somehow I manage to create a chart with slicers to filter the info and a small macro to copy the info were I click. I copied the slicers and a button to run the macro on every sheet. The info is on a sheet named DATA TABLE, the problem is that every time I run the macro the info is copied to sheet "1", if I want to run it in another sheet it will still paste it on sheet "1" unless I change the macro to Sheets("2").Select for example. How can I change the code so that the filtered info can be pasted regardless of the sheet name?, as sometimes these sheets get renamed.


VBA Code:
Sub COPYANDPASTE()
'
' COPYANDPASTE Macro


'
    Sheets("DATA TABLE").Select
    Range("D2:L1304").Select
    Selection.COPY
    Sheets("1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=True, Transpose:=False
      

'Reset all Slicers at once
Application.ScreenUpdating = False
Dim Cache As SlicerCache
For Each Cache In ActiveWorkbook.SlicerCaches
Cache.ClearAllFilters

Next Cache
Application.ScreenUpdating = True


End Sub

Also, I am running a macro to add new sheets (previously done by somebody else)


VBA Code:
Sub AddNewSheet()
' Insert Row and Bid Formulas for new item
' CURSOR MUST BE IN THE DESCRIPTION, ROW AND COLUMN

' Uses message box to confirm adding row the right place
Dim Response As VbMsgBoxResult
    Response = MsgBox("Do you want to insert row here", vbQuestion + vbYesNo)
    If Response = vbNo Then Exit Sub

'Inserts row where cursor is:
    Selection.EntireRow.Insert
'Moves to cells and inputs formulas:
    ActiveCell.Offset(0, -4).Select
    ActiveCell.FormulaR1C1 = "=IF(RC[15]=0,"""",""F"")"
    ActiveCell.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = "=IF(RC[16]=0,"""",""I"")"
    ActiveCell.Offset(0, 8).Select
    ActiveCell.FormulaR1C1 = "=+RC[-2]+RC[-1]"
    ActiveCell.Offset(0, 4).Select
    ActiveCell.FormulaR1C1 = "=+RC[-4]/RC[-2]"
'Move cursor back to Item Number Column:
    ActiveCell.Offset(0, -8).Select


'Will add sheet after last sheet and name it "NEW"

Dim ActNm As String
'Create new worksheet in the active workbook, put it after last sheet
With ActiveWorkbook.Sheets
.Add after:=Worksheets(Worksheets.Count)
End With
ActNm = ActiveSheet.Name
ActiveSheet.Name = "NEW"
'copies specified range to where cursor is
Sheets("MASTER").Select
    Cells.Select
    Selection.COPY
    Sheets("NEW").Select
    Cells.Select
    ActiveSheet.Paste
        

'go to proposal sheet
Sheets("PROPOSAL").Select
ActiveCell.Offset(0, -1).Select

' Insert Row and Add Formulas Macro
' Add formulas from sheet "NEW" to proposal, rename sheet to next number


' Uses message box to confirm adding row the right place
'Dim Answer As VbMsgBoxResult
'   Answer = MsgBox("Are you still in the right spot?", vbQuestion + vbYesNo)
  '  If Answer = vbNo Then Exit Sub


' YOU MUST START IN THE ITEM DESCRIPTION ROW and COLUMN
    ActiveCell.FormulaR1C1 = "=NEW!R5C2"
        ActiveCell.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = "=NEW!R6C2"
        ActiveCell.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = "=NEW!R5C9"
        ActiveCell.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = "=NEW!R47C11"
        ActiveCell.Offset(0, 4).Select
    ActiveCell.FormulaR1C1 = "=NEW!R37C5"
        ActiveCell.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = "=NEW!R37C6"
        ActiveCell.Offset(0, 3).Select
    ActiveCell.FormulaR1C1 = _
        "=NEW!R35C11+NEW!R37C11+NEW!R40C11+NEW!R41C11"
        ActiveCell.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = "=NEW!R39C8"
        ActiveCell.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = "=NEW!R46C8"
        ActiveCell.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = "=NEW!R45C11"
        ActiveCell.Offset(0, -14).Select
'Rename worksheet "New" to next item number
    Worksheets("NEW").Activate
    ActiveSheet.Name = ActiveWorkbook.Sheets.Count - 3
    Range("B5").Select

End Sub


Thank you very much for your attention to this matter,

Have a great day!!!
 

Attachments

  • Capture.PNG
    Capture.PNG
    50.7 KB · Views: 3
Last edited by a moderator:

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

Watch MrExcel Video

Forum statistics

Threads
1,129,528
Messages
5,636,837
Members
416,945
Latest member
Himu

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
Top