Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Jun 2011
    Posts
    188
    Thanks
    62
    Thanked 0 Times in 0 Posts

    Used range tto one sheet specific column to master sheet A2

    Hi All,

    I need a code that copy the used range of one sheet specific coolumn and paste to master sheet starting cell A2.



    cheers

  2. #2
    New Lounger
    Join Date
    Dec 2009
    Location
    Goulburn. NSW, Australia
    Posts
    20
    Thanks
    3
    Thanked 4 Times in 4 Posts

    Copy selected column from used range

    Is this the sort of thing you're looking for?

    Code:
    Sub CopyToMaster()
        Dim wksMaster As Worksheet
        Dim wksSlave As Worksheet
        Dim rngCopy As Range
        Dim lngCol As Long
        Set wksMaster = ThisWorkbook.Worksheets("Master")
        Set wksSlave = ThisWorkbook.Worksheets("Slave")
        lngCol = 4  ' Column D
        Set rngCopy = Application.Intersect(wksSlave.UsedRange, wksSlave.Columns(lngCol))
        If Not rngCopy Is Nothing Then
             rngCopy.Copy Destination:=wksMaster.Range("A2")
        End If
        Set rngCopy = Nothing
        Set wksMaster = Nothing
        Set wksSlave = Nothing
    End Sub

  3. The Following User Says Thank You to CephasOz For This Useful Post:

    farrukh (2012-01-19)

  4. #3
    2 Star Lounger
    Join Date
    Jun 2011
    Posts
    188
    Thanks
    62
    Thanked 0 Times in 0 Posts
    Hi CephasOz ,


    Great exactly it is according to task ,


    while coping the data to Master sheet do not change the format of master sheet (template) how it would be?




    Thanks
    Farrukh
    Last edited by farrukh; 2012-01-19 at 08:44.

  5. #4
    New Lounger
    Join Date
    Dec 2009
    Location
    Goulburn. NSW, Australia
    Posts
    20
    Thanks
    3
    Thanked 4 Times in 4 Posts

    Copying without changing the format of the destination range

    I think this should do the job.

    Code:
    Sub CopyToMaster()
        Dim wksMaster As Worksheet
        Dim wksSlave As Worksheet
        Dim rngCopy As Range
        Dim lngCol As Long
        Set wksMaster = ThisWorkbook.Worksheets("Master")
        Set wksSlave = ThisWorkbook.Worksheets("Slave")
        lngCol = 4  ' Column D
        Set rngCopy = Application.Intersect(wksSlave.UsedRange, wksSlave.Columns(lngCol))
        If Not rngCopy Is Nothing Then
             rngCopy.Copy
             wksMaster.Range("A2").PasteSpecial Paste:=xlPasteValues
            Application.CutCopyMode = False
        End If
        Set rngCopy = Nothing
        Set wksMaster = Nothing
        Set wksSlave = Nothing
    End Sub

  6. The Following User Says Thank You to CephasOz For This Useful Post:

    farrukh (2012-01-19)

  7. #5
    2 Star Lounger
    Join Date
    Jun 2011
    Posts
    188
    Thanks
    62
    Thanked 0 Times in 0 Posts

    Cool

    Hi CephasOz,

    Stunning Thank you so much this is exactly i need too...


    Cheers

Posting Permissions

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