YouTubersLoveExcel#34: Hide Columns Copy Problem

Hello,

I have a sheet with 2 years of data
Columns B-M have Jan-Dec data for 2010 and N-Y have 2011.

When we are sending out the sheet we only want to show the current month and the previous years month.

So for Januarys version we would hide C-M and O-Y
In feb we would hide B and D-M and O and Q-Y and so on and so on...

I need a vba way of doing this as I have a preperation macro which applies all last min formatting and cleanup (removal of links etc) and this is the last issue I cant really resolved without going into the macro each month to fix it.

The reporting month we are in is in a cell in a sheet say A1 in sheet X, so the macro would need to read the date to understand what columns to hide.

Any ideas?

Thx

I am using excel 2000

My worksheet has data in columns A - Q

I'm trying to hide columns E - N, but the code below hides all columns A-Q, how do I only hide columns E-N?

HTML Code:

ActiveSheet.Unprotect Password:="password"
    Columns("E:N").Select
    Selection.EntireColumn.Hidden = True
        Range("A1").Select
    ActiveSheet.Protect Password:="password"

I just want this code to hide columns v through AG. When I run it, it hides A through AK.

I think the problem is that cells At through Akt are merged. Does anyone know a way to hide these columns without hiding all the columns associated with the merged cell?



Sub HideMonthly()
'
' HideMonthly Macro
'

'
Columns("V:AG").Select
Selection.EntireColumn.Hidden = True
Range("A1:AK1").Select
End Sub

Ok so here is my problem...I supervise a team...I have an Excel sheet with all client information...For clinical supervision I want my team members to open their own "Supervision.xls" and click a button...this button will open "Client.xls" and select "Client info sheet"...It will then hide rows c,d,g & f...it will then filter column "e" based on the specific caseworkers name ( say "Joe") and copy only the visable columns back to their "supervision.xls" Values only (doesn't effect the colour formating of Supervision.xls) then close "Client.xls"

Help please....

Hello All,

I am currently working on a macro that I have gotten as far as I can with mostly due to things I have learned from other threads on this site. I have a lot of experience with Excel but this is my first macro that I have built.

I am hoping that someone might be able to help me. I need to copy 2 columns from each tab of a workbook based off of an auto filter and paste them in to a new workbook. The problem I am having is when the auto filter returns no data then it copies the entire sheet as if the filter was never applied and pastes it to the new workbook.

Here is the Code that I have so far.
VB:

Columns("A:L").Select 
Selection.AutoFilter 
ActiveSheet.Range("$A$1:$L$900").AutoFilter Field:=9, Criteria1:="=" 
ActiveSheet.Range("$A$1:$L$900").AutoFilter Field:=4, Criteria1:="<>" 
ActiveSheet.Range("$A$1:$L$900").AutoFilter Field:=8, Criteria1:="<>" 
Columns("A:B").Select 
Selection.EntireColumn.Hidden = True 
Columns("E:L").Select 
Selection.EntireColumn.Hidden = True 
Rows("1:1").Select 
Selection.EntireRow.Hidden = True 
Selection.CurrentRegion.Select 
Selection.Copy 
Workbooks.Add 
ActiveSheet.Paste 
Range("A65536").End(xlUp).Offset(1, 0).Select 
Windows("AutoRemit2010.xls").Activate 

If you like these VB formatting tags please consider sponsoring me in support of injured Royal Marines



The code repeats for each tab in the workbook then after they have all completed the macro goes through and removes the filters and unhides everything.

any help would be great.

Thanks.

Greetings.

I have a sheet with 7 buttons, each button attached to a macro that wil show/hide the relevant columns for the button.

The sheet has Row 1 with Headings but not in consecutive cells. Sometimes the cells are blank until the next heading appears.
So it may be A1=Heading1, B1-F1 Blank, G1=Heading2, H1-S1 Blank, T1-Heading3, etc.

Row 2 has subheadings for each section, no blank cells.

Each button relates to each Heading. So clicking the Button 1 will show/hide the columns from A to F, Button 2 G-S, and so on.

The macro I have attached to the buttons is as follows:

Sub ShowHide_Heading1()
If ActiveSheet.Columns("A:F").Hidden = True Then ActiveSheet.Columns("A:F").Hidden = False Else ActiveSheet.Columns("A:F").Hidden = True
End Sub

with each button having it's own macro with the column letters adjusted accordingly.

My question is this:

If I add a column in the sheet, the macros have to be manually adjusted.
How can I code in any changes so the code takes care of any column changes?

I was thinking of something like looking up the column of the desired heading (in the above example this would give the A parameter), and then looking up the column of the next heading - 1 ( this would give the F parameter). However my VBA skills are of the cut and paste variety, and I haven't worked out how to achieve my aim.

Is there anyone with the skill who can help?

Thanks in advance.
the Tigg

Hello,

I am looking for a macro to hide columns in a sheet based on the hidden columns of another sheet.

The reason I want to do that, is to be able to copy rows from one sheet to the other and I want the data to appear exactly in the same columns.

thanks a lot
Andy

Just to save one hour...

Perform this test :
Enter into the 256 available columns, row 1, anything you want, just to use all the columns;
Enter a commentary in the cell(1,254).

Now try to hide columns (1 to 4 ) : you will get a message :
"Impossible to move objects outside of the worksheet"
And your action will not be executed.

Stupid, isn't it ?

Just cancel the commentary and hiding columns will again be possible.

With a positive mindset, this is a way to forbid users to hide columns...

Hi All,

I went through the threads and I couldn't find the solution which could be helpful so...maybe someone;P will be able to find the way how to deal with the issue I encountered when I switched from excel 2003 to 2007.

Macro is located in workbook 1 and it opens another one (workbook 2) where it should auto filter data in some columns and then copy particular columns and paste them (only visible cells) in the workbook 1. Sounds very simple but...it's not:P. When I was using excel 2003 it worked smoothly but now the values which I get are blanks. In Debug mode I can't find any issue. Before you ask me...the autofilter code works fine because in other part of the macro in which it calculates the number of visible cells it gives correct values.

This is the code for copy paste.:

'copy from 8th column
xlApp.Worksheets("Main").AutoFilter.Range.Columns(8).SpecialCells(xlCellTypeVisible).Select
xlApp.Worksheets("Main").AutoFilter.Range.Columns(8).SpecialCells(xlCellTypeVisible).Copy
'paste in 1st column starting with A1
Worksheets("TEMP").Activate
Worksheets("TEMP").Range("A1").Select
Worksheets("TEMP").Paste

I will be really grateful if you could help me with this one.

Thanks in advance