Consulting

Results 1 to 6 of 6

Thread: Copy and paste specific ranges

  1. #1
    VBAX Regular
    Joined
    Feb 2020
    Posts
    16
    Location

    Copy and paste specific ranges

    Hello,
    I am trying to build a code in Excel VBA to copy specific ranges from one sheet and paste them to specific area in another sheet. Every time I copy the specific ranges from the first sheet they have to be pasted in the next empty column of the specific area. The code is as folows:

    Sub copyandpaste()
    Dim sws As Worksheet
    Dim dws As Worksheet
    
    Set sws = Sheets("Sheet1")
    Set dws = Sheets("Sheet2")
    
    
    Sheets("Sheet1").Select
    Range("C10:C11").Select
    Selection.Copy
    dws.Select
    Range("B6:B7").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
            
    Sheets("Sheet1").Select
    Range("H20:H40").Select
    Selection.Copy
    dws.Select
    Range("B9").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
            
    dws.Activate
     End Sub
    Every time I try to execute the code the data are pasted in the same B column. Is there a way to make it find the next empty column (C, D, E and so on) and paste the data there?
    Any help would be really appreciated.
    Thanks

  2. #2
    Moderator VBAX Guru Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    4,243
    Location
    Maybe try
    Sub copyandpaste()Dim sws As Worksheet
    Dim dws As Worksheet
    Set sws = Sheets("Sheet1")
    Set dws = Sheets("Sheet2")
    Sheets("Sheet1").Range("C10:C11").Copy
    Sheets("Sheet2").Cells(10, .ColumnsCount).Left(xlToLeft).Offset(0, 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Sheets("Sheet1").Range("H20:H40").Copy
    Sheets("Sheet2").Cells(20, .ColumnsCount).Left(xlToLeft).Offset(0, 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    dws.Activate
    End Sub
    Remember To Do the Following....
    Use tags when posting code to the thread,
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  3. #3
    VBAX Regular
    Joined
    Feb 2020
    Posts
    16
    Location
    Thank you Aussiebear.
    I will definitely try your suggestion!
    Hope all will go well.

  4. #4
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,619
    Strange:

    Set sws = Sheets("Sheet1")
    Sheets("Sheet1").Range("C10:C11").Copy
    sws is 100% redundant

    Sheets("Sheet2").Cells(10, .Columnscount)
    to which object is .ColumnsCount referring ?
    Besides .columnscount doesn't exist as range property.

    In
    Sheets("Sheet2").Cells(20, .ColumnsCount).Left(xlToLeft).Offset(0, 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    , Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    is 100% redundant.


    Why not ?
    Sheets("Sheet2").Cells(10,Columns.Count).Left(xlToLeft).Offset(0, 1).resize(2)=Sheets("Sheet1").Range("C10:C11").value
    Last edited by snb; 10-05-2022 at 08:50 AM.

  5. #5
    you may also try this:
    Sub copyandpaste()
        Dim sws As Worksheet
        Dim dws As Worksheet
        Dim EmptyColumn As Integer
        
        Set sws = Sheets("Sheet1")
        Set dws = Sheets("Sheet2")
        
        'find the next empty column
        EmptyColumn = LastColumn(dws) + 1
        
        sws.Range("C10:C11").Copy
        dws.Cells(6, EmptyColumn).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                :=False, Transpose:=False
        sws.Range("H20:H40").Copy
        dws.Cells(9, EmptyColumn).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                :=False, Transpose:=False
        'remove the dashed line
        Application.CutCopyMode = False
        dws.Cells(1, EmptyColumn).Select
     End Sub
     
    Public Function LastColumn(Optional ByRef sht = Nothing)
    Dim LColumn As Long
    On Error Resume Next
    If sht Is Nothing Then sht = ActiveSheet
    LColumn = sht.Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
    LastColumn = LColumn
    End Function

  6. #6
    Moderator VBAX Guru Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    4,243
    Location
    Nice effort arnelgp.
    Remember To Do the Following....
    Use tags when posting code to the thread,
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

Posting Permissions

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