Workbook is lacking a lot of functionality

[hallingh]

Hi all

I have a bunch of workbooks containing budget figures. The workbooks have yearly budgets broken down into daily budgets, with monday through friday in columns L through r and weeks 1 through 52 in rows 3 through 54. I need to get this budget in one row, which I've done using the following code:

Sub GetBudget()
Dim X As Double
Dim Y As Double
Dim Z As Double
Z = 1
For Y = 3 To 54
For X = 12 To 18
ActiveSheet.Range("U" & Z).Value = ActiveSheet.Cells(Y, X).Value
Z = Z + 1
Next X
Next Y
End Sub

However, with the second to last workbook I'm trying to do this for I'm running into issues. For whatever reason, when I click the "Visual Basic" button, the workbook is not expandable, and doesn't have any of the worksheets listed in the project explorer (there are 6 worksheets.) When I try to record a macro and edit it to see its contents, it records the macro using a new workbook and takes me to the new workbook's respective vba code. It also won't allow me to protect or unprotect the sheet. It seems something has caused the workbook to lose a lot of its functionality.

Does anyone have any idea what is going on here? I have tried to save it as a macro enabled workbook, a 97-2003 workbook, and a regular excel workbook (.xlsx). I'm running out of troubleshooting ideas!

Thanks for any help you can provide. I greatly appreciate it!

Hank

 

[tusharm]

You shouldn't need to put the code in each workbook. If I were in your shoes, my code would be in a single workbook that would *not* contain any of the data.

Also, the following code is, IMO, a lot cleaner and possibly quite a bit faster.

Sub GetBudget()
    Dim Y As Integer
    For Y = 3 To 54
        With ActiveSheet
        .Cells(Y, 12).Resize(1, 7).Copy
        .Range("U1").Offset(0, (Y - 3) * 7).PasteSpecial xlPasteValuesAndNumberFormats
            End With
        Next Y
    End Sub

Hi all

I have a bunch of workbooks containing budget figures. The workbooks have yearly budgets broken down into daily budgets, with monday through friday in columns L through r and weeks 1 through 52 in rows 3 through 54. I need to get this budget in one row, which I've done using the following code:

Sub GetBudget()
Dim X As Double
Dim Y As Double
Dim Z As Double
Z = 1
For Y = 3 To 54
For X = 12 To 18
ActiveSheet.Range("U" & Z).Value = ActiveSheet.Cells(Y, X).Value
Z = Z + 1
Next X
Next Y
End Sub

However, with the second to last workbook I'm trying to do this for I'm running into issues. For whatever reason, when I click the "Visual Basic" button, the workbook is not expandable, and doesn't have any of the worksheets listed in the project explorer (there are 6 worksheets.) When I try to record a macro and edit it to see its contents, it records the macro using a new workbook and takes me to the new workbook's respective vba code. It also won't allow me to protect or unprotect the sheet. It seems something has caused the workbook to lose a lot of its functionality.

Does anyone have any idea what is going on here? I have tried to save it as a macro enabled workbook, a 97-2003 workbook, and a regular excel workbook (.xlsx). I'm running out of troubleshooting ideas!

Thanks for any help you can provide. I greatly appreciate it!

Hank

 

[hallingh]

Thanks for the response, and for the improved code.

I should have clarified my issue. My problem was not that I couldn't accomplish what I needed to in this instance. My problem is that this is a workbook we use regularly, and I can't get into its vba code, and I can't protect or unprotect sheets. I'm assuming that much more of Excel's functionality is disable, but these are the most pressing functions.

Does anyone have any clue what could be causing this?

Thanks
Hank

 

[hallingh]

In fact, I can do almost nothing that has to do with altering the the workbook at all. I can set the print area, change the view, and do anything that has to do with changing individual cells' values, like add formulas etc. I can't change formatting, insert anything (graphs, charts, images, etc), get external data, sort data, add comments, and many others. I'm missing nearly all of Excel's functionality.

Thanks for any help you can provide.

Hank

 

[tusharm]

One possibility is that the vba project is protected. Another is that the workbook is shared. I know that shared workbooks have several restrictions related to the code in them, though I don't know if the restrictions extend to access to the vba project.

Thanks for the response, and for the improved code.

I should have clarified my issue. My problem was not that I couldn't accomplish what I needed to in this instance. My problem is that this is a workbook we use regularly, and I can't get into its vba code, and I can't protect or unprotect sheets. I'm assuming that much more of Excel's functionality is disable, but these are the most pressing functions.

Does anyone have any clue what could be causing this?

Thanks
Hank

 

[tusharm]

One possibility is that the workbook is protected. Another is that the workbook is shared; again, while I know sharing imposes many restrictions on a workbook, I don't know if it encompasses all the issues you've brought up. The last possibility is that the workbook is corrupt and you may want to transfer whatever you can out of it. I have never encountered such a scenario but I've seen anectodal evidence about such corruption.

In fact, I can do almost nothing that has to do with altering the the workbook at all. I can set the print area, change the view, and do anything that has to do with changing individual cells' values, like add formulas etc. I can't change formatting, insert anything (graphs, charts, images, etc), get external data, sort data, add comments, and many others. I'm missing nearly all of Excel's functionality.

Thanks for any help you can provide.

Hank

 

[hallingh]

Hmm, how would I find out if it is a shared workbook? If I did a Save As would that solve it?

 

[hallingh]

Nevermind, a quick google search returned the steps I needed. You were correct, it was a shared workbook, though I'm not sure why.

Thanks a ton for the help!