Current FileName

[dgrimm]

OK, I have looked and tried several things for this. I am trying to get the current filename as a variable. Here is the code that I currently have.

Sub CopyData()
'
' This macro copy's data onto another sheet

Dim DataSht As Worksheet
Dim ActSht1 As Worksheet
Dim SetCell As Range
Dim CopCell As Range
Dim CurrWB As Workbook


Set CurrWB = ActiveWorkbook
Set DataSht = Sheets("Sheet1")
Set ActSht1 = Sheets("Sheet3")
Set SetCell = ActSht1.Range("A3")
Set CopCell = DataSht.Range("A3")


CurrWB.Select
DataSht.Select

CopCell.Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy

ActSht1.Select
SetCell.Select
ActiveSheet.Paste

End Sub

With CurrWB being the current workbook. I keep erroring out on the CurrWB.Select. I need this because the name will change over time so I can't set it to any particular name.

I am putting this in just in case the user has several workbooks open at the sametime and I don't want the pasting and copying to happen in the wrong workbook. My plan is to create a macro to call this and several others to run a check between two files. So what mistake am I making in order to get the current filename.

Thanks

Dave

 

[Andrew Poulsom]

A Workbook doen't have a Select method. but it does have an Activate method. That said there is no need to do any selecting to Copy/Paste. Try:

Sub CopyData()
''
'' This macro copy's data onto another sheet
'
    With ActiveWorkbook
        .Sheets("Sheet1").Range("A3").CurrentRegion.Copy .Sheets("Sheet3").Range("A3")
    End With
End Sub

Are you sure that you want ActiveWorkbook and not ThisWorkbook?

 

[Norie]

Dave

I don't think it's erroring out because of the name - one of the reasons to use something like ActiveWorkbook is so you don't need to hardcode the name.

Mind you, if you want to refer to the workbook the code is in you should use ThisWorkbook not ActiveWorkbook.

Why are you selecting anyway?

It's almost always not needed and I'm pretty sure it isn't in this case..

Sub CopyData()
'
' This macro copy's data onto another sheet
Dim DataSht As Worksheet
Dim ActSht1 As Worksheet
Dim SetCell As Range
Dim CopCell As Range
Dim CurrWB As Workbook
Dim CopRng As Range
 
    Set CurrWB = ThisWorkbook

    Set DataSht = CurrWB.Sheets("Sheet1")

    Set ActSht1 = CurrWB.Sheets("Sheet3")

    Set SetCell = ActSht1.Range("A3")

    Set CopCell = DataSht.Range("A3")

    Set CopRng = Range(CopCell, CopCell.End(xlToRight).End(xlDown))
 
    CopRng.Copy SetCell
 
End Sub