Trouble with VBA macro compability from excel 2007 to 2003

[maciej.wludyka]

Hi,
I a newbie in VBA coding, but for the purposes of company I work in, I wanted to learn it, to make my life easier. Recently I needed to code a macro gathering certain ranges of data and pasting it to a specific place. My macro looks like:





Sub NAVrecap2011_Maciej_Wludyka()
'
' Makro zwracajace pliki z zewnetrznych plikow
'
Dim a As Double
Dim b As Double
a = 1
Dim zrodlo As String
Dim zrodelko As String

b = Application.InputBox(Prompt:= _
"Wpisz od ktorego wiersza makro ma zaczac dzialac!", Type:=1)
c = Application.InputBox(Prompt:= _
"Wpisz do ktorego wiersza makro ma zaczac dzialac!", Type:=1)
zrodlo = Worksheets("2011 NAV Recap").Cells(b, 93)
zrodelko = Worksheets("2011 NAV Recap").Cells(b, 102)


Do While b < c






If Worksheets("2011 NAV Recap").Cells(3, 102) < Worksheets("2011 NAV Recap").Cells(b, 91) Then
ActiveWindow.Close
Else
End If

Beginning:
On Error GoTo Errortrap



zrodlo = Worksheets("2011 NAV Recap").Cells(b, 93)
zrodelko = Worksheets("2011 NAV Recap").Cells(b, 102)

If zrodlo = Worksheets("2011 NAV Recap").Cells(17, 93) Then
b = b + a
Else






Workbooks.Open Filename:=zrodlo


Sheets("Template 2").Select
Range("F13:H13").Select
Selection.Copy
Windows("Diapason NAV recap 2011.xls").Activate
Worksheets("2011 NAV Recap").Cells(b, 50).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Windows(zrodelko).Activate
Range("F16:H16").Select
Application.CutCopyMode = False
Selection.Copy

Windows("Diapason NAV recap 2011.xls").Activate
Worksheets("2011 NAV Recap").Cells(b, 44).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Windows(zrodelko).Activate
Range("F19:H19").Select
Application.CutCopyMode = False
Selection.Copy

Windows("Diapason NAV recap 2011.xls").Activate
Worksheets("2011 NAV Recap").Cells(b, 77).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Windows(zrodelko).Activate
Range("F22:H22").Select
Application.CutCopyMode = False
Selection.Copy

Windows("Diapason NAV recap 2011.xls").Activate
Worksheets("2011 NAV Recap").Cells(b, 71).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Windows(zrodelko).Activate
Range("F25:H25").Select
Application.CutCopyMode = False
Selection.Copy

Windows("Diapason NAV recap 2011.xls").Activate
Worksheets("2011 NAV Recap").Cells(b, 2).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Windows(zrodelko).Activate
Range("F28:H28").Select
Application.CutCopyMode = False
Selection.Copy

Windows("Diapason NAV recap 2011.xls").Activate
Worksheets("2011 NAV Recap").Cells(b, 5).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Windows(zrodelko).Activate
Range("F31:H31").Select
Application.CutCopyMode = False
Selection.Copy

Windows("Diapason NAV recap 2011.xls").Activate
Worksheets("2011 NAV Recap").Cells(b, 8).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Windows(zrodelko).Activate
Range("F34:H34").Select
Application.CutCopyMode = False
Selection.Copy

Windows("Diapason NAV recap 2011.xls").Activate
Worksheets("2011 NAV Recap").Cells(b, 11).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Windows(zrodelko).Activate
Range("F37:H37").Select
Application.CutCopyMode = False
Selection.Copy

Windows("Diapason NAV recap 2011.xls").Activate
Worksheets("2011 NAV Recap").Cells(b, 14).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Windows(zrodelko).Activate
Range("F40:H40").Select
Application.CutCopyMode = False
Selection.Copy

Windows("Diapason NAV recap 2011.xls").Activate
Worksheets("2011 NAV Recap").Cells(b, 17).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Windows(zrodelko).Activate
Sheets("Template 1").Select
Range("F13:H13").Select
Application.CutCopyMode = False
Selection.Copy

Windows("Diapason NAV recap 2011.xls").Activate
Worksheets("2011 NAV Recap").Cells(b, 56).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Windows(zrodelko).Activate
Range("F16:H16").Select
Application.CutCopyMode = False
Selection.Copy

Windows("Diapason NAV recap 2011.xls").Activate
Worksheets("2011 NAV Recap").Cells(b, 59).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Windows(zrodelko).Activate
Range("F19:H19").Select
Application.CutCopyMode = False
Selection.Copy

Windows("Diapason NAV recap 2011.xls").Activate
Worksheets("2011 NAV Recap").Cells(b, 62).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Windows(zrodelko).Activate
Range("F22:H22").Select
Application.CutCopyMode = False
Selection.Copy

Windows("Diapason NAV recap 2011.xls").Activate
Worksheets("2011 NAV Recap").Cells(b, 65).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Windows(zrodelko).Activate
Range("F25:H25").Select
Application.CutCopyMode = False
Selection.Copy

Windows("Diapason NAV recap 2011.xls").Activate
Worksheets("2011 NAV Recap").Cells(b, 74).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Windows(zrodelko).Activate
Range("F28:H28").Select
Application.CutCopyMode = False
Selection.Copy

Windows("Diapason NAV recap 2011.xls").Activate
Worksheets("2011 NAV Recap").Cells(b, 38).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Windows(zrodelko).Activate
Range("F31:H31").Select
Application.CutCopyMode = False
Selection.Copy

Windows("Diapason NAV recap 2011.xls").Activate
Worksheets("2011 NAV Recap").Cells(b, 41).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Windows(zrodelko).Activate
Range("F34:H34").Select
Application.CutCopyMode = False
Selection.Copy

