Excel VBA error- Copying data between workbooks 1

[darvistor]

Hello all,

I have data in an existing excel workbook that I am trying to copy into a new workbook and then rename the new file based on given variable names.

I had previously recorded macros to autofilter the data, select the range of cells I wanted to copy, create a new workbook and past the data into sheet 1 of the new workbook. I linked these different macros to buttons in the original workbook. The macros/buttons all work fine.

However, I was asked to combine all of the macros into one step and place a single button on the main sheet. Here is where things go wrong. If I try to copy the code from each macro into one subroutine, the code crashes when the following piece of code executes: Range("XX:YY").Select, which is to select the data I want to copy. I get this error, Run-time error '1004': Application-defined or object-defined error.

If I simply tell the new subroutine to just run each macro in order like this(simplified for example):
Application.Run Macro1
Application.Run Macro2
Application.Run Macro3

Each macro executes fine with no errors. I guess I don't want all these extra macros in the file and want to simplify things. Am I missing something with the range selection? Should I be using a different selection function like Cells?

Any help would be greatly appreciated. I didn't post all of the code because it seems like the issue is with the cell/range selection. And before it is asked, yes I declared all the necessary variables.

 

[bubba100]

Instead of selecting try using Sheets("YourSheetName").Range("CA2:CD2").Copy

 

[SkipVought]

"Should I be using a different selection..."

As a rule you should avoid using Select and Activate methods.

Rather as bubba has suggested, reference objects explicitly.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[darvistor]

I went back and looked at the code and found that adding Activesheet in front of the range resolved the issue. I thought selecting the sheet in the previous line was sufficient but apparently not.

Skip, please excuse my VBA ignorance, why should I avoid using Select/Activate? I can see cutting down some lines of code, but does it behave any better/faster not using the select?

Thanks for the replies.
Darvistor

 

[SkipVought]

Regarding the ActiveSheet object, will you ALWAYS know what the ActiveSheet is? What if someone saves the workbook with an unintended sheet selected. Your macro will either burp OR it will give you unintended results.

Using the Select and Activate methods does slow down code processing. It is an unnecessary statement in most cases. I use Select or Activate only when I want the user to see a different sheet or have a different selection when the procedure finishes.

In certain few instances, the Select method is a must; like coding the Conditional Formatting feature in Excel. I have not fould a way around using the Select method.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[darvistor]

Thanks Skip. You've given me help in the past and I appreciate advice from anyone who knows what they are talking about.

In this particular case, the file opens to the same sheet every time and there is a procedure the users follow. In the code, I actually switch to the sheet I want the data copied from and then use the activesheet.range command. I will try it all in one command again, but I couldnt get the cells selected or copy command to work when I tried it before.

Thanks again.

 

[SkipVought]

"but I couldnt get the cells selected or copy command to work when I tried it before."

What was the code that you, "tried it before?"

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[darvistor]

I believe it was something like this(its at work so I am going by memory):

Sheets("Mysheet").Select
Range("A1:G404").Select
Selection.Copy
.....more code...

I then tried this which didnt work:
Sheets("Mysheet").Select
Range("A1:G404").Copy
.....more code...


What I have now that works is:

Sheets("Mysheet").Select
Activesheet.Range("A1:G404").Select
Selection.Copy
.....more code...

What I am going to try tomorrow is:
Sheets("Mysheet").Range("A1:G404").Copy
.....more code....

 

[SkipVought]

Here's all you need to replace..,.

Sheets("Mysheet").Select
Activesheet.Range("A1:G404").Select
Selection.Copy

Instead use...

Sheets("Mysheet").Range("A1:G404").Copy

The sheet with the range that you are copying, does not need to be active, if your quallify your object references.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[xlbo]

Skip - maybe for a different thread but I have not seen any issues with needting to select to apply conditional formatting - I created a workbook a couple of weeks ago that uses the following:

With Range("A1:A" & lRow)
    .FormatConditions.Delete
    .FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _
        Formula1:="10"
    With .FormatConditions(1).Font
        .Bold = True
        .ColorIndex = 10
    End With
    .FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
        Formula1:="11", Formula2:="20"
    With .FormatConditions(2).Font
        .Bold = True
        .ColorIndex = 45
    End With
    .FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
        Formula1:="20"
    With Selection.FormatConditions(3).Font
        .Bold = True
        .ColorIndex = 3
    End With
End With
End Sub

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question