Copy Paste Values

[BENSON]

I am working on a code that will copy data from 4 work sheets and paste the values in four other work sheets ,all in the same work book.I have made the code a before close event with the choice to copy paste or not.My problem is that the code will copy and paste the data ,but if I copy paste again it will paste over the previous pasted data and not below it as I wish to happen. I wish to create in the destination sheets a history of the information which is posted from the source sheets .The source sheets are compiled over one month and than cleared and used again.

THANKS


Private Sub Workbook_BeforeClose(Cancel As Boolean)
If MsgBox("ARE YOU READY TO UPDATE THE HISTORY FILE?", vbYesNo) = vbYes Then
Cancel = True
Dim Lastrow As Long
With Worksheets("WATERFRONT")
Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Range("A2").Resize(Lastrow, 11).Copy
End With
Sheets("W").Range("A" & Rows.Count).End(xlUp).Offset(, 1).PasteSpecial xlPasteValues
Application.CutCopyMode = False
With Worksheets("GARDENS")
Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Range("A2").Resize(Lastrow, 11).Copy
End With
Sheets("G").Range("A" & Rows.Count).End(xlUp).Offset(, 1).PasteSpecial xlPasteValues
Application.CutCopyMode = False
With Worksheets("SEA POINT")
Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Range("A2").Resize(Lastrow, 11).Copy
End With
Sheets("S").Range("A" & Rows.Count).End(xlUp).Offset(, 1).PasteSpecial xlPasteValues
Application.CutCopyMode = False
With Worksheets("SOMERSET")
Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Range("A2").Resize(Lastrow, 11).Copy
End With
Sheets("D").Range("A" & Rows.Count).End(xlUp).Offset(, 1).PasteSpecial xlPasteValues
Application.CutCopyMode = False
End If
End Sub

[Aussiebear]

I'm thinking that your line


