Solved

Compile error variable not defined

Posted on 2013-01-23
15
411 Views
Last Modified: 2013-01-23
Hi Experts

I am getting a compile error "variable not defined" with the following vba code

On line  If strFilename <> wkDst.Name Then

The workbooks ate saved in excel 2007 as . Xlsm file....here is the link to the original question :-
http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28003426.html

Sub Conso()
Dim wbDst As Workbook
Dim wbSrc As Workbook
Dim strFilename As String

    Set wbDst = ThisWorkbook  ' Workbooks.Open("C:\Documents and Settings\Test\Master Template.xls")
   
    strFilename = Dir("C:\Documents and Settings\Test\*.xls")
   
     While strFilename <> ""
   
        If strFilename <> wkDst.Name Then
       
            Set wbSrc = Workbooks.Open("C:\Documents and Settings\Test\" & strFilename)

on error resume next        
wbSrc.Worksheets("cm").UsedRange.Copy wbDst.Worksheets("cm1").Range("A" & wbDst.Rows.Count).End(xlUp).Offset(1)
                 
wbSrc.Worksheets("orange").UsedRange.Copy wbDst.Worksheets("orange1").Range("A" & wbDst.Rows.Count).End(xlUp).Offset(1)

wbSrc.Worksheets("apple").UsedRange.Copy wbDst.Worksheets("apple1").Range("A" & wbDst.Rows.Count).End(xlUp).Offset(1)
on error goto 0
           
             wbSrc.Close
        End If
       
        strFilename = Dir()
       
    Wend
                 
End Sub
0
Comment
Question by:route217
  • 11
  • 4
15 Comments
 
LVL 24

Expert Comment

by:Steve
ID: 38808988
wkDst.Name is not deifned.. you have used wbDst

Use the same spelling and all will be fine.
0
 

Author Comment

by:route217
ID: 38808989
Ok fixed the compile error -

Now the macro is not copying the data from source WBk to master workbook...taking this as an example
wbSrc.Worksheets("cm").UsedRange.Copy wbDst.Worksheets("cm1").Range("A" & wbDst.Rows.Count).End(xlUp).Offset(1)

I have changed a to m18 in the range only...
0
 

Author Comment

by:route217
ID: 38808993
Thanks Barman...
Kindly refer to second post
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 24

Expert Comment

by:Steve
ID: 38808996
How have you changed the code? You say M18? This will likely error.
Can you show the code as it is in error.
0
 

Author Comment

by:route217
ID: 38809001
Hi Barman... There is no error just the data is not being copied across...
0
 

Author Comment

by:route217
ID: 38809004
I have just changed the range part of the code nothing else...and worksheet from .xls to . Xlsm
0
 

Author Comment

by:route217
ID: 38809275
Apologies Barman...I hope my last message was not confusing ...
0
 

Author Comment

by:route217
ID: 38809354
Hi Barman

As a final point I  not just copying row A but the entire spreadsheet data starting a row a??...
The two spreadsheets in both workbooks are the same "apples for apples"
0
 
LVL 24

Accepted Solution

by:
Steve earned 500 total points
ID: 38809379
ok to explain the copy lines:

wbSrc.Worksheets("cm").UsedRange.Copy wbDst.Worksheets("cm1").Range("A" & wbDst.Rows.Count).End(xlUp).Offset(1)

Open in new window


comes in two parts

The Copy (From)
wbSrc.Worksheets("cm").UsedRange.Copy

Open in new window

This takes the UsedRange of data on sheet named cm

And the Paste (To) which is straight after the copy sepertaed by space:
wbDst.Worksheets("cm1").Range("A" & wbDst.Rows.Count).End(xlUp).Offset(1)

Open in new window

This takes the copied data and pastes it to the destination file worksheet cm1 in a range Sarting with a top left position in Column A and Row number one down from the last row of the destination sheet.

It is very important to have the two sheet names correct in destination and source workbooks... ie the current code goes from cm to cm1 or from apple to apple1
If the sheets do not exist there will be no copy.

So check the sheet names match correctly (including case sensitivity) and it should be OK.
If you need to change the top left starting column just change the A but do not add a number to it.

If you have added M18 in place of A you will start pasting data a long way furthur down your sheet as if you data starts at row 18 it will paste to row M1818!
0
 

Author Comment

by:route217
ID: 38809402
Thanks for the constructive feedback..positive as always...I have double checked the sheet names and about to change the M18 to m... To see final results..
0
 

Author Comment

by:route217
ID: 38809432
Ok tested the macro by changing a to m on the copy range and still the macro copies the data from source worksheet m18 into destination worksheet row m 32...and then closes the first work and opens the second workbook and copy the data from m18 and pastes the data into the workbook in col m row 64....
0
 

Author Comment

by:route217
ID: 38809459
Point to note the starting point of the actual data less any headers info is cell m18...on both sheets diff workbooks...
0
 

Author Comment

by:route217
ID: 38809488
Hi Barman

Thanks for assistance on thus question... Going to post a better worded question...
0
 
LVL 24

Expert Comment

by:Steve
ID: 38809490
Could you please provide a sample of source and destination workbooks.

This will allow me to test and re-code.

I think your bigest issues are the selection of the ranges in the from and to of the copy.
i.e... the use of UsedRange is not great as it can pick up rows and columns which it shouldn't.
and the Rows.Count xlUp method may be selecting the wrong row.

Sample workbooks will get us to the best solution fastest as I will not be "flying blind"

Thanks,
Steve.
0
 

Author Comment

by:route217
ID: 38809568
Barman

I'll cannot upload a test file until this evening...
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

825 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question