Excel Macro Copy&Past data between 2 sheets

**Nett** · 07-12-2009, 09:00 AM

Hi All,

I am searching for a macro such that whenever i press this button (in excel) all the data in the sheet is moved to another sheet.

The macro would need to
1) Find which rows in the first sheet contains data
2) Move all data from the first sheet to the second

Ive attached a TEST sheet as an example.

Thanks.

**share knowledge** · 07-12-2009, 10:50 AM

I am actually searching for similar kind of macro

See if this helps you!

http://www.excelforum.com/excel-prog...ting-data.html

**Nett** · 07-12-2009, 12:40 PM

Hi there,
Thanks a lot for taking your time to reply.=)

While inserting the code i encountered some problems. I've created a new module in the VBA Project and changed the worksheet name to S1 and S2 respectively. But when i run the sub, it prompts the Macro Name which is Copy_Ma by default. When i click on run, a second prompt appears that 'The macro in this project are disabled' . Ive set the macro settings to disabled all macros with notification. There's no security alert for me to enable macro. I've done every ways even in the excel options to enable the macro but i don't know what is it that i've done wrong here. Why can't i run the sub? Please please help.

Public Sub Copy_Ma()
Dim shSo As Worksheet, shDe As Worksheet
Dim rSo As Range, rDe As Range
Application.ScreenUpdating = False

Set shSo = Sheets("Sheet1")
Set shDe = Sheets("Sheet2")

Set rSo = shSo.[a2].Resize(shSo.[a2].CurrentRegion.Rows.Count - 1, shSo.[a2].CurrentRegion.Columns.Count - 1)
Set rDe = shDe.[a65536].End(xlUp).Offset(1)

rSo.Copy
rDe.PasteSpecial
End Sub

**share knowledge** · 07-12-2009, 12:48 PM

Not sure why i am not able to download the spreadsheet. Please upload it in xls format

**Nett** · 07-12-2009, 12:59 PM

I'm able to finally run the macro but there's error though. Sorry for any inconvenience caused. I'm really new to this.

**Nett** · 07-12-2009, 01:54 PM

Here's the attached xls format.
I managed to copy and paste the values by using this code:

Sub NoCopyAndPaste()
Sheet2.Range("D11:R11").Copy
Sheet1.Range("D9:K9".PasteSpecial
Application.CutCopyMode=False'
EndSub

But now the merged cells in Sheet 2 are making the value inserted in Sheet 1 incorrect. Anyone have any idea how i can correct this?

**JBeaucaire** · 07-12-2009, 04:55 PM

Originally Posted by Nett

But now the merged cells in Sheet 2 are making the value inserted in Sheet 1 incorrect. Anyone have any idea how i can correct this?

Yes, fix the S2 by removing the merged cells. Merged cells truly shouldn't be used at all, or at least never in cells where actual work is going on. If you need a wider cell, widen the column.

If you MUST merge a cell, text cells only, never data cells. And by never, I simply mean "not ever".

**Nett** · 07-13-2009, 02:48 AM

Thanks JBeaucaire!=)
I found this website with a macro on how to Copy range of cells from one sheet to another sheet matching on date values in Excel. Im trying to apply this on my spreadsheet now instead of using the Copying and Pasting of cell range macro. Hope this will help anyone who might encounter the same problem.

Here's the link:http://www.techonthenet.com/excel/macros/copy_range.php

**JBeaucaire** · 07-13-2009, 09:15 AM

1) You should still fix your sheet so data cells are not part of merged cells.

2) If that takes care of your need, be sure to EDIT your original post, click Go Advanced and mark the PREFIX box [SOLVED].

(Also, use the blue "scales" icon in our posts to leave Reputation Feedback, it is appreciated)