Move data within workbook
 

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.

Move data within workbook
 

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

--
Kevin Backmann


"gregatvrm" wrote:

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.

Move data within workbook
 

Works like a charm! You guys impress me.

"Kevin B" wrote:

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

--
Kevin Backmann


"gregatvrm" wrote:

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.