Maintain The Columns Using Macro

May 6, 2007

Imagine the spreadsheet contain informations.
(For eg, columns A to Z all got contents)
Then i want to delete columns A, B, C, K,L,M, X,Y,Z

Problem faced:

When i delete A,B,C
D,E,F . . will shift to the left.. becoming A,B,C
I need to use macro buttons so that next time i dont need to remember what to delete and what not to delete.

View 9 Replies


ADVERTISEMENT

Maintain Range When Inserting Columns

Sep 30, 2009

I have a macro recorded that inserts into column E in the attached report, and then retrieves data from another worksheet in the workbook. In column D I am trying to keep a 6 month average which includes the latest inserted data, however every time I insert a column the relationship with column D is moved out by one. e.g. range E8:J8 becomes F8:K8.

I am also stuck with setting up a macro to import a new model into the report. I have set up a worksheet called new model with the manufacturer, model and fail descriptions, however all macros I have recorded fail when trying to insert the manufacturer and model into mulitple cells.

View 9 Replies View Related

Maintain Control Focus

Jan 20, 2007

is it possible to always have the entire combox highlghted?

View 3 Replies View Related

Maintain History Of Exam Results

Mar 29, 2007

I want to maintain a history of exam results held on different dates

For this purpose I created a file with three sheets named Inputs, Working & result history. Description of all sheet is as follows :

“Inputs” Used as main Input file to feed marks for each subject on certain exam date.
“Working” Linked with sheet inputs and perform calculations ( total marks, % etc)
“Result History” Date wise history of exam results based on sheet (working).

Required Solution Steps:

1.Score entered for different subjects in Sheet “Inputs” for a particular exam date.
2.Obtained marks are totaled and score % calculated based on scores entered in sheet inputs.
3.Date wise history of exam results recorded based on working sheet.

To transfer/copy/paste results from “Working” sheet to “Result history” sheet I recorded a macro which is linked with a button. This macro copy record from working sheet and paste it as value to result history sheet....

View 6 Replies View Related

Maintain Column Header And Footer

Apr 23, 2007

This may seem like an odd request for help, and there may be other options that I am unaware of but....

I am looking to maintain a top header row and a bottom footer row. The sheet is 300 rows deep and If I put a freeze pane or split, the header or footer ends up scrolling off of the viewable screen.

The header range is A1:G6 The footer range is A301:G304. I am looking for 2 sub()'s. The first will check the DATA range A7:G300 and hide all empty rows between the header and footer ranges. The worksheet will display lets say 23 rows of data if the user needs to enter data in a new row I would like them to be able to run a macro that will hide the first data row and unhide the first empty data row below the last row that is not empty (in the data range), and a second macro that would do just the reverse. If there were any hidden rows at the top of the data range, the first hidden row above the first unhidden row would be unhidden, and the last row of the data range would then become a hidden row.

In essence it would allow the user to scroll through the data range and never loose sight of the header range & footer range.

View 4 Replies View Related

Maintain Formula Links After A Sort

Nov 27, 2007

I'm going to attempt to explain the problem I am having with a project in Excel. I have tried to search using several different terms but have not been successful in finding an answer to my problem. I came close with a MS help document entitled "HOW TO: Sort Cells Without Sorting Linked Cells in Excel 2000". But that document does not directly address the solution. I think there is a clue there but I can't figure it out.

What I'm trying to do:
I am trying to sort a data list that is one worksheet without affecting the links I have created to that data in a second worksheet. I have two worksheets; one holds the database, or list data; the other is a formatted price list where I have created links to the data in the first sheet. The idea is that I want to be able to manipulate the data in the data list and make changes to it while keeping the price list in a fixed format but with updated prices. I am using relative links and I have had some success in the past with these links automatically updating when inserting rows or cells, or moving the cell containing the formula. But this time I am trying to sort the data. When I sort the data the formulas in the linked cells simply point to the same cell reference as they did before the sort, producing the wrong results. I want the links to follow the referenced cells so that the results remain the same on the price list even when the rows in the data change position. For example:

