Pasting in Excel 1

[phil009]

Hi, all I am trying to do is copy data from one file ("allcompanies.xls") and paste it to another file ("test.xls"). Allcompanies.xls are results from a query in Access and so the size of it can vary every time it is opened. The problem I am having is that I can't run this macro if they are two different sizes. Excel comes back with a message that says data is different sizes. My code looks like this:

Windows("all companies.xls").Activate
    Cells.Select
    Selection.Copy
    Windows("test.xls").Activate
    Sheets("all companies").Select
    ActiveSheet.Paste

Any help would be greatly appreciated,
Phil

 

[SkipVought]

Hi,

Windows("all companies.xls").[A1].CurrentRegion.Copy _
  Destination:=Windows("test.xls").Sheets("all companies").[A1]

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[phil009]

Skip-Thanks for the quick reply but now when I run the code you gave me I get a message that says: Run-time Error '438': Object doesn't support this property or method.

Sorry I am new to VB but anything that you can think that I might be doing wrong would be helpful,

Phil

 

[SkipVought]

I didn't even notice that you were using WINDOWS...

Workbooks("all companies.xls").Worksheets("[b]WHATEVER THIS SHEET NAME IS[/b]").[A1].CurrentRegion.Copy _
  Destination:=Workbooks("test.xls").Worksheets("all companies").[A1]

Both workbooks must be open!

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[PHV]

Try this:
Windows("all companies.xls")[highlight].ActiveSheet[/highlight].[A1].CurrentRegion.Copy _
Destination:=Windows("test.xls").Sheets("all companies").[A1]


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[phil009]

Works like a charm Skip.

Thanks a bunch,
Phil

 

[SkipVought]

PHV's suggestion will also work.

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[phil009]

Skip\PHV I do have one more question. Now that my macro works is there a way to create a button on my excel workbook ("test.xls") that will automatically run the macro "aall". I can give you any info you need.

Phil

 

[SkipVought]

Phil,

There are 2 types of controls: Forms and COntrol Toolbox. Forms are very simple controls. With Control toolbox controls you can customize the control. They are handled in different ways.

If all you want is a simple button, then try Forms. Right click in the Excel toolbar and select Forms. Select the Button and "place" on your sheet. Select New Macto and type your macro name in the Button Click event.

Right click the Button to select and change the Caption.
Right clikc the Button and select Format COntrol to change the font.



Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[phil009]

Thanks once again Skip, it works just like I had hoped.

The help was much appreciated,
Phil

 

[phil009]

Skip-I guess I am running into one problem now. If I run a query with a lot of results and then run the next query with only a couple the macro is only pasting over a couple of the results, it isn't deleting the rest of the old results. So I am left with my new results and some other old results underneath. Is there anyway to, maybe, have the macro delete what is in "test.xls" workbook "all companies" before I paste the new data so I don't get these old results?

Phil

 

[SkipVought]

Workbooks("test.xls").Worksheets("all companies").Cells.ClearContents
Workbooks("all companies.xls").Worksheets("WHATEVER THIS SHEET NAME IS").[A1].CurrentRegion.Copy _
  Destination:=Workbooks("test.xls").Worksheets("all companies").[A1]

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[phil009]

Thanks again Skip for all of your help. The button works perfectly now.

Thanks again,
Phil