Macro

[RD]

I have the following macro which is supposed to add a new sheet, open up the
designated file, copy the first 3 lines, go back to the original spreadsheet
and paste the copied 3 lines.

However, it seems to be stopping at the Selection.Paste line because it
can't determine which spreadsheet to go to.

How do I fix this? At any time I run this I could be in a diffferent
spreadsheet creating jnls.

Thanks in advance
Rick

Sub newupload()
'
' newupload Macro
'
' Sheets("Sheet1").Select
Sheets.Add
Workbooks.Open Filename:="I:\Jnl upload.xls"
Rows("1:3").Select
Selection.Copy
ActiveSheet.Activate
Selection.Paste
Windows("Jnl upload.xls").Activate
ActiveWindow.Close
End Sub

 

[RussellT]

What is the SheetName in the Jnl upload.xls file that has the data you want
to copy?

 

[Dave Peterson]

You can keep track of where you started and what you used:

Option Explicit
Sub newupload()

Dim CurWks As Worksheet
Dim Wkbk As Workbook
Dim RngToCopy As Range
Dim DestCell As Range

ActiveWorkbook.Worksheets.Add
Set CurWks = ActiveSheet

Set Wkbk = Workbooks.Open(Filename:="I:\Jnl upload.xls")

With Wkbk.Worksheets(1) 'or with wkbk.worksheets("Sheet9999")
Set RngToCopy = .Rows("1:3")
End With

RngToCopy.Copy _
Destination:=CurWks.Range("A1")

Wkbk.Close savechanges:=False
End Sub

 

[JLatham]

Dave Peterson has given you a good immediate solution.

What was killing you in your original code is the way things happen in Excel
when you open a workbook from within another:
Initially the active workbook is (most likely) the one with the code in it,
but the moment the other workbook gets opened, it becomes the active
workbook, and the last displayed worksheet in it becomes the ActiveSheet.

So, your original code was making a copy of the 1st 3 rows of information in
the last sheet accessed in Jnl upload.xls, and that sheet had become the
ActiveSheet. So the ActiveSheet.Activate line of code was, in effect, saying
"activate the currently active sheet" -- which is the sheet in that other
workbook, not the sheet that HAD BEEN active in the workbook with the code in
it as you probably thought. That in turn made the Selection.Paste pretty
much a do-nothing line because the paste was being applied to the very
selection/range it had been copied from.

 

[RD]

Thanks Dave that sorted me out.

You can keep track of where you started and what you used:

Option Explicit
Sub newupload()

Dim CurWks As Worksheet
Dim Wkbk As Workbook
Dim RngToCopy As Range
Dim DestCell As Range

ActiveWorkbook.Worksheets.Add
Set CurWks = ActiveSheet

Set Wkbk = Workbooks.Open(Filename:="I:\Jnl upload.xls")

With Wkbk.Worksheets(1) 'or with wkbk.worksheets("Sheet9999")
Set RngToCopy = .Rows("1:3")
End With

RngToCopy.Copy _
Destination:=CurWks.Range("A1")

Wkbk.Close savechanges:=False
End Sub