The data on the worksheet named "Database" is in a list from A10:P65. I sort this list by two columns, first by Categories then by ManuPartNo. After I sorted the list I went to the worksheet named "Price List" and set up links to the cells in the data list that contained the correct information for my items. Some of my items are a combination of two or more rows from the data list. This works fine. If I make a change to the data in the list the price list automatically updates. If I insert a row or column to the data list the formulas on the Price List continue to work just fine. But, if I sort the data again, the formulas on the Price List no longer point to the correct cells in the data list. The formulas remain unchanged, but the rows of data move. I expected the formulas to update just like they do when I insert a row or move a block of data with cut and paste. Is there a way to force Excel to follow the rows as they move after a sort? Excel does this automatically when inserting rows. Why not with sorts? Where can I go to learn more about how to do this?

View 4 Replies View Related

Maintain Formulas & Formatting In New Table Row

Mar 12, 2008

I am working on a macro to insert a row below if a cell with a validation list has an item from the list selected. The new row needs to maintain the same format and formulas as the original row. To explain this better I have attached a very generic spreadsheet Called Custom Order. In this example cell A3 has a validation list. If the user selects one of the items in the list... I need the macro to insert a row below and maintain the format and formulas found in row A3.

Also this is posted on another forum. http://www.excelforum.com/showthread...=1#post1893257.........

View 2 Replies View Related

Excel 2007 :: Maintain Aspect Ratio?

Nov 11, 2012

I have a userform that opens up to full screen, however, when i designed it everything is central and in its place, but when the userform opens full screen everything is pushed to the left and not using all the form.

I know that in VB2010 u can use docking and table layout panels for this sort of thing but VBA 2007 which I am using with excel doesnt seem to have these options?

View 1 Replies View Related

Update Log Worksheet And Maintain A History Of All Orders?

Apr 20, 2013

I have a table that includes list of clients, products they order, and requested delivery date. When the client calls in to place an order, I pull up the excel table (orders worksheet) and fill in the items quantities and the required delivery date for the client who called. Once I fill the information for the client who called in, I need to update a "log" worksheet with the information that I just entered. The log worksheet will eventually contains history of all the orders which I received in the past.

View 1 Replies View Related

Sort Groups Of Data And Maintain Rows?

Jan 3, 2014

I need to sort groups of data by a single cell in the group, compared to other single cells in other groups of data. The groups are always the same number of rows but vary in the number of rows. There is an example file below. The red is the company name and the green is what I need to sort the group by, as compared to the other groups. This example only has a few, the real file has thousands of groups. I want to sort the companies by the totals in green...they are currently in alphabetical order. There must be a way to do this without going the VBA option.

View 4 Replies View Related

How To Maintain Formatting (such As Highlighting) When Importing In Ms Query

Apr 3, 2013

My current solution is to filter the Master Log so I only see Region 2, copy everything, then paste over Region 2, and repeat with 3, 4 and 5.

I have a master log (attached) that I import using Microsoft Query to 4 other spreadsheets (called Regional Logs), depending on the contents of the column "Region." I have all this figured out, but I can't figure out how to have the highlighting persist through the export and refresh. I don't think the attached file has any highlighting, but how I use this is I edit the Master Log throughout the week and highlight changes as I go. Coworkers use the 4 Regional Logs throughout the week to monitor different cases, particularly the new ones, so they need to see all changes for the week highlighted in yellow, which means the Regional Logs need to be linked to the Master Log and the highlighting must be visible any time a coworker opens the Regional Log. In addition, they are emailed across the state to county directors and they need to see the yellow-highlighted data, as well.

Currently, when exporting data using MS Query, no formatting persists. How can I ensure the highlighting persists? Or, is there another way to easily export the data, depending on the contents of the column "Region", which would allow the highlighting to persist

View 3 Replies View Related

Copy Non-Adjacent Cells But Maintain Spaces

Aug 12, 2013

