Copy paste a range of values into a different excel sheet

Hi guys,

I'm trying to write a macro that would copy values from an excel workbook and paste them into another one. Im totally new to vba so I don't really know the syntax to do that.

What is important to know is that the data to copy begins in B2 and I want to paste it starting in A1. Also, the name of the file where the copied data is changes everyday: Alpha Date of the day before. I have already stored somewhere in the beginning of my macro the said date, so I think that will help. And the data must be pasted in a worksheet named Deals Date of the day before also.

I'd really appreciate it if someone could help me out. I don't necessarily need the whole text, If you could just help me out on how to write that'd be great.

Cheers

Hugo

A good place to start with VBA coding is to record the macro and then take a look at the code excel writes for you. You will find you can replace some of the code with something more appropriate. e.g. You might replace Selection.copy with ActiveSheet.Range("A1:A100").copy.

In your case I would think you would use ActiveSheet for the source data (as it changes all the time) and specify the name of the workbook for the destination e.g. Workbooks("Insert name of workbook here"....

When recording the macro consider whether it might be better to use the Paste Special> Paste Values function instead of a straight paste. Often that is the better option if it is just the values you need.

Hey Guys,

I tried applying what I found by recording a macro to my vba programming but it didn't really work.
Here's what I've written

Please Login or Register  to view this content.

Knowing that sDateName is used preivously in my macro and takes the value of the date of the day before (or friday if today's monday)
Moreover, I added a part that formates the K column by replacing two spaces by one space.
Also, is it possible to replace the "Range("B2:BF20100")" by range ( all sheet) because the size of the data sheet will vary from day to day.

Could you please help me with that?

Thanks so much

Hugo

I've worked a bit more on the vba coding but it still doesn't work
Here's what I've came up with

Please Login or Register  to view this content.

But it doesn't work. It gives me runtime error, object not defined...
Could you guys help me with that?

Cheers

Hugo

Does anybody have an idea on how to fix this?