Workbook is lacking a lot of functionality

hallingh

Well-known Member
Joined
Sep 11, 2010
Messages
769
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:

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
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
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.


Code:
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:

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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
Hmm, how would I find out if it is a shared workbook? If I did a Save As would that solve it?
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,211,744
Messages
6,103,670
Members
447,876
Latest member
rodqntr

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top