I want to copy A1 and C1 (there is data in A2), but when I copy it somewhere else, I want it to maintain the column space with NO data. Can I do this?

View 1 Replies View Related

Copy Sheets From One Workbook To Another And Maintain The Order

Mar 15, 2012

I have code to copy sheets from one workbook to another, but how to maintain the order based on the Source Workbook.

Actually what i'm trying to do is copy all the Sheets named start with "_". FYI, i cannot use the Index of Sheets as well.

Code:

i = 0
For Each ws In Workbooks(xWBSource).Worksheets
If ws.Name Like "_*" Then
i = i + 1
sArrSheet(i) = ws.Name

[Code]...

View 1 Replies View Related

Copy Worksheet And Maintain Relative Hyperlinks

Nov 4, 2008

I'd like to be able to create a copy of a worksheet and maintain the relative hyperlinks within each worksheet. Currently, when I copy a worksheet, the hyperlink takes me back to the original worksheet.

I'm using Excel 2003.

View 9 Replies View Related

Hyperlink Does Not Maintain Path To Target File

Sep 19, 2007

I read about Hyperlinks in the August 2006 "OZGRID" Newsletters but found no answer.

Assume 2 workbooks - book1 and book2, both located in "c:" (root of drive C)

Now, In book1 I have declared an HL to book2 using CTRL+K and upon clicking in that cell the book2 is opened.

After MOVING(!) book1 to a new location (D:DATA) and clicking the HL - I get an Error "cannot open the specified file".

While book1 was still in root C: - I tried to force to c:ook2.xls by typing it in the HL address window but after saving, Excel deletes the "c:" and leaves "book2.xls" only – I think because both files were located I the same directory.

I am aware that my "problem" can be solved by using the function HYPERLINK – but I would like to learn (if possible) how to insert an HL using CTRL+K that will maintain the path although moving the file which contains the HL).

View 9 Replies View Related

Maintain Source Data Formatting In Pivot Table?

Nov 20, 2010

I have some source data which is used to generate a pivot table. The data is conditionally formatted to color certain cells based on some other values. Once the data is transferred to the pivot table this formatting (as well as the number formatting) is lost.

I have found some code to fix the number formatting issue but can't seem to locate something similar to set the interior color of the cells in the pivot table to match that of the original source data.

View 2 Replies View Related

Excel 2010 :: Maintain Pasted Chart Format

Oct 23, 2012

Using 2010 - When I paste a chart I can initially retain the format of chart bar colours (Paste Special - source formatting). But when I select a new data source for the chart the formatting of the colours is not retained.

View 1 Replies View Related

Excel 2010 :: Maintain Different Headers For Different Sheets When Printing

Aug 23, 2013

Excel 2010. I have a workbook that has multiple sheets where some have headers and others do not. Some sheets are static in that no user input is required and others are dynamic so the toal number of pages per worksheet may vary based on how much information the user inputs.

An example:

Sheet 1 is static and requires no input, it does not have a header or a page number. For compliance reasons this sheet must be printed on top of all the other sheets in this policy

Sheet 2 is dynamic. This sheet can range from 2 pages to 5 pages depending on user input. This sheet must have page numbers starting with 1 and it must have a header on page 2 through the last page, but no header on page 1.

I am currently using a worksheet_activate sub to input data into the headers and footers of sheet 2. This works very well and puts the data where I want it to go just by using the "Different First Page" option in the headers design tab.

The problem arises when I select both sheets to print. Excel now thinks Sheet1 is the first page, so the first page of sheet 2 now has a header and the page number beings at 2 instead of 1.

Where I can stop this from happening?

Current sub:

Code:
Private Sub Worksheet_Activate()
' If WS is activated, place information in header
Application.ScreenUpdating = False
'Policy #

[Code] ..........

As I said, this works all well and good for when I'm just printing Sheet2 but when I have to print Sheet 1 & 2 together, it no longer prints the way it should. There are times when Sheet 2 will be the first page of the overall document but there are a few instances where it will be second to Sheet 1 and I run into this issue. Is there a way to specifically reference which headers/footers the code will input the data?

