Deconstructing Code - Tranposing Data

Apr 6, 2009

I received this code from Joe a while back and it works soooo wonderfully, I thought I might be able to apply it to another fun activity. I'm having trouble understanding most important part of the code - in red (go figure)....can someone translate? the original thread was here: http://www.mrexcel.com/forum/showthread.php?t=325705


Sub MyNewFormatMacro()
' Copies data from Sheet1 to Sheet2 in new format

Application.ScreenUpdating = False

Dim myNumColumns As Long
Dim myNumRows As Long
Dim c1 As Long
Dim r1 As Long
Dim r2 As Long

' Determine number of columns from number of titles in header row
Sheets("Sheet1").Activate.......

View 9 Replies


ADVERTISEMENT

Sorting And Tranposing Of Data

Jun 26, 2009

I have a spreadsheet which the data looks like the below. The below example has been filtered using subtotal function...

View 9 Replies View Related

Tranposing An Array Of Cells

Sep 28, 2009

Looking to take an array of cells and paste them from left to right as right to left. for example if I have 3,2,4 and want them to be 4,2,3. The paste special option will make them vertical and then horizontally but not how I am expecting them to be.

View 5 Replies View Related

Data Connection From Web / Web Query Returns Code Instead Of Selected Data Table

May 31, 2014

A web query I am trying to use to bring an online data table into my worksheet is broken. Now, instead of returning the data table nicely into my worksheet, it imports the code of the webpage, instead, and turns my worksheet into a mess.

The query used to work but there was recently an "upgrade" to the program that populates the web table and the query no longer works and just returns the code for the page, instead. I can see the html code for the table in all of the code it returns but I really need the table to import cleanly into excel.

I can't talk to the people who changed the web program.

View 3 Replies View Related

Excel 2007 :: Using VBA To Assign Row Of Data - Code Based On Data In That Row

Feb 17, 2012

I have a workbook (excel07) that I use to import data onto our purpose built database. I have some macros to sort the data and send it to the correct columns however the one stage I still have to do manually is assign each row its own unique sorting Code (in a bespoke column "B2:B999").

These are objectives and the code should contain three seperate parts (part 1a and b do not need to be seperated);

1a. The characters "PO" should be the first in the code (for sorting purposes)

1b. The persons initials (first letter of the first name and last name) found in column J2:J999. [They are sorted by name so It would also be good to add a number after the initials seperated by a space incase of multiple objectives however I could defintly live with typing these in manually]

e.g. "Joe Blogs" second objective = JB02

2. The numerical part of the stategic objective they have selected found in column H2:H999. [incase you need this these range from 1.1-1.4, 2.1-2.5,3.1-3.6 and 4.1-4.5 all with a short text strings after which shouldnt be included in the code]

e.g. "1.1 We are Committed to being nice" = 1.1

3. The year they are applicable for which will all be "12/13" however I will need to change this next March.

Therefore the final code should appear as "POJB02 3.1 12/13"

There is a 16 Character limit on these codes but all of that information is needed for sorting, filtering and report generation, the spaces could be replaced by "_" if that makes things easier.

View 5 Replies View Related

VBA Code To Extract Data And Remove Unwanted Data In Row

Dec 15, 2013

I have a workbook that has ben imported into excel and I need to extract the Cabinet sizes (red text) from ColumnC On Sheet2 leaving out the rest of the info. I have a button on the home sheet that has the code that I am running to generate this report it will do most of what I want but I am stuck at extracting the cabinet sizes.

The data is coming from the SheetComponetListing worksheet and going to Sheet2. I have manually created the end result that I am looking for on the CabinetSize worksheet. [URL]....

View 3 Replies View Related

2002 Code V 97 Code: Add A Small Workbook Open Event Code Which Works For Me But Debugs For The Others

Jan 27, 2009

I use excel 2002 but some of my office are on 97, i want to add a small workbook open event code which works for me but debugs for the others?? The code is basically, go to a tab, on that tab and that range sort..

