Using a macro to paste cells one cell below the last cell.
 

I have some information in one sheet that I want to copy and paste to another
sheet at the bottom of it (END + one sell), but now it looks like it will be
pasted in the same cell, regardless how many rows i have in the source sheet
and in the sheet that I paste the information in.

Using a macro to paste cells one cell below the last cell.
 

Hi,

To find the first empty cell after the last used row in a column use

lastrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row + 1

or if you don't know which column it's in use

lastrow = ActiveSheet.UsedRange.Rows.Count + 1

Mike

"Magnus HB" wrote:

I have some information in one sheet that I want to copy and paste to another
sheet at the bottom of it (END + one sell), but now it looks like it will be
pasted in the same cell, regardless how many rows i have in the source sheet
and in the sheet that I paste the information in.

Using a macro to paste cells one cell below the last cell.
 

Post your code.

"Magnus HB" wrote:

I have some information in one sheet that I want to copy and paste to another
sheet at the bottom of it (END + one sell), but now it looks like it will be
pasted in the same cell, regardless how many rows i have in the source sheet
and in the sheet that I paste the information in.

Using a macro to paste cells one cell below the last cell.
 

Range("F2:G2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Windows("Mark to Market.xls").Activate
Sheets("ProFX").Select
Range("A1").Select
ActiveSheet.Paste
Range("A1").Select
Selection.End(xlDown).Select
Range("A165").Select I THINK IT IS HERE THE
PROBLEM IS
Windows("D090130-FX.xls").Activate
Sheets("terminer").Select
Range("D2:E2").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Windows("Mark to Market.xls").Activate
Range("A165").Select OR MAYBE HERE

"joel" wrote:

Post your code.

"Magnus HB" wrote:

I have some information in one sheet that I want to copy and paste to another
sheet at the bottom of it (END + one sell), but now it looks like it will be
pasted in the same cell, regardless how many rows i have in the source sheet
and in the sheet that I paste the information in.

Using a macro to paste cells one cell below the last cell.
 

The following code copies a range from Sheet1 and pastes it at the bottom of
the existing data in the first column of Sheet2.

The code for the Copy/Paste is actually one line of code and the space and
underscore at the end of lines are line breaks in an otherwise single line of
code.

Note that when you copy a range (that is multiple cells) you only need to
address the first cell of the destination.

Sheets("Sheet1").Range("A2:Z2").Copy _
Sheets("Sheet2").Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0)

To get the first blank cell in column 1 the code emulates the following:-
Selecting the last cell in column A (At the very bottom of the worksheet.)

Holding Ctrl key and pressing Up arrow. Moves the cursor up to the first
cell with data.

Press down arrow once to move to cell below the one with data.

Hope this helps.


--
Regards,

OssieMac


"Magnus HB" wrote:

I have some information in one sheet that I want to copy and paste to another
sheet at the bottom of it (END + one sell), but now it looks like it will be
pasted in the same cell, regardless how many rows i have in the source sheet
and in the sheet that I paste the information in.

Using a macro to paste cells one cell below the last cell.
 

Not sure if I got the source and destination locations correct. This code
should be easy to fix

with workbooks("D090130-FX.xls").Sheets("terminer")
set StartLocation = .Range("F2:G2")
.Range(StartLocation, StartLocation.End(xlDown)).Copy
end with

with workbooks("Mark to Market.xls").Sheets("ProFX")
set LastCell = .Range("A" & Rows.count).End(xlup)
set NewCell = LastCell.offset(rowoffset:=1,columnoffset:=0)
NewCell.Paste
end with



"Magnus HB" wrote:

Range("F2:G2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Windows("Mark to Market.xls").Activate
Sheets("ProFX").Select
Range("A1").Select
ActiveSheet.Paste
Range("A1").Select
Selection.End(xlDown).Select
Range("A165").Select I THINK IT IS HERE THE
PROBLEM IS
Windows("D090130-FX.xls").Activate
Sheets("terminer").Select
Range("D2:E2").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Windows("Mark to Market.xls").Activate
Range("A165").Select OR MAYBE HERE

"joel" wrote:

Post your code.

"Magnus HB" wrote:

I have some information in one sheet that I want to copy and paste to another
sheet at the bottom of it (END + one sell), but now it looks like it will be
pasted in the same cell, regardless how many rows i have in the source sheet
and in the sheet that I paste the information in.