Copy from another sheet, paste to current/active

[JonathanK1]

I need a macro that will copy data from another spreadsheet and paste it into the current spreadsheet (where the macro is stored). I'm working off of this (below). This opens up the other workbook (and appears to copy) but it's still pasting into a new workbook, which is not what I want.

Sub Button6_Click()
Dim TheAnswer As String
Set wb1 = Workbooks.opm (Filename:="blahblahblah.xlsm"
Dim working As Worksheet, dumping As Workbook
Set working = ActiveSheet
TheAnswer = LCase$(InputBox("Enter state below"))
Set dumping = Workbooks.Add
For x = 1 To 17
working.Rows(x).EntireRow.Copy
dumping.Activate
ActiveSheet.Paste <---------------------is this what needs to be changed???
ActiveCell.Offset(1).Select
Next
For x = 1 To working.Cells.SpecialCells(xlCellTypeLastCell).Row
If LCase$(working.Cells(x, 8).Value) = TheAnswer Then
working.Rows(x).EntireRow.Copy
dumping.Activate
ActiveSheet.Paste
ActiveCell.Offset(1).Select
End If
Next
Application.CutCopyMode = False
Cells.Sort Key1:=Range("C:C"), Order1:=xlAscending, _
Key2:=Range("Q:Q"), Order2:=xlDescending, Header:=xlYes
End Sub

 

[Keliyra]

Hi Jonathan -

At first glance, this is your issue. You're adding a new workbook here
Set dumping = Workbooks.Add

 

[JonathanK1]

Hi Keliyra,

Yes, I can see that now. I tried replacing the "Workbooks.Add" with "ActiveWorkbook" and a few other variations but nothing seems to be working. Do you have any idea what I would change it to? I tried removing it, but that created an error so it must be something I need to keep.

Sorry, I'm just learning about all of this macro stuff :P

I appreciate your help!

J-

 

[JonathanK1]

Can anyone else assist?

 

[Keliyra]

Sub Button6_Click()
Dim TheAnswer As String
Set wb1 = Workbooks.opm (Filename:="blahblahblah.xlsm"
Dim working As Worksheet, dumping As Worksheet
Set working = ActiveSheet
TheAnswer = LCase$(InputBox("Enter state below"))
Set dumping = Worksheet.Add
For x = 1 To 17
working.Rows(x).EntireRow.Copy
dumping.Activate
ActiveSheet.Paste
ActiveCell.Offset(1).Select
Next
For x = 1 To working.Cells.SpecialCells(xlCellTypeLastCell).Row
If LCase$(working.Cells(x, 8).Value) = TheAnswer Then
working.Rows(x).EntireRow.Copy
dumping.Activate
ActiveSheet.Paste
ActiveCell.Offset(1).Select
End If
Next
Application.CutCopyMode = False
Cells.Sort Key1:=Range("C:C"), Order1:=xlAscending, _
Key2:=Range("Q:Q"), Order2:=xlDescending, Header:=xlYes
End Sub

Try that - haven't had time to test so not 100% sure about it - but basically changes it from adding a new workbook to a new sheet in the current workbook.