Copy Used Range from Sheet1 and paste into Sheet3

Admin 1
Viewed 66

I have been trying to make a code which copies the UsedRange from Sheet1 and Paste that range into Sheet3.

Upon running the code each time UsedRange will be paste into Sheet3 from first empty Row.

For Example: There are 5 rows (1st Row will always be Header) in the Sheet1 with data i will press run the code will copy and paste the data into Sheet3 Row2 (1st Row will always be Header).

So now the Sheet3 has data till Row5 I will press the run button again then data will be pasted from Row6.

Upon pressing button each time data will be paste accordingly. I got a code online and tried to edit it but its not working as i want.

Your help will be greatly appreciated.

Code.

Sub usedrange()

    Dim ws1         As Worksheet
    Dim ws2         As Worksheet
    Dim source      As Range
    Dim target      As Range
    Dim lastColumn  As Long

    Set ws1 = Worksheets("NewSheet")
    Set ws2 = Sheet3

    With ws2
        lastrow = .Cells(Rows.Count, 1).End(xlUp).Row
        If WorksheetFunction.CountA(.Rows(1)) > 0 Then
            lastrow = lastrow + 1
        End If
    End With

    Set source = ws1.usedrange.Offset(1)
    Set target = ws2.Cells(, lastrow)

    source.Copy Destination:=target
    Application.CutCopyMode = False

End Sub
vba excel
3 Answers

Copy Used Range

Option Explicit

Sub copyUsedRange()

    Dim ws1         As Worksheet
    Dim ws2         As Worksheet
    Dim Source      As Range
    Dim Target      As Range
    Dim LastRow     As Long
    
    ' Code names
    Set ws1 = Sheet1
    Set ws2 = Sheet3
    ' Tab Names
    'Set ws1 = ThisWorkbook.Worksheets("Sheet1")
    'Set ws2 = ThisWorkbook.Worksheets("Sheet3")
    
    With ws1.UsedRange
        Set Source = .Resize(.Rows.Count - 1).Offset(1)
    End With
    
    With ws2
        Set Target = .Cells(.Rows.Count, 1).End(xlUp).Offset(1)
    End With
 
    Source.Copy Target

End Sub
user_98140690

Code is self explanatory

Public Sub CopyUsedRange()

    Dim sourceSheet As Worksheet
    Set sourceSheet = ThisWorkbook.Worksheets("sourceSheetName")
    
    Dim targetSheet As Worksheet
    Set targetSheet = ThisWorkbook.Worksheets("targetSheetName")

    Dim sourceRange As Range
    Set sourceRange = sourceSheet.UsedRange.Resize(sourceSheet.UsedRange.Rows.Count - 1, sourceSheet.UsedRange.Columns.Count).Offset(1)
    
    Dim targetLastRow As Long
    targetLastRow = targetSheet.Cells(targetSheet.Rows.Count, "A").End(xlUp).Row
    
    Dim targetRange As Range
    Set targetRange = targetSheet.Range("A" & targetLastRow + 1)
    
    sourceRange.Copy targetRange
    
End Sub
user_15215790 
Sub usedrange()

    Dim ws1         As Worksheet
    Dim ws2         As Worksheet
    Dim source      As Range
    Dim target      As Range
    Dim vDB As Variant
    Dim rngDB As Range
    Dim r As Long, c As Long

    Set ws1 = Worksheets("NewSheet")
    Set ws2 = Sheet3
    
    With ws1
        r = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        c = .Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
        Set rngDB = .Range("a1", .Cells(r, c))
    End With
    Set source = rngDB.Offset(1)
    vDB = source
    Set target = ws2.Range("a" & Rows.Count).End(xlUp)(2)

    target.Resize(UBound(vDB, 1), UBound(vDB, 2)) = vDB

End Sub
user_81070920
Related