Windows("Diapason NAV recap 2011.xls").Activate
Worksheets("2011 NAV Recap").Cells(b, 53).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Windows(zrodelko).Activate
Range("F37:H37").Select
Application.CutCopyMode = False
Selection.Copy

Windows("Diapason NAV recap 2011.xls").Activate
Worksheets("2011 NAV Recap").Cells(b, 80).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Windows(zrodelko).Activate
Range("F40:H40").Select
Application.CutCopyMode = False
Selection.Copy

Windows("Diapason NAV recap 2011.xls").Activate
Worksheets("2011 NAV Recap").Cells(b, 47).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Windows(zrodelko).Activate
Sheets("Template 3").Select
Range("F13:H13").Select
Application.CutCopyMode = False
Selection.Copy

Windows("Diapason NAV recap 2011.xls").Activate
Worksheets("2011 NAV Recap").Cells(b, 29).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Windows(zrodelko).Activate
Range("F16:H16").Select
Application.CutCopyMode = False
Selection.Copy

Windows("Diapason NAV recap 2011.xls").Activate
Worksheets("2011 NAV Recap").Cells(b, 32).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Windows(zrodelko).Activate
Windows(zrodelko).Close (0)
Worksheets("2011 NAV Recap").Cells(b, 1).Select

b = b + a

End If

Loop

Exit Sub
Errortrap:
b = b + a

GoTo Beginning

End Sub



As to simplify, It just takes the data, from the certain xsl's and if there is none, go futher on. I need to convert it, as it would work on 2003 excel, which my boss has.
Thanks for all Your help in advance.
With regards,
Maciej Wludyka

 

[Norie]

Maciej

Have you had problems running the code in the other version?

There doesn't appear to be anything that needs to be changed because of the different versions.

There are some things that should probably be changed just generally.

For example this:

Windows(zrodelko).Activate
Range("F16:H16").Select
Application.CutCopyMode = False
Selection.Copy

Windows("Diapason NAV recap 2011.xls").Activate
Worksheets("2011 NAV Recap").Cells(b, 44).Select
Selection.PasteSpecial Paste:=xlPasteValues, ...

can replaced with this:

Workbooks(zrodelko).Range("F16:H16").Copy

Workbooks("Diapason NAV recaps 2011.xls").Worksheets("2011 NAV Recap").Cells(b, 44).PasteSpecial Paste:=xlPasteValues

 

[maciej.wludyka]

Hello Norie,
Thanks for such a fast reply!
I have changed and simplified the code as You recommended, hope it will go faster, cause it's already using big data sets.
And my problem is caused, by not reading the macro file in excel 2003. I have tried copying the text into the new model and extracting the macro, both way it didn't proceed. I will try to check it with f8, as soon as possible, as to find the missing point.
Any futher suggestions?
With Regards,
Maciej Wludyka

 

[Norie]

Maciej

There's probably some other things you could change, the first one would be to remove the On Error...

That could just be hiding errors that are actually the real problem.

Another thing is all that Select/Activate/Selection isn't needed and actually makes the code pretty hard to follow.

That should be straightforward to deal with by creating references to the workbooks/worksheets you are working with.

Another thing is, basically, can you post an explanation of what the code is doing?

I think I get some of it.

- Ask the user which rows have data or the code should start, or ask them something anyway.

- Get 2 workbook names from Cells(b, 93) and (b, 102).

- Open the first of those workbooks.

- Copy data from that workbook to the other workbook.

- Repeat the above 3 steps.

There's also seems to be at least 1 other workbook involved, one of which is named in the code - 'Diapason NAV recap 2011.xls'.

Is that anywhere near?

 

[maciej.wludyka]

Norie,
I cannot erase the on error, as some of the files are wrongly named and missing, as i want the macro to run for large number of dates.
I have already deleted activate, etc.

As an explanation of the process:
The macro starts with asking the user, about the range of data imputs( they represents dates, the information should be uploaded)
Then
I used the reference to cells, as to get the desired file source: dependent by the data(the filename changes)
Then it would open the dependent data, and copy all desired information, to stiff cells in consolidated report for selected period of time.
the Diapason NAV is the main workbook, i didn;t know thisworkbook (before) so i did some work around it


As to explain it the easiest:
I have multiple excel files, with the same data structure, which i paste into the main excel file, in the right places. Some files are missing or badly named, so the error occur. It's the first day I'm using VBA so it is pretty bad
With regards,
Maciej Wludyka

 

[Ruddles]

Norie,
I cannot erase the on error, as some of the files are wrongly named and missing, as i want the macro to run for large number of dates.

You should only activate On Error to trap errors which you cannot detect in advance, and in that case you should turn it off again as soon as that command has been executed. Don't leave it enabled throughout the program because it could quite easily hide more serious errors which could corrupt your results.

If you're concerned about files not existing, test whether they exist before you try to use them.

 

[Norie]

Maciej

I understand your reluctance to remove the On Error, but it will help.

As Ruddles says you can test for bad filenames or for the existence of files.

If there is a problem you can move onto the next file.

Can you post the code as it is now?

 

[Ruddles]

I understand your reluctance to remove the On Error, but it will help.

It will hurt for a while, but then you will feel liberated, as though a huge weight had been lifted from your shoulders, and you will never use On Error again.

And after that, whenever you see a code module which doesn't have Option Explicit at the top of it, or a GoTo in the middle of a procedure, a small shiver will go down your spine.

 

[maciej.wludyka]

Hi,
Thanks for all Your replies.
i figured out the problem somehow, and I am decently happy with created solution. I am starting to learn more basics about VBA from Paul McFedries book, so I hope i will be able to do more
Thanks again for tips,
Maciek