Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Jun 2002
    Location
    Johannesburg, Gauteng, South Africa
    Posts
    59
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Copy from 1 workbook to a hidden worksheet (Excel

    Hi,
    I need to copy data from one workbook to a hidden sheet in another protected workbook. The advice given by JKP in <post#=265,067>post 265,067</post#> works fine except the range to be copied is dynamic and will not always be the same. I will need to copy the range from A2 to the last cell with information in.

    I can do this within the same workbook with the following code (I can't remember where I managed to borrow it from) but I don't seem to be able to to modify it to work across workbooks.

    With Sheet4.Columns("a:a").Find(Sheet4.Range("a2"))
    Worksheets("Sheet4").Range(.Offset(0, 0), .Offset(Sheet4.UsedRange.Rows.Count - 1, Sheet1.UsedRange.Columns.Count - 1)).Copy _
    Destination:=Worksheets("Sheet1").Range("A4")
    End With

    The name of the workbook / sheet that I want to copy from is "Deans_Classifications.xls" / "Sheet1" and that from which I am running the code and wish to copy to is "Classifications_20070101.xls" / "Sheet1"

    Any assistance will, as always, be appreciated.

    Thanks

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Copy from 1 workbook to a hidden worksheet (Excel

    You could use

    Workbooks("Deans_Classifications.xls").Worksheets( "Sheet1").UsedRange.Copy _
    Destination:=Workbooks("Classifications_20070101.x ls").Worksheets("Sheet1").Range("A1")

  3. #3
    Star Lounger
    Join Date
    Jun 2002
    Location
    Johannesburg, Gauteng, South Africa
    Posts
    59
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Copy from 1 workbook to a hidden worksheet (Ex

    <img src=/S/music.gif border=0 alt=music width=97 height=29> Thanks Hans, as usual you leave me <img src=/S/starstruck.gif border=0 alt=starstruck width=15 height=15>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •