Copy, paste to active workbook?
 

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 think part of the problem is the add part below, but I've replaced it 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 the 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.

Copy, paste to active workbook?
 

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
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2