Move data within workbook

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need to know how to move data from worksheet 1, A2 to D13 to next available
row in worksheet 2 of the same workbook using a command button. Cells
contain text, currency, and dates.
 
Try this in a copy of your actual workbook, not the original...

Sub PasteRange()

Dim wb As Workbook
Dim wsSource As Worksheet
Dim wsTarget As Worksheet
Dim r As Range
Dim varVal As Variant
Dim lngRow As Long

Set wb = ActiveWorkbook
Set wsSource = wb.Worksheets("Sheet1")
Set wsTarget = wb.Worksheets("Sheet2")
Set r = wsSource.Range("A1:D13")

wsTarget.Activate
Range("A1").Select

varVal = ActiveCell.Value

Do Until varVal = ""
lngRow = lngRow + 1
varVal = ActiveCell.Offset(lngRow).Value
Loop

r.Copy
wsTarget.Paste Destination:=wsTarget.Range("A" & lngRow + 1)
Application.CutCopyMode = False

Set wb = Nothing
Set wsSource = Nothing
Set wsTarget = Nothing
Set r = Nothing

End Sub
 
Works like a charm! You guys impress me.

Kevin B said:
Try this in a copy of your actual workbook, not the original...

Sub PasteRange()

Dim wb As Workbook
Dim wsSource As Worksheet
Dim wsTarget As Worksheet
Dim r As Range
Dim varVal As Variant
Dim lngRow As Long

Set wb = ActiveWorkbook
Set wsSource = wb.Worksheets("Sheet1")
Set wsTarget = wb.Worksheets("Sheet2")
Set r = wsSource.Range("A1:D13")

wsTarget.Activate
Range("A1").Select

varVal = ActiveCell.Value

Do Until varVal = ""
lngRow = lngRow + 1
varVal = ActiveCell.Offset(lngRow).Value
Loop

r.Copy
wsTarget.Paste Destination:=wsTarget.Range("A" & lngRow + 1)
Application.CutCopyMode = False

Set wb = Nothing
Set wsSource = Nothing
Set wsTarget = Nothing
Set r = Nothing

End Sub
Click to expand...
 
Back
Top