VBA Pasting from table to different sheets without using sheets("x").Select HELP!!!

[GR2020]

Hello there!!!

Somehow I manage to create a table 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.



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)



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




https://www.mrexcel.com/board/data/attachments/12/12753-a8a9a46d209589e3b4fc6065c34938c1.jpg (https://www.mrexcel.com/board/attachments/capture-png.12775/)
Capture.PNG (https://www.mrexcel.com/board/attachments/capture-png.12775/)
50.7 KBViews: 0

[Hightree]

Hello there!!!

Somehow I manage to create a table 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.




At First do not use Select Or Offset

[paulked]

Hi and welcome to the forum.

Try this for your copy and paste:



Sub COPYANDPASTE()
'
' COPYANDPASTE Macro


'
Sheets("DATA TABLE").Range("D2:L1304").Copy ActiveSheet.Range("D2")

'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



I didn't read the rest of your post, the colour hurts my eyes!

[paulked]

@ Hightree

Did you have to 'Reply With Quote'? You hurt my eyes again! :rofl::devil2: