Copy and Paste

Roses8

Active Member
Joined
Aug 4, 2005
Messages
348
Hi, im trying to copy and paste from A1 to I and till last row and paste into sheet 1 in A1 last row, but when it copies it selects empty cells and does not paste correctly.

Thanks. :)

Sub Macro1()

lastrow = Range("A65000").End(xlUp).Row

Sheets("Qry_Hse_Business_BSM").Select
Range("A1:I1" & lastrow).Copy
Sheets("Sheet1").Select
Range("A1" & lastrow + 1).PasteSpecial (xlPasteValues)
End Sub
 

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

iggydarsa

Well-known Member
Joined
Jun 28, 2005
Messages
1,647
I dont know if it will help but try to change:
Code:
 Range("A1:I1" & lastrow).Copy
to
Code:
Range("A1:I" & lastrow).Copy
 

Roses8

Active Member
Joined
Aug 4, 2005
Messages
348
Hi thanks, but when i want it to paste, it paste on column A but row 18 and i dont know why, when it should paste in A1, otherwise look for the next empty row then paste in column A.


Sub Macro1()

lastrow = Range("I65000").End(xlUp).Row

Sheets("Qry_Hse_Business_BSM").Select
Range("A1:I" & lastrow).Copy
Sheets("Sheet1").Select
Range("A1" & lastrow).PasteSpecial (xlPasteValues)
Sheets("Qry_Hse_Business_BSM").Select
Application.CutCopyMode = False
End Sub
 

iggydarsa

Well-known Member
Joined
Jun 28, 2005
Messages
1,647
I am sorry, I couldnt understand your problem, but if you are trying to paste on A1 try:

Range("A1").PasteSpecial (xlPasteValues)

in stead of

Range("A1" & lastrow).PasteSpecial (xlPasteValues)
 

Roses8

Active Member
Joined
Aug 4, 2005
Messages
348

ADVERTISEMENT

Hi,

I have several things to paste...what i am trying to achieve with help..

is that select A1, but if something is already contained in it, then skip 1 row and paste on the next empty cell.
 

iggydarsa

Well-known Member
Joined
Jun 28, 2005
Messages
1,647
Try this


Sub Macro1()

lastrow = Range("I65000").End(xlUp).Row
OtherLastRow=Worksheets("Sheet1").Range("A65000").End(xlup).Row+1 'Added this line
Sheets("Qry_Hse_Business_BSM").Select
Range("A1:I" & lastrow).Copy
Sheets("Sheet1").Select
Range("A" & OtherLastRow).PasteSpecial (xlPasteValues) 'Changed this lineSheets("Qry_Hse_Business_BSM").Select
Application.CutCopyMode = False
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,118,112
Messages
5,570,259
Members
412,314
Latest member
yazanwael
Top