View 2 Replies View Related

Cracked Vb Code: Prevent People From Accesing The Code I Protected The Code Blocking It From Visualization

Feb 8, 2007

I've developed a little software using Excel Macros & VB. To prevent people from accesing the code I protected the code blocking it from visualization. It seems not enough as an acquaintance of a friend cracked it in 25 minutes. Or so he says. So I'd like to know if there is a better way to protect the font code.

View 8 Replies View Related

Code To Add Data To Next Available Row

Jun 28, 2007

I am "pushing" information from MS Word Fill-In Fields to Excel. Currently, the code (which is intended to run in Word) is overwriting the "pushed" data in the same cells in Excel (row 1). I would like it to add new data to the next available rows [kind of like the End(xlUp).Row + 1 code, but for Word].

Dim Fld As Field
Dim ExcelSheet As Object
Dim iColumn As Integer
iColumn = 1
Set ExcelSheet = CreateObject("Excel.Sheet")
ExcelSheet.Application.Visible = True
For Each Fld In ActiveDocument.Fields
ExcelSheet.Worksheets(1).Cells(1, iColumn) = Fld.Result
iColumn = iColumn + 1
Next Fld
ExcelSheet.SaveAs "C:TEST.XLS"
' Close Excel with the Quit method on the Application object.
ExcelSheet.Application.Quit
' Release the object variable.
Set ExcelSheet = Nothing
End Sub

View 9 Replies View Related

VBA Code For Exporting Data?

Dec 24, 2012

I am trying to get some data to export from one worksheet to another, but it doesn't seem to be working.

I have put the code below:

VB:
Sub ExportData()
Sheets("Data Export").Select
Range("B3:K5").NumberFormat = "@"

[Code].....

All VBA code posted in the forum must be wrapped in code tags, which you omitted, including single-line code snippets.

How to use code tags

[code]
your code goes between these tags
[/code]

Or, just highlight all of the code and press the # button to add the code tags

View 3 Replies View Related

Sort Data Without Using Code

Dec 12, 2009

Can a sorted array be produced from a table without using code? The attached sample gives a better idea of what I'm trying to do.

View 4 Replies View Related

Looping VBA Code Until End Of Data?

Jul 2, 2014

trying to get macro to loop until there's no data to process.I have data in two columns A&B which it creates a chart from I then need it to skip C (which is blank) and do the same chart for columns D&E and so on indefinitely until there is no data to process..

I have attached a very simplified version of the data, the data is made up so the charts don't make sense but if you run the macro you'll see what I'm trying to achieve.

View 4 Replies View Related

How To Get Data From Text Box To Code

Jul 19, 2014

Its been 3 days im trying to use vba for excel. However after lot of struggle i was able to make user form in which i enter data in textbox1 it searches from the sheets and puts the data in the rest of text boxes On the other hand i made a code which makes a word document basically a notice. Which uses data such as names and ids from cells but now i want to mix both. As now i want is that if i search using the user-form i want to add one more button to that same user-box which executes data from that user form to put values on notice

View 2 Replies View Related

VB Code To Transpose Data

Mar 26, 2014

Please refer to attached file.

I have employee Clock IN - Clock Out as shown in Column A thru C. An employee can have more then 1 Clock In Clock Out as shown for Employee Name Karmen and Haley.

As shown, each employee is separated by "----------------------" and it ends with "REPORT END"

I would like a VB Code to transpose the data as shown in Column G thru H ...

View 2 Replies View Related

Moving Data With VB Code

Jul 24, 2007

I have a workbook with 5 Tabs. One of these tabs is "Completed" (for completed work) The other tabs are names of Managers and the tabs contain information about who is doing what work for the Manager and information about it.

What i would like to do is in column F on every sheet is the "status". I would like when the "status" is changed to completed, to have VB code move that entire record to the Completed tab.

I think its possible i just don't know how to do it.

View 14 Replies View Related

Code For Lookup With 2 More Or Same Data

Mar 6, 2008

