ExcelBanter - View Single Post

bobbo

wrote:
I altered it to this:

Dim strFile As String

strFile = InputBox( _
prompt:="What is the path and name of the new Admin file?", _
Title:="Admin File Update", _
Default:="T:\General\John Bock\my documents\AdminFile.xls")

Workbooks.Open Filename:=strFile
Selection.CurrentRegion.Select
Selection.Copy
Worksheets("All Movements").Select
Range("A1").Select
Selection.PasteSpecial
-------------------------------------------------------

It doesn't error out, but it also doesn't work at all. It opens the new
file as asked, and copies it. However it does not paste the sheet into
the other workbook.

Any ideas?
Thanks.

I just glanced at your code the first time around and just told you how
not to get the error. The reason that nothing is happening is that when
you open a workbook in code it becomes the activeworkbook. So selection
would return the cell A1 of the file that you had just opened.Since you
copied and pasted in the same cell the same range it appears nothing
happened. Looking at it further I think that you are trying to copy the
current region to the "All Movements" sheet or you might want to copy
the entire worksheet contents to it. Here is code that I wrote to
accomplish a similar task.

Dim cpyrg As Range
Dim pstrg As Range
Dim rg1 As Range
Dim wbknm As String
Dim shtnm As String
Dim rgadd As String

wbknm = ActiveWorkbook.Name
shtnm = ActiveSheet.Name
rgadd = ActiveSheet.UsedRange.Address

' these statements recall the original details of the range I would
like to copy.
' The UsedRange finds the entire range that has any info on a sheet.
' You can replace it with Activecell.CurrentRegion if that is what
you need

Workbooks.Open Filename:="C:\Documents and Settings\acctemp2\My
Documents\clrstest.xls"

Set cpyrg = Workbooks(wbknm).Worksheets(shtnm).Range(rgadd)
' This copies the range that I want to the clipboard.
' I could have declared it directly above the Workbooks.Open
Statement.
' Set cpyrg = ActiveWorkbook.ActiveSheet.ActiveSheet.UsedRange
' This would negate the need for all the string variables. I have
used both
' methods it depends on what you are doing.
cpyrg.Copy

Set pstrg = ActiveSheet.Range("a1")
pstrg.PasteSpecial
' If you are copying to a master spreadsheet you may want the
original values to remain.
' To insert the copied range replace the pstrg.PasteSpecial statement
with
' pstrg.Insert Shift:=xldoiwn

I hope this helps. A little narrative about what you are trying to do
often helps in answering questions.

HTH