View 1 Replies View Related

Maintain Sort Order Importing Text File

Sep 19, 2007

I am trying to create a Karaoke Song List in excel, which would show four columns on each page with Artist, Track, Artist, Track. I am importing a text file to do this. The problem I have is that I can import the file so that it puts Artist and Track going down, however, it would then need to go back to the top of the page to fill in the right hand side of the current page. Is there any code that retrieves the current print page number of the current cell (so that I can initiate the code to go to the top right of the current page and continue importing)? Or is there a better way of doing this?

View 3 Replies View Related

Macro To Unhide Columns - Call A Macro Then Revert Columns To Previous State

Jul 17, 2014

I have on sheet1 a number (72 at the moment) of Form CheckBoxes.

In simple terms: I would like a macro to look at each CheckBox and remember its state (Checked or Unchecked)

Then, go through and Check All checkboxes

Call MyMacro

Once MyMacro is complete (Filtering & Printing)

Revert the checkboxes to their original state.

The purpose of the checkboxes:

When Checked column on sheet2 is UnHidden
When UnChecked column on sheet2 is Hidden

Or, UnHide All columns on sheet2, run MyMacro, then "re-hide" the columns that were previously hidden.

View 11 Replies View Related

Calculate From Last Instance Ignore Blanks To Maintain Running Total

Aug 6, 2013

I have a spreadsheet that is tracking a MTD receivables (running total). When it comes to weekends or day's when their were no receivables the running total needs to reference the last working day or the last receivable entry to perform its calculations for that day.

