MrExcel.com - Your One Stop for Excel Tips & Solutions

This sould be a simple one...(i hope)

Posted by RANDY on September 25, 2001 11:26 AM

I have thirteen cellS (not always) that need to be copied and pasted to another sheet. I then clear those original cells(using a macro) and then fill them with new values (not always all thirteen). What I need to do is copy that second set of cells and paste them to the other sheet starting with the last entry from the last paste...any ideas?

Posted by Rodney on September 25, 2001 6:45 PM


to get the cell below the last non-blank cell in column A :-

Dim firstBlank As Range
Set firstBlank = Range("A65536").End(xlUp).Offset(1, 0)


Posted by Travis Harr on September 25, 2001 6:48 PM

I think I understand. How about this:

Sub Macro1()
Sheets("Sheet1").Select
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("Sheet2").Select
Range("A14").Select
ActiveSheet.Paste
End Sub

Where Sheet1 and 2 are the tab names of your worksheets A1 is the first cell in the range of your second set of data and A14 is the first cell below your data on the second sheet...make sense?
Good Luck

Posted by RANDY on September 26, 2001 7:02 AM

STILL NEEDS SOME REFINEING

WITH BOTH OF YOUR HELP I CAME UP WITH THIS

Sub COVERSHEET_LIST()
Application.ScreenUpdating = False
Sheets("INFO").Select
Range("B8").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("COVER SHEET").Select
Range("J100").End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste
Sheets("INFO").Select
Application.ScreenUpdating = True
End Sub


NOW THE ONLY PROBLEM IS WHEN I RUN IT THE RANGE STAYS SELECTED AND I HAVE TO HIT ESCAPE TO CLEAR THE RANGE...HOW WOULD I ADD THAT TO THE CODE?

Posted by Rodney on September 26, 2001 3:19 PM

Re: STILL NEEDS SOME REFINEING


Add the line :-
Application.CutCopyMode = False
(which you could have obtained by using the macro recorder).

However, you can avoid the need to use this line of code by avoiding all selecting in your macro. Here is a fully "tweaked" macro :-

Sub COVERSHEET_LIST()
Dim Ws1 As Worksheet, Ws2 As Worksheet
Dim source As Range, dest As Range
Set Ws1 = Worksheets("INFO")
Set Ws2 = Worksheets("COVER SHEET")
Set source = Ws1.Range(Ws1.Range("B8"), Ws1.Range("B8").End(xlDown))
Set dest = Ws2.Range("J65536").End(xlUp).Offset(1, 0)
source.Copy dest
End Sub