Copy, paste to active workbook?

[JonathanK1]

Me again (sorry, I've been posting quite a bit, I know - but I'm almos
finished).

Below is the macro I have. When I run it, it opens a spreadsheet i
another folder and pulls from it, then opens a new workbook to paste i
into. What I'd like to do is have the data paste into the workbook th
macro is being run from. So, no new workbook. I'd like it to past
into the current/active workbook, the one with the macro in it (and i
would be nice if I could specify the range it pastes into too - startin
A5 to Q5, for example.

I think part of the problem is the add part below, but I've replaced i
with "active workbook" (and a dozen variations) and it only errors out.


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 <-------------------------is this th
problem?
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
etc etc etc

 

[Claus Busch]

Hi Jonathan,

Am Thu, 28 Mar 2013 11:41:18 +0000 schrieb JonathanK1:

Me again (sorry, I've been posting quite a bit, I know - but I'm almost
finished).

Below is the macro I have. When I run it, it opens a spreadsheet in
another folder and pulls from it, then opens a new workbook to paste it
into. What I'd like to do is have the data paste into the workbook the
macro is being run from. So, no new workbook. I'd like it to paste
into the current/active workbook, the one with the macro in it (and it
would be nice if I could specify the range it pastes into too - starting
A5 to Q5, for example.

I hope that I understood you correctly.
Set your referencies explicit. Then you don't have to select or
activate. If both workbooks are open (in my case "Test" and
"blahbla...") you can use following code:

Sub Button6_Click()
Dim LRow As Long
Dim wb1 As Workbook

Set wb1 = Workbooks.Open(Filename:="blahblahblah.xlsm")
With Workbooks("Test").Sheets("Sheet1")
.Range("A1:Q17").Copy _
wb1.Sheets(1).[A1]

LRow = .Cells(.Rows.Count, 1).End(xlUp).Row
.Range("A1:Q" & LRow).Copy _
wb1.Sheets(1).[A18]
End With
End Sub


Regards
Claus Busch