route217
asked on
Compile error variable not defined
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 :-
https://www.experts-exchange.com/questions/28003426/Edit-macro-to-include-two-extra-worksheets-data-to-be-copied.html
Sub Conso()
Dim wbDst As Workbook
Dim wbSrc As Workbook
Dim strFilename As String
Set wbDst = ThisWorkbook ' Workbooks.Open("C:\Documen ts 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:\Documen ts and Settings\Test\" & strFilename)
on error resume next
wbSrc.Worksheets("cm").Use dRange.Cop y wbDst.Worksheets("cm1").Ra nge("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
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 :-
https://www.experts-exchange.com/questions/28003426/Edit-macro-to-include-two-extra-worksheets-data-to-be-copied.html
Sub Conso()
Dim wbDst As Workbook
Dim wbSrc As Workbook
Dim strFilename As String
Set wbDst = ThisWorkbook ' Workbooks.Open("C:\Documen
strFilename = Dir("C:\Documents and Settings\Test\*.xls")
While strFilename <> ""
If strFilename <> wkDst.Name Then
Set wbSrc = Workbooks.Open("C:\Documen
on error resume next
wbSrc.Worksheets("cm").Use
wbSrc.Worksheets("orange")
wbSrc.Worksheets("apple").
on error goto 0
wbSrc.Close
End If
strFilename = Dir()
Wend
End Sub
ASKER
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").Use dRange.Cop y wbDst.Worksheets("cm1").Ra nge("A" & wbDst.Rows.Count).End(xlUp ).Offset(1 )
I have changed a to m18 in the range only...
Now the macro is not copying the data from source WBk to master workbook...taking this as an example
wbSrc.Worksheets("cm").Use
I have changed a to m18 in the range only...
ASKER
Thanks Barman...
Kindly refer to second post
Kindly refer to second post
How have you changed the code? You say M18? This will likely error.
Can you show the code as it is in error.
Can you show the code as it is in error.
ASKER
Hi Barman... There is no error just the data is not being copied across...
ASKER
I have just changed the range part of the code nothing else...and worksheet from .xls to . Xlsm
ASKER
Apologies Barman...I hope my last message was not confusing ...
ASKER
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"
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"
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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..
ASKER
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....
ASKER
Point to note the starting point of the actual data less any headers info is cell m18...on both sheets diff workbooks...
ASKER
Hi Barman
Thanks for assistance on thus question... Going to post a better worded question...
Thanks for assistance on thus question... Going to post a better worded question...
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.
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.
ASKER
Barman
I'll cannot upload a test file until this evening...
I'll cannot upload a test file until this evening...
Use the same spelling and all will be fine.