Errors referencing workbooks/worksheets properly

[alwayslrN]

Hello -

I am using Excel 2002 SP3 and I in the process of building a macro that will eventually do the following:

(1) Use a button control to allow a user to select a file - Procedur OpenSingleFile - I got that from the FAQ's so thanks
(2) Once opened there will be a procedure that will identify the appropriate macro to run based on particular cell values from the file that was just opened.
(3) Run the appropriate procedure passing in the filename from the opened file - in this case there are several different macros because there are varying file formats. The procedure copies specified data from the opened workbook in step 1 and pastes a sheet in the speadsheet that has the button.
(4) Once all the copy/paste process is done between the opened workbook and the main workbook create a csv file. I have not gotten to this part yet.

My issue, because I am still trying to get a grasp on all of this, is knowing how to reference the correct workbooks, correct worksheets and/or range within the worksheets.

Before creating step 1 mentioned above, I used Record Macros to create all the macros used in step 3. When doing that the code ended up with something like:

   ChDir "I:\Real Estate\Single Family\Nov 2006"
    Workbooks.Open Filename:= _
        "I:\Real Estate\Single Family\Nov 2006\001 10-06 Spreadsheet emailed.xls"
    Range("A8").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Windows("remittance.xls").Activate
    
    Range("B2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows("001 10-06 Spreadsheet emailed.xls").Activate
    Application.CutCopyMode = False
    Windows("remittance.xls").Activate
    Range("E2").Select

Now that I am building onto the project I need to be able to replace the 001 10-06 Spreadsheet emailed.xls and remittance.xls so that it goes to the correct workbook and/or worksheets. Below is the code I have so far.

'Step 1
Sub OpenSingleFile()
    Dim Filter As String, Title As String
    Dim FilterIndex As Integer
    Dim Filename As Variant
    Dim RemitFileName As Workbook
    
    Set wbMain = ThisWorkbook
    
    ' File filters
    Filter = "Excel Files (*.xls),*.xls," & _
             "Text Files (*.txt),*.txt," & _
             "All Files (*.*),*.*"
    ' Default Filter to *.*
    FilterIndex = 3
    ' Set Dialog Caption
    Title = "Select a Remittance File to Open"
    ' Select Start Drive & Path
    ChDrive ("C")
    ChDir ("C:\sfp\Nov 2006")
    With Application
        ' Set File Name to selected File
        Filename = .GetOpenFilename(Filter, FilterIndex, Title)
        ' Reset Start Drive/Path
        ChDrive (Left(.DefaultFilePath, 1))
        ChDir (.DefaultFilePath)
    End With
    ' Exit on Cancel
    If Filename = False Then
        MsgBox "No file was selected."
        Exit Sub
    Else
        Set RemitFileName = Workbooks.Open(Filename)
        LoadName = RemitFileName.Name
        
    End If
    ' Open File
    'Workbooks.Open Filename
    
    Call SelectMacro

End Sub

'Step 2
Sub SelectMacro()
    'purpose is to search through the open workbook and run the appropriate macro based
    'on the Loan Field and Ending Schedule Value Field
    
    If Range("A6").Value = "InvLnNo" And Range("S6").Value = "END SCH BAL" Then
        Call macAS
    End If
    
End Sub

Here is part of the revised Step 3 and where I am now having a problem

Sub macAS()
'
Dim NetCellRng As Range
Dim GrossCellRng As Range

    Application.ScreenUpdating = False
        
    Range("A8").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Windows("remittance.xls").Activate '<--having this is copying into B2 of the page with the button rather than the third tab
    Range("B2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows("LoadName").Activate '<- I think this will be a problem too

Thanks, in advance, for the guidance

P

 

[alwayslrN]

I forgot to mention I have the following as public variables

Public LoadName As String
Public wbMain As Workbook

 

[SkipVought]

Hi,

Try this...

Sub macAS()
'
    Dim NetCellRng As Range
    Dim GrossCellRng As Range

    Application.ScreenUpdating = False
        
    Range(Range("A8"), Range("A8").End(xlDown)).Copy _
    Workbooks("remittance.xls").Worksheets("WhatSheetName????").Range("B2")
'    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
'        :=False, Transpose:=False
'    Windows("LoadName").Activate '<- I think this will be a problem too

End Sub

Skip,

_[red][/red]

 

[alwayslrN]

Well - I solved one problem

Windows("LoadName").Activate '<- I think this will be a problem too

should be

Windows(LoadName).Activate '<- I think this will be a problem too

I had used find/replace to replace 001 10-06 Spreadsheet emailed.xls with the variable LoadName, totally forgetting about the quotes.

Now I have to solve how to reference the correct areas (worksheet/ranges) in the remittance.xls workbook.

 

[alwayslrN]

Hi Skip - thanks

I tried

Sub macAS()
'
    Dim NetCellRng As Range
    Dim GrossCellRng As Range

    Application.ScreenUpdating = False
        
    Range(Range("A8"), Range("A8").End(xlDown)).Copy _
    Workbooks("remittance.xls").Worksheets("REMITTANCE").Range("B2")
    Selection.PasteSpecial Paste:=xlPasteValues, -Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

and received an error stating

Object doesn't support this property or method at the line

Workbooks("remittance.xls").Worksheets("REMITTANCE").Range("B2")

 

[alwayslrN]

My bad Skip -

I did not keep the comments that you had made on the paste lines. If I keep the comments it works, HOWEVER, I want to be sure that when I do the paste that no commas come over, hence why I was using copy paste special. I suppose I can code the formatting?

 

[alwayslrN]

Hmm...another issue with that little shortcut is I have several places throughout the macro and several macros where I would need to incorporate this change. The code I have now was generated by Excel when recording the macros. Is there an alternative that can use what I originally had? Otherwise, it is going to take me FOREVER to make these changes even if it is less code.

Thanks

P

 

[SkipVought]

Sorry.

Sub macAS()
'
    Dim NetCellRng As Range
    Dim GrossCellRng As Range

    Application.ScreenUpdating = False
        
    Range(Range("A8"), Range("A8").End(xlDown)).Copy
    Workbooks("remittance.xls").Worksheets("WhatSheetName????").Range("B2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

End Sub

Skip,

_[red][/red]

 

[alwayslrN]

Though Skips shortened code for the copy/paste from one workbook to antother is quite cool, it would be too much work on my part to replace all the current macro recorded code with the short cut. So what I have gotten to work is changing

Windows("remittance.xls")

to

ThisWorkbook.Worksheets("REMITTANCE").Activate

 

[SkipVought]

Unless you need to end up in REmittance, I would NOT Activate, just REFERENCE that sheet.

Skip,

_[red][/red]

 

[alwayslrN]

Skip what do you mean by just reference and then I can better address your comment.

Thanks

 

[SkipVought]

This code...

    Range(Activesheet.Range("A8"), Activesheet.Range("A8").End(xlDown)).Copy[b]
    ThisWorkbook.Worksheets("REMITTANCE"].Range("B2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

references the other sheet. It does not ACTIVATE the paste target sheet. Activate in this case is unnecessary and in general slows a process down.

I would Also explicitly reference what the ActiveSheet is for the COPY. The Range("A8") ASSUMES the ActiveSheet. The problem with this is, under some circumstances, you might NOT be on the sheet that you implicitly intend. You ought to solve that eventuatlity be explicitly referecing the parent object (sheet) for the range to copy.

Skip,

_[red][/red]

 

[alwayslrN]

Skip I want to make sure I am following by using an example.

Say the user has a workbook open and then opens the workbook with the button in it to execute the above code, there would be a problem in that the procedure as I have it now, could reference the previously open workbook and not the workbook that the code just opened?

For the record, if like I mentioned earlier in the post, I would change my code to be exactly like you demonstrated, but the dang blasted macro recording put the code as

Application.ScreenUpdating = False
        
    ChDir "I:\Real Estate\Single Family\Nov 2006"
    Workbooks.Open Filename:= _
        "I:\Real Estate\Single Family\Nov 2006\001 10-06 Spreadsheet emailed.xls"
    Range("A8").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Windows("remittance.xls").Activate
    Range("B2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows("001 10-06 Spreadsheet emailed.xls").Activate
    Application.CutCopyMode = False
    Windows("remittance.xls").Activate
    Range("E2").Select
    Windows("001 10-06 Spreadsheet emailed.xls").Activate
    Range("D8").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Windows("remittance.xls").Activate
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("F2").Select etc...

and I have several more macros with lines like that. It would just be painful to do the references like you mention. But that does not stop me from wanting to understand what you are suggestion.

changing the macro recording code of

Windows("001 10-06 Spreadsheet emailed.xls").Activate

to

Windows(LoadName).Activate

and

Windows("remittance.xls").Activate

to

ThisWorkbook.Worksheets("REMITTANCE").Activate

with a find/replace is a WHOLE lot faster than what I would have to do to get it like you showed me (us).

 

[SkipVought]

Well, go ahead and use the Activate method.

So your're saying that the user could have ANY workbook open, then open the workbook with the button and hit the button?

Does the procedure do ANYTHING to the original workbook?

How does it know?

Maybe the user has 2 or more workbooks open when they hit the button.

Part of my problem is, I don't understand your process.



Skip,

_[red][/red]

 

[alwayslrN]

OK Sorry for the confusion.

Workbook 1, in my first set of code above that is wbMain. That workbook has a sheet with a button, a sheet which takes the copy of data from the other sheets (I will talk about that in a moment), and all the macros.

There is a macro (Sub OpenSingleFile()) for the button which allows the user to select the file (excel spreadsheet or text file) to open. From there, there is a sub routine (Sub SelectMacro()) to determine which macro to run based on field names in the file that was just opened. Base on the existance of that macro the appropriate macro is called and run. For example macAS (a snippet is shown above). With those macros like macAS I am copying columns of data, one at a time, from the file that was opened into the workbook that had the button or rather, the workbook that initiated the process. So basically, I am moving back and for between the file that was just opened and the workbook that initiated the process.

I hope that helps.

Thank

 

[SkipVought]

If you want to copy columns of data from one workbook to another, ASSUMING that the columns are part of a TABLE, headings in ROW 1, this can be done without little or no code at all using MS Query via Data/Get External Data.

Check out Using MS Query to get data from Excel faq68-5829



Skip,

_[red][/red]