So in the table below (couldn't post attachment) the first row(1) = days of week , second row (2) = running MTD totals and the 3rd row (reference cells). So for Wednesday I our totals were 9995 which I entered in Row 3 (column A) and called it to Row 2 (column A). For Thursday I called Row 3 (column b which was known and manually entered) and subtracted Row 3 (column A) and populated Row 2 (column b). So my equation to in Row 2 column b is simple as =sum(b2-b1)

This is ok but when the weekend (or days not worked) come in to play you can see it produces a negative for Saturday / Sunday. Saturday took 15,707 and subtracted it from nothing (row 3 column d) since there were no receivables on Saturday.

So I need a formula that will calculate from the last instance while ignoring blank cells.

Wednesday
Thursday
Friday
Saturday
Sunday
Monday

9,955.00
3,325.00
2,427.00
(15,707.00)
(0.00)
20,903.00

9,955.00
13,280.00
15,707.00

20,903.00

View 1 Replies View Related

Data Consolidate - How To Maintain Case Sensitive Data After Merging Cells

Dec 8, 2013

I'm looking for a way of keeping case sensitive data in a range of cells, before using Data Consolidate, which when merged afterwards, Consolidate removes the case sensitivity and combines the quantities into one.

Check out this simple table as an example >

A
B
C
D
E
F
G
H

1
TEXT
QTY
TEXT
QTY
TEXT
QTY
TEXT
QTY

2
and
3
and
2
and
10
and
5

[Code] .........

Cells A1-B7 and C1-D7 are two sets of original data, before consolidation.

Cells E1-F4 are the result of applying a Data Consolidate operation to the A1-B7 and C1-D7 ranges - note how the merged result ignores the case sensitive condition in the original ranges.

Cells G1-H7 is the post-consolidation result that I'm looking for, where the original text case is maintained.

View 3 Replies View Related

Macro To Compare Columns A & B And Dispaly Any Duplicates In Columns C & D

Feb 21, 2009

what I'm after is a macro to check the contents of Column 'A' against column 'B' and display any duplicates in Columns 'C' & 'D'.

N.B. The headings of Columns C & D are :-

C = Value Found in Column A

D = Value Found in Column B

Any duplicate entries logged in columns C & D should be listed in C2,C3,C4....C20 and D2,D3,D4......D20 etc (in effect creating two new lists)

View 5 Replies View Related

Macro Needed To Include Particular Columns Out Of So Many Columns In A Sheet

Apr 23, 2014

I have file with so many columns and i want to keep only columns i want.

Data
genredyellowgreenwhiteblue
1aaggttccbb
2aaggttccbb
3aaggttccbb
4aaggttccbb
5aaggttccbb

expected
genredwhite
1aacc
2aacc
3aacc
4aacc
5aacc

for example here i want to keep only gen, red and white columns only out of columns what i have in my data. I have so many columns in my original data but here i given just small example. How to proceed with macro or any other way because removing manually taking long time for me.

View 6 Replies View Related

Building Macro That Runs Another Macro Based On Certain Columns

Jun 6, 2012

Essentially, I am working with a database for a school and up until this point, each row matches up to a student with a bunch of information on them. One of these pieces of info is their parent's names. This is a much simplified look at things, but the screenshot below is a basic version of what everything looks like:

Now, the school wants me to split up the parents names from the student's and place them in their own row. I have recorded a macro to do this where you make the active cell the "full name" column for the row you want to expand, and then run the macro. Afterwards, up to two more rows are added, with the parent's names and some data copied. Below is a screenshot of what that looks like: (yellow is the new data generated, and blue is the active cell I marked to run the macro properly)

The issue is that there are thousands of these that need to be done, and I figured there is a way to do this with a macro. The issue is that not every row has parents names to expand out.

In layman's terms, I need a macro that will look at the two columns titled "Father's Name" and "Mother's Name" on each row of the spreadsheet and then, if there is any data in both or either of them, it should set the active cell to the "Full Name" column for that row then run the macro accordingly. After that it should repeat this all the way down, skipping any where neither of the columns have text in them

In the actual spreadsheet, the columns that need to be checked are AW and AX, and the column that needs to be set to the active cell before running the macro is B.

View 5 Replies View Related

Paste First Chart Into Second Chart But Maintain Format Of Second Chart?

Jul 12, 2013

How do I paste the first chart into the second chart but maintain the format of the second chart?

View 2 Replies View Related

Macro To Populate Columns

Jul 17, 2012

I am trying to write a macro to populate column A "main sheet"with all data from column A "info sheet", Also, in the "main sheet" Column B add a Batch number from a static cell value B5 in "Settings Sheet" every time we have data in Column A. then insert the today's date in Column C "main" every time we have data in column A.

I put a sample of the data I want to see in my "main sheet" ...

View 2 Replies View Related

Copying Columns From One Tab To Another Via Macro

Dec 17, 2013

I have 2 tabs (tab 1 is "Data", tab 2 is "Compare")

Data tab is filled with results from tests I do in a lab, each test has its own row, each 'test subject' has its own column.

In the second tab I want to have the ability to pick from a drop down list one of the other columns from the first tab.

So I have 2 columns (D and F) in tab 2 with drop down lists that I have populated with the names of the test subjects (using data validation grabbing the top row in tab 1).

I have formulas set up in Column E of tab 2 for the actual comparison.. What I'd like to do is when I select a test subject (for example SubjectA), from the drop down list in $D$1.. I want to populate $D$3:$D$155 with rows 3-155 of the matching column in tab 1 (so if for example SubjectA is in column X, it would copy from tab 1 X3:X155 to tab 2 D3:D155.

So so far I have the validation part done with the drop down list.. and I have the following VB code for tab 2.

View 4 Replies View Related

Autofill Columns Macro

Mar 29, 2014

Problem: The following code autofills columns O:P beyond the last row of data in column H.

Range("O3:P3").AutoFill Destination:=Range("O3:P3" & Range("H" & Rows.Count).End(xlUp).Row)

View 3 Replies View Related

AutoFit Columns With Macro

May 14, 2014

I am trying to program a macro to AutoFit 29 columns on 3 different sheets. However it keeps coming up with "run-time error '1004' select method of range class failed". Here is the code

[Code] .....

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved