Macro code not pasting where I want it to

[badam1212]

Hello- the below code from my macro is not pasting in cell A1 on the sheet I want it to, but instead is pasting into a random place on the sheet. Can I add something here in order for the results at the end of my macro to paste in A1?

*It selects data from another workbook and then opens template.xlsm. Here is where I want it to paste into cell A1. (the code used to have Range("D13").Select, but I changed that in an attempt to solve this issue.

Windows("template.xlsm").Activate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A1").Select

Thank you!

 

[Fluff]

Change Selection to Range("A1")

 

[badam1212]

Perfect, thank you!

 

[Fluff]

Glad to help & thanks for the feedback

 

[badam1212]

Sorry - maybe you can help with this too? Same code, but the workbook I'm copying from, I would like it to close w/o saving changes before my macro is complete.

So I'm selecting data from "CWL Setup Tracker.xlsm" and then:

(opening my "template" file):

Windows("template.xlsm").Activate
Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A1").Select

(pasting everything I want into A1)

But before I show my MsgBox indicating the macro run is complete, I want to close out of "CWL Setup Tracker.xlsm". Does that make sense?

I appreciate your help! Let me know if you need more info.

 

[Fluff]

Could you supply your entire code.
When doing so please use code tags, the # icon in the reply window

 

[badam1212]

Sub AABig3Template()'
' AABig3Template Macro
'


'
    Workbooks.Open Filename:= _
        "\\hijk.com\shared\Ecommerce\Reporting\2018 Reports\Content\Setup Tracker\CWL Setup Tracker.xlsm"
    Sheets("DATA").Select
    ActiveSheet.ListObjects("Table_Detail").Range.AutoFilter Field:=1, Criteria1 _
        :=Array("2017/10/01", "2017/11/01", "2017/12/01", "2018/01/01", "2018/02/01", _
        "2018/03/01", "2018/04/01", "2018/05/01", "2018/06/01", "2018/07/01", "2018/08/01", _
        "2018/09/01"), Operator:=xlFilterValues
    ActiveSheet.ListObjects("Table_Detail").Range.AutoFilter Field:=2, Criteria1 _
        :="Smith, John D"
    ActiveSheet.ListObjects("Table_Detail").Range.AutoFilter Field:=3, Criteria1 _
        :=Array("Jones, Sam B", "Adams, Kate", "Holmes, Erin N"), Operator:= _
        xlFilterValues
    ActiveWindow.SmallScroll ToRight:=24
    Range("Table_Detail[[#Headers],[SUB_CLASS_NUMBER]]").Select
    ActiveWindow.SmallScroll ToRight:=-5
    ActiveSheet.ListObjects("Table_Detail").Range.AutoFilter Field:=23, _
        Criteria1:=Array("1577", "544", "9835"), Operator:=xlFilterValues
    ActiveWindow.ScrollColumn = 19
    ActiveWindow.ScrollColumn = 1
    Range("Table_Detail[[#Headers],[Target Month]:[DEPARTMENT_NUMBER]]").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Windows("template.xlsm").Activate
    Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("A1").Select
    
MsgBox "Done."


End Sub

 

[Fluff]

You forgot the code tags

try

Sub AABig3Template() '
' AABig3Template Macro
'

   Dim Wbk As Workbook
'
    Set Wbk = Workbooks.Open(FileName:= _
        "\\hijk.com\shared\Ecommerce\Reporting\2018 Reports\Content\Setup Tracker\CWL Setup Tracker.xlsm")
    Sheets("DATA").Select
    ActiveSheet.ListObjects("Table_Detail").Range.AutoFilter Field:=1, Criteria1 _
        :=Array("2017/10/01", "2017/11/01", "2017/12/01", "2018/01/01", "2018/02/01", _
        "2018/03/01", "2018/04/01", "2018/05/01", "2018/06/01", "2018/07/01", "2018/08/01", _
        "2018/09/01"), Operator:=xlFilterValues
    ActiveSheet.ListObjects("Table_Detail").Range.AutoFilter Field:=2, Criteria1 _
        :="Smith, John D"
    ActiveSheet.ListObjects("Table_Detail").Range.AutoFilter Field:=3, Criteria1 _
        :=Array("Jones, Sam B", "Adams, Kate", "Holmes, Erin N"), Operator:= _
        xlFilterValues
    ActiveWindow.SmallScroll ToRight:=24
    Range("Table_Detail[[#Headers],[SUB_CLASS_NUMBER]]").Select
    ActiveWindow.SmallScroll ToRight:=-5
    ActiveSheet.ListObjects("Table_Detail").Range.AutoFilter Field:=23, _
        Criteria1:=Array("1577", "544", "9835"), Operator:=xlFilterValues
    ActiveWindow.ScrollColumn = 19
    ActiveWindow.ScrollColumn = 1
    Range("Table_Detail[[#Headers],[Target Month]:[DEPARTMENT_NUMBER]]").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Windows("template.xlsm").Activate
    Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("A1").Select
    Wbk.Close False
MsgBox "Done."


End Sub

 

[badam1212]

I pressed that # button, sorry if I didn't do it correctly!

It works perfectly, but there is another message that pops up that says something about there's a large amount of data, do you want to save it to be able to use it for later, etc...with a Yes, No, Cancel option. I can just press No and that workbook goes away, but can we incorporate that into the code or should I just leave it as is?

Thanks so much!

 

[Fluff]

Add the 2 lines in blue

    Range("A1").Select
    [COLOR=#0000ff]Application.DisplayAlerts = False[/COLOR]
    Wbk.Close False
  [COLOR=#0000ff]  Application.DisplayAlerts = True[/COLOR]
MsgBox "Done."