Copy+Insert Macro only works once?

[mafallaize]

Hi all,

I usually come to this site through a google search, it surely is the best for answering excel problems. Now I thought its time to share my excel experiences, so here is one you may have come across. This macro is designed to copy and insert data from one sheet to another according to the month of the year. It tends to work once but thereafter fails to insert the copied cells and instead inserts a single cell. Does anyone have any idea why this happens or how to fix it?

Sub ()

Sheets("2011 Reviewed").Select
Dim Jan As Range
Set Jan = Range("A10")
Dim Feb As Range
Set Feb = Range("A13")
Dim Mar As Range

'Prevent pop ups

On Error Resume Next
Application.DisplayAlerts = False

'Clear 2011 Reviewed

Sheets("2011 Reviewed").Select
Cells.Select
Selection.Clear
Sheets("Template2").Select
Cells.Select
CutCopyMode = False
Selection.Copy
Sheets("2011 Reviewed").Select
Range("A1").Select
ActiveSheet.Paste

'Filter and copy Processor to 2011 Reviewed

Sheets("Processor").Select
ActiveSheet.Range("$1:$1000").AutoFilter Field:=7, Operator:=xlFilterValues _
, Criteria2:=Array(1, "1/31/2011")
Rows("$2:$1993").SpecialCells(xlCellTypeVisible).Select
CutCopyMode = False
Selection.Copy
Sheets("2011 Reviewed").Select
Jan.Select
Selection.Insert Shift:=xlDown

Sheets("Processor").Select
ActiveSheet.Range("$1:$5994").AutoFilter Field:=7, Operator:=xlFilterValues _
, Criteria2:=Array(1, "2/28/2011")
Rows("$2:$1993").SpecialCells(xlCellTypeVisible).Select
CutCopyMode = False
Selection.Copy
Sheets("2011 Reviewed").Select
Feb.Select
Selection.Insert Shift:=xlDown

End Sub

 

[Jerry Sullivan]

Welcome to the Board,

I suggest you start by removing the statement:

On Error Resume Next

...or at least temporarily comment it out.

That technique can be useful, but it also tends to mask problems in your code making it harder for you to track them down.

You should also compile your project first
(in the VBA Editor: Debug > Compile VBAProject)
which will also reveal some errors before runtime.

 

[mafallaize]

Hello Jerry,

Thanks for your reply and some interesting points, the complie vbaproject looks useful thanks.

I added the "on error resume next" code because a pop up keeps appearing complaining about lack of resources. I tried the compile function but no errors were found with or without the "on error resume next".

The macro works fine on first attempt, but thereafter it either has a problem with copying or inserting the data - it gets at least as far as selecting the filtered data to be copied, but then only inserts a single row instead of the copied data .

 

[Jerry Sullivan]

Hello again,

I think the problems you are experiencing with the macro "working once" are due to the way you are trying to Copy entire rows
and then Insert those copied rows that are on the clipboard into a single cell reference.
Is there some reason that you need to Insert the rows instead of just Copying them?

This can create problems...for example, you initially define Jan=A10 and Feb=A13.
If your first copy-insert operation inserts 10 rows, you might expect Feb to now be A23, but in the VBA code Feb still equals Cell A13.
Your second copy-insert will insert Feb data right in the middle of Jan data.

Also, is there a reason for the variety of addresses used to select ranges in Sheet Processor?

Range("$1:$1000")
Range("$1:$5994")
Rows("$2:$1993")

Is your intent just to filter all data, and then copy all but the header?

 

[mafallaize]

Thanks for your reply Jerry,

Yes the data needs to be inserted due to the way the spreadsheet is set up. There may be a way to set up the spreadsheet in a different format, but since many people use the spreadsheet it would be easier to keep the same format.

I defined the dates to avoid the second copy-insert overwriting the Jan data - this works, I can see the macro selecting the defined cells. The problem appears to be with the copy/insert.

The variety of addresses is just me experimenting to find a solution.

I found this fix on the microsoft site:
http://support.microsoft.com/kb/133412
So my new code has 'ActiveSheet.ShowAllData' which prevents the error 'Run-time error '1004': Insert method of Range class failed', but now only the top line of the filtered data is inserted...

 

[mafallaize]

Well I have given up on inserting filtered selections with VBA. Instead, I have inserted 100s of rows in the destination sheet, copied and pasted the selections and then deleted the excess rows. It's a long way round and takes extra time but at least it works!