I need to lookup a data which have 2 or more same data from the output sheet and the source sheet. i have used concatenate function but i need the invoice number also in the output sheets.

example i have a list of Order NUmber, Qty and Invoice Number. There can be a chance where the Order Number are equal but different Invoice number. I need in my output data the correct Invoice number and Qty.

View 11 Replies View Related

End Of Range/Data Code

Oct 27, 2009

I have been searching for an answer on this but unfortunately to no avail. I think it is xlEnd or down?

I am using the following

View 6 Replies View Related

Code For Transfer Data

Dec 22, 2009

i need your help by a code to transfer the data mentioned in ( main ) sheet ( in attached file ) to the ( 8 agents ) sheets which addressed (A,B,C,D,E,F,G,H) ( in same cells for easy using the code ).

View 10 Replies View Related

VBA CODE IN WORD - FROM Data

Apr 10, 2009

Is there anyway I can get Cell Data to go over to a VBA variable in Word .. Ive tried googling but not coming up with much...

For example.. I have a cell with the number 10 in it... Id like that to get sent over and be assigned to a string variable "Attended" in word....

View 9 Replies View Related

VBA Code For Transfer Of Data

Apr 17, 2006

We have a worksheet entitled 'Data Output' that has the raw data in column B. The data represents temporary input from another worksheet that serves as an intake form. Each person's intake data will temporarily populate column B of the Data Output worksheet. When each intake is complete, I want Excel to take the data in Column B and find the next available empty column in a worksheet called 'database page 1' and populate it with the data starting at row 3 downward. When the last column in database page 1 worksheet is reached, I need for Excel to go to 'database page 2' worksheet and begin populating the next empty column and so on.

Is there anyone within your forum or group that can help me write the VBA code for this and briefly tell me where in Excel to insert the code. I know formulas basically well, but have never used VBA.

View 5 Replies View Related

Sort Data With Code

Dec 20, 2006

I have a set of Data and I would like to order the data based on a column which is G and contains Dates. I would like to order the data Desc order but this must be done in VBA as its a monthly report and needs to be scheduled.

View 3 Replies View Related

VBA Code To Pull Data From A Sheet Based On Date And Add That Data To Another Sheet?

Aug 8, 2014

I use my workbook to track sales data from one store to the next. I use my workbooks to compare data from year to year. Each year's data is displayed on a separate worksheet. '2013' has 2013's data, '2014' has 2014's. On the 2014 sheet, I have a Prior Year's Sales that pulls data from the previous year's spreadsheet using a formula which I just drag down each day I enter sales. I would like to automate this process and have the VBA code check for today's date and automatically pull the previous year's sales data from the '2013' sheet and put it in the appropriate cell on the '2014' worksheet. I hope I have explained this well enough to understand. I've included a link to my workbook for reference.

I had to use dropbox since I can't post a file over 1 MB. The file size is around 1.25 MB.

View 5 Replies View Related

Filling Data Depending On Zip Code

May 21, 2014

I am working with monsterous excel sheets (named Data) and need to a column that is governed by the ZIP. I have a sheet named Source in my excel folder with all the matching information next to the excel sheet. The column my zip codes in Data starts on J6 to J290 and the zip codes in Source are from A2 to A2671. The information I am trying to pull from Source to Data are names and branches located in Source from B2:E2671.

What function will auto fill the columns with the correct information. If it can only be done by putting a function into each column, that is not a problem

View 1 Replies View Related

Formula / Code To Find And Sum Data

Dec 10, 2013

In the attached sample sheet, Col B has data that will more often than not be duplicated. Each Line will have a corresponding value in Col L.

What I need to be able to do is this:

Starting from row 2 (important) is find duplicates and then place the corresponding figures for each from Col L as a Sum into Col N.

So '45678' appears twice in Col B and the Sum of the figures in Col L for '45678' is 1, so 1 will now show in Cell N2.

'55685' appears only once in Col B so it will have the "Sum" of 12 in Cell N3.