Sheets("A".Range("A" & Rows.Count).End(xlUp).Offset(, 1).PasteSpecial xlPasteValues


Isn't working properly. I'd be inclined to try the following


Sheets("A".Range("A" & Rows.Count).End(xlUp).Row.Offset(, 1).PasteSpecial xlPasteValues


Change the Sheet reference to suit your needs

[BENSON]

Thanks for the suggestion but get code error "Run time error '424' Object required" have highlighted the code line



Private Sub Workbook_BeforeClose(Cancel As Boolean)
If MsgBox("ARE YOU READY TO UPDATE THE HISTORY FILE?", vbYesNo) = vbYes Then
Cancel = True
Dim Lastrow As Long
With Worksheets("WATERFRONT")
Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Range("A2").Resize(Lastrow, 11).Copy
End With
Sheets("W").Range("A" & Rows.Count).End(xlUp).Row.Offset(, 1).PasteSpecial xlPasteValues
Application.CutCopyMode = False
With Worksheets("GARDENS")
Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Range("A2").Resize(Lastrow, 11).Copy
End With
Sheets("G").Range("A" & Rows.Count).End(xlUp).Row.Offset(, 1).PasteSpecial xlPasteValues
Application.CutCopyMode = False
With Worksheets("SEA POINT")
Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Range("A2").Resize(Lastrow, 11).Copy
End With
Sheets("S").Range("A" & Rows.Count).End(xlUp).Row.Offset(, 1).PasteSpecial xlPasteValues
Application.CutCopyMode = False
With Worksheets("SOMERSET")
Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Range("A2").Resize(Lastrow, 11).Copy
End With
Sheets("D").Range("A" & Rows.Count).End(xlUp).Row.Offset(, 1).PasteSpecial xlPasteValues
Application.CutCopyMode = False
End If
End Sub

[david000]

Are you sure that you want the .offset (0,1) column to the right or .offset(1) next row?

[Aussiebear]

Thanks David000, for pointing out my mistake. Benson as pointed out the offending line should have read


Sheets ("D").Range ("A" & Rows.Count).End(xlUp).Row.Offset(1) .PasteSpecial xlPasteValues


Change the sheet reference to suit the required sheet

[BENSON]

when I try to insert suggested line of code It turns red andI get "Compile error Expected End of Statement"

[marshybid]

Did you remove the space after (1) .?

See edited code below

[vCode]

Sheets ("D").Range("A" & Rows.Count).End(xlUp).Row.Offset(1).PasteSpecial xlPasteValues
[/Code]

Marshybid

[david000]

I think you can't use end with during the copy mode process so you have to nest the with statements carefully.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If MsgBox("ARE YOU READY TO UPDATE THE HISTORY FILE?", vbYesNo) <> vbYes Then Exit Sub
Cancel = True
Dim Lastrow As Long
With Application
.ScreenUpdating = False
With Worksheets("WATERFRONT")
Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Range("A2").Resize(Lastrow - 1, 11).Copy
With Sheets("W")
.Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
End With
End With
With Worksheets("GARDENS")
Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Range("A2").Resize(Lastrow - 1, 11).Copy
With Sheets("G")
.Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
End With
End With
With Worksheets("SEAPOINT")
Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Range("A2").Resize(Lastrow - 1, 11).Copy
With Sheets("S")
.Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
End With
End With
With Worksheets("SOMERSET")
Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Range("A2").Resize(Lastrow - 1, 11).Copy
With Sheets("D")
.Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
End With
End With
.ScreenUpdating = True
.CutCopyMode = False
End With
End Sub

or
without the withs

Sub closeMePaste()
Dim wb As Workbook
Dim WATERFRONT, GARDENS, SEAPOINT, SOMERSET, W, G, S, D As Worksheet
If MsgBox("ARE YOU READY TO UPDATE THE HISTORY FILE?", vbYesNo) <> vbYes Then Exit Sub
With Application
.ScreenUpdating = False
Set wb = ActiveWorkbook
Set WATERFRONT = wb.Sheets("WATERFRONT")
Set GARDENS = wb.Sheets("GARDENS")
Set SEAPOINT = wb.Sheets("SEAPOINT")
Set SOMERSET = wb.Sheets("SOMERSET")
Set W = wb.Sheets("W")
Set G = wb.Sheets("G")
Set S = wb.Sheets("S")
Set D = wb.Sheets("D")
WATERFRONT.Range("a2").Resize(WATERFRONT.Cells(Rows.Count, "A").End(xlUp).Row - 1, 11).Copy
W.Range("a" & W.Cells(Rows.Count, "A").End(xlUp).Row).Offset(1).PasteSpecial (xlPasteValues)
GARDENS.Range("a2").Resize(GARDENS.Cells(Rows.Count, "A").End(xlUp).Row - 1, 11).Copy
G.Range("a" & G.Cells(Rows.Count, "A").End(xlUp).Row).Offset(1).PasteSpecial (xlPasteValues)
SEAPOINT.Range("a2").Resize(SEAPOINT.Cells(Rows.Count, "A").End(xlUp).Row - 1, 11).Copy
S.Range("a" & S.Cells(Rows.Count, "A").End(xlUp).Row).Offset(1).PasteSpecial (xlPasteValues)
SOMERSET.Range("a2").Resize(SOMERSET.Cells(Rows.Count, "A").End(xlUp).Row - 1, 11).Copy
D.Range("a" & D.Cells(Rows.Count, "A").End(xlUp).Row).Offset(1).PasteSpecial (xlPasteValues)
.ScreenUpdating = True
.CutCopyMode = False
End With
End Sub

or
Without paste just using the default .Value mode for Excel :hi:

Sub closeMeValues()
Dim wb As Workbook
Dim WATERFRONT, GARDENS, SEAPOINT, SOMERSET, W, G, S, D As Worksheet
Dim rngW, rngG, rngS, rngD As Range
If MsgBox("ARE YOU READY TO UPDATE THE HISTORY FILE?", vbYesNo) <> vbYes Then Exit Sub
Set wb = ActiveWorkbook
Set WATERFRONT = wb.Sheets("WATERFRONT")
Set GARDENS = wb.Sheets("GARDENS")
Set SEAPOINT = wb.Sheets("SEAPOINT")
Set SOMERSET = wb.Sheets("SOMERSET")
Set W = wb.Sheets("W")
Set G = wb.Sheets("G")
Set S = wb.Sheets("S")
Set D = wb.Sheets("D")
Set rngW = WATERFRONT.Range("a2").Resize(WATERFRONT.Cells(Rows.Count, "A").End(xlUp).Row - 1, 11)
Set rngG = GARDENS.Range("a2").Resize(GARDENS.Cells(Rows.Count, "A").End(xlUp).Row - 1, 11)
Set rngS = SEAPOINT.Range("a2").Resize(SEAPOINT.Cells(Rows.Count, "A").End(xlUp).Row - 1, 11)
Set rngD = SOMERSET.Range("a2").Resize(SOMERSET.Cells(Rows.Count, "A").End(xlUp).Row - 1, 11)
W.Range("a" & W.Cells(Rows.Count, "A").End(xlUp).Row).Offset(1).Resize(rngW.Rows.Count, 11).Value = rngW.Value
G.Range("a" & G.Cells(Rows.Count, "A").End(xlUp).Row).Offset(1).Resize(rngG.Rows.Count, 11).Value = rngG.Value
S.Range("a" & S.Cells(Rows.Count, "A").End(xlUp).Row).Offset(1).Resize(rngS.Rows.Count, 11).Value = rngS.Value
D.Range("a" & D.Cells(Rows.Count, "A").End(xlUp).Row).Offset(1).Resize(rngD.Rows.Count, 11).Value = rngD.Value
End Sub