PDA

View Full Version : Copying File issue



imso
07-25-2011, 11:44 PM
Brief description of what i want my macros to achieve: I've trying to import data from two Excel spreadsheets, and then compare the data on each list to find the missing elements in each list and then highlight the data row if it is missing.

Kinda puzzled with the error i have here for my excel-vba codes while running the code below. With error --> subscript out of range

Set ws2 = ThisWorkbook.Sheets(stringOfSheet1)
Set ws3 = ThisWorkbook.Sheets(stringOfSheet2)

Could someone enlighten me on how should i get my codes running?
Main set of code below with an attached excel file.

Option Explicit

Public CheckBoolean1 As Boolean
Public CheckBoolean2 As Boolean

Public stringOfSheet1 As String
Public stringOfSheet2 As String

Public dynamicSheet1 As Worksheet
Public dynamicSheet2 As Worksheet

Private Sub ComparisonButton_Click()

ComparisonButton.Enabled = False
Call Differences
ResetButton.Enabled = True

End Sub

Private Sub ImportButton1_Click()

Dim OpenFile As Variant
Dim FileName1 As String
CheckBoolean1 = True

If CheckBoolean2 = False Then
ComparisonButton.Enabled = False
ElseIf CheckBoolean1 = True And CheckBoolean2 = True Then
ComparisonButton.Enabled = True
End If

Dim Wkb1 As Workbook
Set Wkb1 = ActiveWorkbook

OpenFile = Application.GetOpenFilename( _
FileFilter:="Excel Files, *.xls; *.csv; *.xlsx", Title:="Importing Excel File 1")

If OpenFile = False Then
MsgBox "Please Select a Excel File"
Exit Sub
Else
Workbooks.Open OpenFile
End If

FileName1 = ActiveWorkbook.Name

Worksheets(1).Copy after:=Wkb1.Worksheets(1)

Workbooks(FileName1).Close SaveChanges:=False

stringOfSheet1 = ActiveWorkbook.Name

End Sub

Private Sub ImportButton2_Click()

Dim OpenFile As Variant
Dim FileName2 As String
CheckBoolean2 = True

If CheckBoolean1 = False Then
ComparisonButton.Enabled = False
ElseIf CheckBoolean1 = True And CheckBoolean2 = True Then
ComparisonButton.Enabled = True
End If

Dim Wkb1 As Workbook
Set Wkb1 = ActiveWorkbook

OpenFile = Application.GetOpenFilename( _
FileFilter:="Excel Files, *.xls; *.csv; *.xlsx", Title:="Importing Excel File 2")

If OpenFile = False Then
MsgBox "Please Select a Excel File"
Exit Sub
Else
Workbooks.Open OpenFile
End If

FileName2 = ActiveWorkbook.Name

Worksheets(1).Copy after:=Wkb1.Worksheets(1)

Workbooks(FileName2).Close SaveChanges:=False

stringOfSheet2 = ActiveWorkbook.Name

End Sub

Private Sub ResetButton_Click()

ComparisonButton.Enabled = False
ResetButton.Enabled = False

CheckBoolean1 = False
CheckBoolean2 = False

ImportButton1.Enabled = True
ImportButton2.Enabled = True

Application.DisplayAlerts = False
dynamicSheet1.Delete
dynamicSheet2.Delete
Application.DisplayAlerts = True

End Sub

Sub Differences()

Dim ws2 As Worksheet, ws3 As Worksheet, CompareSheet As Worksheet
Dim lastRow2 As Integer, lastRow3 As Integer
Dim rng2 As Range, rng3 As Range, temp As Range, found As Range

Application.ScreenUpdating = False

Set ws2 = ThisWorkbook.Sheets(stringOfSheet1)
Set ws3 = ThisWorkbook.Sheets(stringOfSheet2)

lastRow2 = ws2.Range("A" & Rows.Count).End(xlUp).Row
lastRow3 = ws3.Range("A" & Rows.Count).End(xlUp).Row

Set rng2 = ws2.Range("C21:C" & lastRow2)
Set rng3 = ws3.Range("C21:C" & lastRow3)

For Each temp In rng2
If temp.Value <> "" Then
Set found = rng2.Find(What:=temp.Value, LookAt:=xlWhole)
If found Is Nothing Then
Set CompareSheet = temp.Worksheet
CompareSheet.Range("A" & temp.Row, "P" & temp.Row).Interior.ColorIndex = 3
End If
End If
Next temp

For Each temp In rng3
If temp.Value <> "" Then
Set found = rng3.Find(What:=temp.Value, LookAt:=xlWhole)
If found Is Nothing Then
Set CompareSheet = temp.Worksheet
CompareSheet.Range("A" & temp.Row, "P" & temp.Row).Interior.ColorIndex = 3
End If
End If
Next temp

End Sub

Kenneth Hobs
07-26-2011, 12:40 AM
It means that your object, sheetname, does not exist. You set the sheetnames to the activeworkbook.name which would be ThisWorkbook.Name. It is odd to name sheets with the workbook name.

You first need to move your public variables to the Module. Otherwise, you variable values will be "". After playing the two import macros, in VBE's Immediate Window, you can see the value of your two variables:
?stringOfSheet1
VBA_Comparison.xlsm
?stringOfSheet2
VBA_Comparison.xlsmDebug would have shown you the same thing.

If you need a routine to see if the worksheet exists, we can add that.

Now that you know what is going on, use that knowledge to your benefit.

imso
07-26-2011, 01:33 AM
Hi Kenneth Hobs,

I've tried what you mention but still its not working? Maybe i am not able to interpret your explanation, rather quite new in excel-vba...

Below is a attachment for what i have tried out so far.. Could you tell me what I've have not done?

Regards,
imso