'99954' appears 3 times in Col B and has a sum of 9 from the Col L values so that will be displayed in Cell N4 and so on.

Hammer Sample #2.xlsx‎

View 2 Replies View Related

VBA Code To Copy Data To New Workbook

Mar 24, 2014

I've not had much experience with VBA code, other than recording macros and editing them a little. What i would like to do is code a macro to copy the data (text only) of a whole worksheet into a blank worksheet in a different workbook, effectively allowing the user to "upload" the data to a master workbook.

The user will complete data entry into a daily template worksheet containing all formulae to obtain necessary daily data. I would like them to then be able to click a button that runs a macro copying the text data from the daily template to a monthly workbook. I have no problem recording this in a macro and the code for that (no doubt there is a more efficient way) is:

[Code] .....

The monthly workbook will contain 31 sheets (named 1,2,3...31) for the days, and each month will have it's own workbook (named Jan, Feb, Mar...Dec).

The key thing I need is using cells within the daily workbook to determine the month and day used by VBA for the filename and worksheet respectively.

Cell B5 contains the day (eg. 1) and C5 the month (e.g. Jan)

View 8 Replies View Related

VBA Code That Deletes All Data Except For Highest Value

Mar 27, 2014

What each piece of this code means and what it is doing? The more details the better

[Code] .....

View 1 Replies View Related

VBA Code To Write Data To Sheet?

Mar 31, 2014

See attached my workbook, I have a user form and when the OK button is pressed I need to get the data to write to the master sheet. Sheet1 behind.

Is there also a way I can re-populate all the data back to the userform using my search URN combo box?

Then the recorded can be altered and when update is pressed on the user form the record just updates on the master and not adds another record.

Waiting time sheet Basic.xlsm‎

View 1 Replies View Related

VBA Code Not Copy Data To Other Sheet

May 16, 2014

My vba code to copy row A110 of "Mod" worksheet to A9 in Table A of "Email Data" worksheet using some logic like - in Mod worksheet it searches for data i.e. "COUNT(DISTINCTM.TRANS_ID)" if found then it searches for "row selected" and it searches for data between the two . And then it pastes 34864 to A9 of Email Data worksheet . I have written code for this but it doesn't works.

Also i need vba code similarly for searching "CAP_ACTV_LN_SEQ" and then the values inside it to be pasted i.e. row 128 and row 129 in Mod worksheet to be pasted in table e in Email Data worksheet . Remember there are 2 rows but there can be any number of row anytime.

Also the vba code for "NULLPOINTEREXCEPTION'" is there in the sheet which is working fine. It can be used for your reference.

Button for Vba code to work is present in "Email Data" worksheet.

code for "COUNT(DISTINCTM.TRANS_ID)" given below also excel sheet attached : Worksheet .xlsm

View 6 Replies View Related

Looking For VBA Code To Only Show Columns With Data

Jun 9, 2014

I have a table with row headers (in column A) and column headers (in Row 1). Throughout the table there are 1's and blanks depending if the column is applicable to a particular row or not.

I want to write a VBA macro to do the following:

1. Do a Vlookup of column A for a particular user input text string.
2. Only show the row that matches the text string.
3. Only show the columns where there is a 1 associated with the selected row.

Then once that is done I need a separate macro to return the sheet to show all the rows and columns.

View 8 Replies View Related

VBA Code On Transferring Data To Different Worksheets?

Jun 29, 2014

What I am wanting to do is to transfer Names(Row B7:B160) to a different work sheet IF the Row next to it(C7:C160) is filled out with a time. I will be setting up a command button that once say 10 names have times next to it, this data then will transfer to another work sheet.

I have done below coding, however it will only transfer data in B7 and C7, if I try to add B7:B160 it comes back as Missmatch. Below is what I have done for the Command button.

Private Sub CommandButton1_Click()
Dim AdviserName As String, Time As Integer
Worksheets("Sheet1").Select

[Code]....

View 6 Replies View Related







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