CODEWITHSUNDEEP
excelvbavariablescopy-paste
Trey Jackson
Trey Jackson

Reputation: 1

Referring to Workbook/Worksheet with variable names

Sub CpyProdSch()
Dim wbkOpen As Workbook    
Dim wb2 As Workbook    
Dim wsName As String    
Dim strFileName As String    
Dim strFilePath As String    
Dim MsgBoxResult As Long    

strFilePath = "\\Ykf001\grpdata\PUBLIC\Operations\Converting Schedule\"  

' this could also be a constant
strFileName = Dir(strFilePath)


Set wbkOpen = Workbooks.Open(strFilePath & strFileName, False, True)

'It stops here - or at least it appears to - defaults back to VBA view and does nothing further

    ActiveWorkbook.ActiveSheet.Range("A1:BN500").Select
        Selection.Copy

    Workbooks("Raw Data pRODUCTION.xlsx").Worksheets("production    Data").Range("A1:C1").Select.PasteSpecial Paste:=xlPasteValues, _
         Operation:=xlNone, SkipBlanks:=True, Transpose:=False
    Application.CutCopyMode = False

End If
wbkOpen.Close False

Application.ScreenUpdating = True

As you can see I have a variable workbook name, as the only way I can refer to it is knowing its the only excel file in the folder I'm accessing - so this isn't as simple as activating a known workbook name.

As it stands now this portion of the code OPENS the workbook fine, it just won't do anything in the workbook because I think I'm referring to it incorrectly.

Additionally, the sheet name with the data changes: Currently it says August 29, before it says July 14, its variable but it is always the first sheet. For some reason when the workbook opens though it opens to the second sheet.

I need to activate the first sheet in the workbook I just opened so that I can copy and paste a range into my "Raw Production Data" workbook.

Here is the code now [code] Set wbkOpen = Workbooks.Open(strFilePath & strFileName, False, True) Sheets(1).Activate Set wbkOpen = ActiveWorkbook

    wbkOpen.Sheets(1).Range("A2").Copy

     Workbooks("Raw Data pRODUCTION.xlsx").Worksheets("Production Data").Range("A1").Select.PasteSpecial Paste:=xlPasteValues, _
            Operation:=xlNone, SkipBlanks:=True, Transpose:=False
            Application.CutCopyMode = False
   [/code]

It still just opens the workbook and stops. I trimmed it down to just select sheets(1), and nothing happens after it opens the book. And the code WAS working this morning. I am baffled.

I did some tinkering so this is odd.

No matter what the file path is, where the sub is, or what the excel workbook is every single time the workbook will open then the macro will stop.

It's completely irrelevant of the action, because once that file opens the macro just stops.

Any ideas?

Upvotes: 0

Views: 26697

Answers (2)

Siphor
Siphor

Reputation: 2544

ActiveWorkbook.ActiveSheet.Range("A1:BN500").Select
Selection.Copy

Should be

wbkOpen.Sheets(1).Range("A1:BN500").Copy

Workbooks.Open returns the workbook it opens so you can use that.(Note that wbkOpen. isnt even needed because a newly opened workbook is always the active workbook) You can get the first sheet of a workbook with Sheets(1). The .select and then selection.copy can be simplified to just .copy

In

Workbooks("Raw Data pRODUCTION.xlsx").Worksheets("production    Data").Range("A1:C1").Select.PasteSpecial Paste:=xlPasteValues, _
                Operation:=xlNone, SkipBlanks:=True, Transpose:=False
                Application.CutCopyMode = False

is a select that does nothing useful, so it should be

Workbooks("Raw Data pRODUCTION.xlsx").Worksheets("production    Data").Range("A1:C1").PasteSpecial Paste:=xlPasteValues, _
                    Operation:=xlNone, SkipBlanks:=True, Transpose:=False
                    Application.CutCopyMode = False

Upvotes: 0

n8.
n8.

Reputation: 1738

It's actually fairly shocking that this works at all, just goes to show that there's always something new to learn. Instead of doing this:

Set wbkOpen = Workbooks.Open(strFilePath & strFileName, False, True)

You could do this:

Workbooks.Open strFilePath & strFileName, False, True
Sheets(1).Activate
Set wbkOpen = ActiveWorkbook

Works for me.

Upvotes: 1

Related Questions