+ Reply to Thread
Results 1 to 3 of 3

Copying Data To Another Worksheet

  1. #1
    Registered User
    Join Date
    08-31-2006
    Posts
    2

    Copying Data To Another Worksheet

    Hi All,
    I'm relatively new to Macro's and VBA and am having difficulty getting a macro set up.

    What I want it to do is to take 9 value's from cells in sheet HL2 (D155-F157)and copy them into 9 cells in another sheet named PD (E3-G5). I will need the macro to run on 5 other sheets as well as do some other bits and pieces.

    I have looked through posts already and tried lots of things but nothing has worked. How do you reference a worksheet which has spaces in its name?

    Hopefully if someone can help me get this section working I can get the other functions I need set up.

    Thanks

  2. #2
    Valued Forum Contributor
    Join Date
    08-26-2006
    Location
    -
    MS-Off Ver
    2010
    Posts
    388
    This should give you the basic idea, adjust the ranges and sheet names to suit:

    Sub test()
    Dim rng1 As Range
    Dim rng2 As Range
    Dim srcWS As Worksheet
    Dim dstWS As Worksheet

    Set srcWS = ActiveSheet
    Set dstWS = Worksheets("the other sheet")

    Set rng1 = srcWS.Range("a1:c3") '9 cells
    Set rng2 = dstWS.Range("d4") 'only require top left of range

    rng1.Copy
    rng2.PasteSpecial xlPasteValues
    Application.CutCopyMode = False
    End Sub

  3. #3
    Registered User
    Join Date
    08-31-2006
    Posts
    2
    Thanks a lot! That worked 1st time once i'd changed the sheet names.

    Took me about 2 hours to get the loop sorted (as it needed to paste the values into a list).

    I have however come across a problem with trying to paste some other values into a merged cell. I get the error:

    "Run-time error '1004':
    This operation requires the merged cells to be identically sized.

    As far as i can see the cells are the same size. Here's the code:

    Set dtdst = dstWS.Range("C" & dstmod)
    dtsrc.Copy
    dtdst.PasteSpecial xlPasteValues
    Application.CutCopyMode = False

    dstmod is an integer I have used to adjust where its pasted.

    I've set dtsrc and dtdst as Range's even though its a single cell, would that have something to do with it?

    Cheers again for any help.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1