VBA Code help..copy & paste at end of range

[ExcelKid_10]

Hello-

As a follow up to an earlier post I have the following code which I from this board that I need a little help modifying:

Sub CopyData()
'
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("MASTER").Select
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub

What I would like to do is add a line after the code takes you to the last row in column and go one more down (which will be a blank cell/row) and paste my data there. Can anyone help with this?

Thanks!!

 

[fridgenep]

server problems?

 

[fridgenep]

Hi,

I don´t know the author of your posted code, but....

Select The Selection!!! :wink:

Do you want to copy the Range in the same sheet?

Try this:

Option Explicit

Sub Copy()
   With ActiveSheet
    .Range("A1").CurrentRegion.Copy
    .Paste Destination:=.Range("A" & Range("A65536").End(xlUp).Row + 2)
   End With
Application.CutCopyMode = False
End Sub

Is that what you want?

 

[ExcelKid_10]

very close...instead of pasting it on the current worksheet i would like it to paste on another worksheet called "Master". Can you help me do that? Your help is much appreciated!

 

[fridgenep]

Hi,

try now:

Option Explicit

Sub Copy()
Dim lngI As Long
 ActiveSheet.Range("A1").CurrentRegion.Copy
 lngI = Sheets("MASTER").Range("A65536").End(xlUp).Row + 2
  If lngI = 3 Then
   lngI = 2
  End If

        With Sheets("MASTER")
            .Activate
            .Paste Destination:=.Cells(lngI, 1)
        End With
    
    Application.CutCopyMode = False
End Sub

 

[ExcelKid_10]

Thank you so much! Works perfect. One more ?....can you help me paste-special values instead of just pasting?

Thanks!!! your help is much appreciated.

 

[fridgenep]

Hi,

one more? No prob.

Option Explicit
Sub Copy()
Dim lngI As Long
 ActiveSheet.Range("A1").CurrentRegion.Copy
 lngI = Sheets("MASTER").Range("A65536").End(xlUp).Row + 2
  If lngI = 3 Then
   lngI = 2
  End If
Application.ScreenUpdating = False
        With Sheets("MASTER")
            .Activate
            .Cells(lngI, 1).PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
            .Cells(lngI, 1).Select
        End With
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
  MsgBox ("Finished")
End Sub