I import a raw data file from our phone system into sheet 'imported', I then want to display the data on sheet 'formatted' how I have displayed it for the purposes of this example. I had a solution to this but the formatting out of the phone system has changed.So I need it to list the agents names, and then the corresponding time for each activity. The activities are listed on B3:I3.
View 2 Replieshow to merge data automatically in one sheet (master sheet) from specific sheet with different name sheet but same format?
Adding, deleting or editing should be automatic update to master sheet.
For example:
Sheet name:
Sheet 1: Salesman 1 - with data range A10:F310 (as his data file)
Sheet 2: Salesman 2 - with data range A10:F310 (as his data file)
Sheet 3: Salesman 3 - with data range A10:F310 (as his data file)
Sheet 4: Salesman 4 - with data range A10:F310 (as his data file)
Sheet 5: Salesman 5 - with data range A10:F310 (as his data file)
Sheet 6: Salesman 6 - with data range A10:F310 (as his data file)
Sheet 7: All Salesman - - with prepared data range A11:F2000 (as master data file/record for 6 salesman)
Column Name as a Header:
A10: Customer Number
B10: Customer Name
C10: Salesman ID
D10: Promotion Type
E10: Promo Item
F10: Free Item
The below piece of code is from a macro that creates a summary sheet based upon the name of the worksheet. My problem is as follows: I have another macro which copies the summary sheet and renames it in the format dd-mm-yy and I want to exclude this from the macro that constructs the summary sheet. How do I modify line 4 to capture the exclusion of all sheets with format ##-##-##? Note: The current line is my poor attempt at doing it.
If (A$ = "Template") Then Goto 10
If (A$ = "Create New Sheet") Then Goto 10
If (A$ = "Summary") Then Goto 10
If (A$ = Like "00-00-00") Then Goto 10
' Process the current sheet
Range("A" + Format(j)).FormulaR1C1 = "='" + A$ + "'!R4C15"
Range("B" + Format(j)).FormulaR1C1 = "='" + A$ + "'!R4C16"
Range("C" + Format(j)).FormulaR1C1 = ""
Range("D" + Format(j)).FormulaR1C1 = "='" + A$ + "'!R4C18"
Range("E" + Format(j)).FormulaR1C1 = "='" + A$ + "'!R4C19"
Range("F" + Format(j)).FormulaR1C1 = "='" + A$ + "'!R4C20"
Range("G" + Format(j)).FormulaR1C1 = "='" + A$ + "'!R4C21"
Range("H" + Format(j)).FormulaR1C1 = "='" + A$ + "'!R4C22"
j = j + 1
10 Next i
A
B
C
D
[Code]....
There are four sheets (labeled A,B,C)
Depending on the contents in cells in Columns A and B (which have 1000+ rows), I want to change the background color of a referenced cell elsewhere:
For each row (lets say from 2-1000)
1. If "A" cell is blank -- referenced cell (address in columns C & D) should be colorless -- (as in Row 2 --- cell Z5 in Sheet "C" should be colorless)
2. If "A" cell has text AND "B" cell is blank, reference cell should be colored BLUE -- (as in Row 3 --- Cell F3 in Sheet "C" should be BLUE)
3. If both "A" and "B" cells have text, reference cell should be colored YELLOW -- (as in Row 4 --- Cell C5 in Sheet "B" should be YELLOW)
While I could do this with a conditional format in every referenced cell, it would take too long to enter them.
I've got a workbook with a sheet named Data that has the information laid out in a single row. The data can't be fit onto a single sheet for printing / viewing so I've got a second sheet named Presentation that the data get's laid out to 2 rows so that it can be viewed / printed on a single page.
I'm handing this off to non-technical people so I need an easy way to populate the presentation sheet with the data. I've layed the data out using =Data!A1 however, the drag auto-fill doesn't properly fill it down since it's single row to double row.
Example of Data Sheet
Patient
Chart
[Code]....
create a fixture list from data i have in 2 other worksheets,Fixture Generator and Results.I was thinking the results page would be better to get the info from as it is already filtered (if 16 teams, 8 rows + 2 headers, if 9 teams, 5 rows + 2 headers etc.)The thing is, the filter has it listed in a column.
How would i get the info, conditionally, to enter into the fixtures sheet so that week 1 would be to the left, then week 2 to the right of that, up until week 4, then start a new row of fixtures until it is complete. Possibly having a gap between the first half and the second half ?
Using xl 2007 - The following code is working fine. except on completion my new sheet does not have the same nice formatting of the Original Sheet.. So I need to Incorporate the formatting of the new sheet to be the same as the Original Sheet.
Sub DumpAutoFilterToNewSheet1()
Dim wss As Worksheet
Dim wsd As Worksheet
Set wss = ActiveSheet
If ActiveSheet.FilterMode Then
Set wsd = Worksheets.Add
wss.AutoFilter.Range.Copy _
Destination:=wsd.Cells(1, 1)
Else
MsgBox "Nothing is currently Filtered" & vbNewLine _
& "Filter a Column and Try Again."
End If
End Sub
I was wondering if i could arrange some sets of data (on one sheet) in fixed format and create a new sheet for each set of data using VBA
I have some sets of data in sheet (similar to the sheet "Raw data" of the attached file)
first four rows contain costumer-1 details,
next few rows contain bill details of costumer-1,
again,
next four rows contain costumer-2 details,
next few rows contain bill details of costumer-2,
and
table goes on for around 10-20 costumers
Now, I want to arrange the given set of data of each costumer into a fixed format (as sheet "reminder pad" of the attached file) and create a new sheet for each costumer
Also I want to add reference no to each sheet (with automatic increment for each sheet)
I have been trying to produce a macro that will format data of varying lengths. Basically, I pull data from a database and export it into excel. I have a macro that will format the first few lines fine. Unfortunately, there are a few lines of info at the end of each report that need to be deleted. I cannot figure out how to make excel reach the end of my data a delete these two lines. If I build the macro on one report that has 100 lines and delete line 99 and 100, and then attempt to run the macro on a report that has 150 lines - lines 99 and 100 are still deleted.
Is there a way to delete the last couple of lines of data of a spreadsheet?
check and format some data with some code.
I have 100,000+ rows that look like this
Sheet1 *ABCDEFG1start_date start_time end_date end_timeTypeSOC Before SOC
After208/01/201218:36:2008/01/201218:40:00T**308/01/201218:45:1208/01/201218:50:44T**408/01/201218:52:1208/01/201219:50:46C3446508/01/201219:54:2708/01/201221:44:40C4681608/01/201221:48:4108/01/201222:02:36C8187708/01/201222:06:4608/01/201222:11:49C8787808/01/201222:15:5008/01/201223:02:00C87100909/01/201208:16:5109/01/201208:44:08T**1009/01/201208:45:1109/01/201208:49:55T**
I'm trying to merge all the "C" types in Column E into one row like this
Sheet1 *ABCDEFG14start_date start_time end_date end_timeTypeSOC Before SOC After1508/01/201218:36:2008/01/201218:40:00T**1608/01/201218:45:1208/01/201218:50:44T**1708/01/201218:52:1208/01/201223:02:00C341001809/01/201208:16:5109/01/201208:44:08T**1909/01/201208:45:1109/01/201208:49:55T**
So basically, I need to use the 'start date' and 'start time' from the first row and the 'start date' and 'start time' from the last row. Also use the first 'SOC before' and the last 'SOC' After'.
I have more data in column h - column z, so I need to delete the entire row of the additional "C" rows. Otherwise the data will be misaligned.
Macro or formula to reformat some data.
I have like data like this:
Column A = Date
Column B = Single Distance
Column C = Total Distance for that date
Sheet1 Â ABC1DateIndividual Distance Total distance 204/01/2011919304/01/20111019405/01/2011
212505/01/20111012606/01/2011926706/01/2011326806/01/20111026906/01/20112261006/01/2011226
It would be really useful if I could format it like this
Sheet1 Â FGHIJ1Total Individual 219910Â Â 312210Â Â 42631022
So basically, it puts all the information on one row for a particular date.
I have cells which contain dates in the typical Excel format. IE: Jan 3rd, 2000 in unformatted form shows up as 36528 in Excel in Cell A2. What I would like to do, in VB, is take the contents of that cell A2 (36528), and turn it into a date in the form of:
Year,Month,Day
(For instance, Jan 5th 2001 would be
2001,1,5
And then assign it to a variable
I'm looking for a macro to select data from a larger data set, and then put it in another worksheet. Basically, I want to select any cell in column A that contains "SO5" and move those to the column A of a separate worksheet. Then, for each cell that contains "SO5" would like to move to the right 5 columns and down 3 rows and select from that cell all the way to the end of the table. There may or may not be data in all of the table cells, so the selection should be based off of the column headers which will always have values. I would like to take that data and put it with the corresponding "SO5" in the new sheet.
Here's a sample:
MacroSample.xlsm
This is a simplified version, but I'm trying to get the data from sheet1 into the format in sheet2. Keep in mind that the number of columns and rows may change so it needs to be as dynamic as possible.
Iam just trying to format an raw data(which has morethan 40,000 rows) which is converted from notepad to excel.
The major problem the token number which is below the name and quantity i need to bring the number to left side of the row.
i think this has not given an clear picture so iam attaching an sample sheet
I have a worksheet (“A”) that is populated from a second sheet (“B”). The data on “A” changes as necessary using another Macro. Most of the data on sheet “B” is numeric and comes from various formulas and results in numbers with too may digits after the decimal point. I can format sheet “A” to turn 58.22222222 into 58.2 but when I run a Macro to clear the data I lose the formatting. I need to find a way to clear only the data and not the format, or find a way to add a mask on specific columns in the macro that copies and pastes. Here is the Macro to clear the data.
View 3 Replies View RelatedI am having a problem with a macro which has only started occuring on Excel 2000. It worked fine on Excel 1997. Basically, the Macro converts a text file (with comma delimiter) to an excel format. The problem I am having is with some of the date fields in the text file.
All dates in the Text file are in the format dd/mm/yyyy (i.e. 01/02/1990)
If I convert the Text file to Excel format manually through the wizard it correctly converts the date in Excel to dd/mm/yyyy (01/02/1990). However, If I run the Macro I have created, it converts the date to mm/dd/yyyy (02/01/1990).
This only seems to happen if the dd field is less than 12.
I attach a sample of the text file and Excel file (with incorrect dates) and also the Macro.
Basically I need the Macro to convert the dates in dd/mm/yyyy always....
I have attached a 97-2003 .xls file with data for multiple store locations on sheet 1, and the desired result on sheet 2. I am actually using excel 2007, but I dont think I need any special features that it provides.
I will try to explain the issue here without opening the attachment.
Here is an example of the Data on Sheet1
i need a macro that will compare the p'folios in sheet "Data to Sheet Workings, and then list the p;folios which is in sheet Data but not in workings in Sheet "New", so in the example below this would be ETSTSA ...
View 9 Replies View RelatedI need a Macro which pulls the data from different sheets of excel (which is not formatted properly) to Main Sheet. Also some of the columns will not have the same names, so macro should handle this exception as well.
View 8 Replies View RelatedI need to create a macro that will scan a spreadsheet for the number of sheets and then pull data from the same places on each sheet in order to create a summary sheet. Let me try to explain a little better.
The spreadsheet I'm working with has a separate sheet for each new deal our company makes. Each of these sheets is in the same format - we use a template and fill in the data based on that whenever a new deal emerges. The sheets contain basic info about the deal in the first few rows and columns, then some narratives with dates describing the progress of the deal, and then a list of issues and whether or not they have been resolved. The problem is, each of these sheets contains too much info for a quick, high-level overview with the bosses so they've asked me to create something that will pull the basic info, the most recent narrative, and any unresolved issues from each sheet. This way, each time there's an overview scheduled with the bosses, the macro can be run and it will create a new sheet with data from each sheet in the workbook.
I export raw data from my accounting system each month that I then format for use in another application that uses the data to produce customer statements. I have attached a file that shows the raw data in the first sheet and the formatted data in the second sheet. I recorded a macro while I did the process but I need to change the code to deal with dynamic data as the number of rows may be different every month.
Here are the steps I go through:
1. Clear the first 3 rows and the last 5 rows
2. Copy the totals in the last row of the data and paste them in the first row
3. Subtotal the different categories in row 2 and add a validity check
4. Add a new column A with a formula to add customer numbers on each line
=IF(ISERROR(FIND("00000",B5)),A4,B5
5. Copy the formulas and paste values over them
6. Filter the data to show blanks under the "Doc Date" column, delete all rows
7. Filter the data to show blanks under the "Type" column, delete all rows
8. Filter the data to show "Total:" under the "Apply No" column, delete all rows
9. Turn off filters, format all numbers in accounting format
10. Check the validation at the top to ensure no transactions were deleted
We have a system that normally dumps our data in the format of column K, with all of the data in one column and the same spacing you see below in red. The problem is when the system is slow, we have to manually dump it, and when we manually dump it, it comes out spread out from column A to column I. For the macro to work without bugging out on me, it has to be in the same format as column K, with exactly the same spacing.
I have tried text to columns while importing, amongst other things, but have not had any luck. So in a nutshell I need to be able to make column A thru I, look just like column K with exactly the same spacing. The data dumps are different every day.
I have a strange feeling I will not get a reply on this question, because it is so strange, or that I failed to articulate it correctly....
I'm trying to get the data from Receipt log (sheet 1) to automatically populate into the Print Receipt (sheet 2) and to automatically filter and go to the Activity by account (sheet 3). I am so new to this and totally lost.
See attached sample : Student fees.xlsm
How to create macros. I need to copy a certain group of cells from one sheet to another, and then do it for x number of times. I'm just using the record function and now i'm lost. Here is the code i currently have:
Sheets("Mapping QTR2").Select
Range("B137:D230").Select
Selection.Copy
Sheets("ExpDB_Q1").Select
[Code] ....
Also, column C is blank. i would like to get the values from another list in the same workbook, say "branch list". I would like to populate Column C with one specific branch for each "batch", if that makes any sense...
I am looking for a Macro which can transfer data from sheet 1 to the first available empty row on sheet 2.
For example, I have data in the following cells on sheet 1: B2, B4, B6, B8, and B10. I want to transfer this data to the first available empty row into cells A1, B1, C1, D1 and E1 of sheet 2. The cell number is ofcourse depending on the first available empty row and could thus be 2, 3, 4, 5 etc.
I also want to delete the data from the original cells in sheet 1 after the data has been transferred.
I have a couple of spreadsheets that requires me to copy data from Sheet 1 that matches certain criteria to Sheet 2.
I have tried using posted VB codes that have been written for other people but they dont seem to work (this is probably to do with me not actually knowing what I am doing, as I am not sure what I should be replacing and what I need to be deleting etc out of the pre-written code )
I need to be able to search on column e in Sheet 1 for anything that has just h written in the cell and then copy all the rows that match into Sheet 2. I also would like to ensure that when it is copied it doesnt go in row 2 but maybe 4 or 5 as I have quite allot of headings.
Transaction #Transaction DescriptionAccountDate (XX/XX/XX)DesciptionABCDEFG10/24/2015Cash Held(3404.00)3404.00 0.00 0.00 50.00 0.00 (50.00)10/25/20152Cash not Held0.00 (9707.00)9707.00 0.00 (60.00)60.00 0.00 10/26/2015Cash Held0.00 (7402.00)0.00 7402.00 0.00 0.00 0.00 10/27/20154Transfer to Bank 0.04 0.00 (50.04)0.00 50.00 0.00 0.00 10/28/20155Transfer of Cash0.00 4208.00 0.00 (4208.00)0.00 0.00 0.00
I am trying to make a macro that will turn transpose the above data into the below data.
DateDescriptionAccountAmount10/24/2015Cash HeldA-340410/24/2015Cash HeldB340410/24/2015Cash HeldE5010/24/2015Cash HeldG-5010/25/2015#2 Cash not HeldB-970710/25/2015#2 Cash not HeldC970710/25/2015#2 Cash not HeldE-6010/25/2015#2 Cash not HeldF6010/26/2015Cash HeldE-740210/26/2015Cash HeldG7402
Basically each non-zero number in the accounts (A through G) need to have their own row with the account name from their appropriate column and the date and description in their appropriate row. So as you can see each account A,B,E,and G get the amount -3404, 3404, 50, and -50 in their own row and get the appropriate descipion of 'Cash Held' and Date '10/24/2015'. Then the macro would move onto the next row and do the same thing.
Also if there is a way to tack in front of the description the transaction # (if there is one) ie. #2 Cash not Held. I really don't even know where to start.
I'm currently using some pretty extensive conditional formatting macros on a worksheet (this functionality is working fine when data is directly entered into the sheet). But when i paste data from another worksheet into my "formatted" sheet, the formatting does not take affect. is there any way to "refresh" a macro?
View 2 Replies View RelatedI have a time format in one excel sheet, I need a macro or a formula to change it into a specific format, this is to be able to import it to another software.
I have attached a file here you can see an example, this I manually, I want Row D and E into the format in A, I s it possible to make it in a macro or formula, manually it takes a long time.
File Type: csv TimeFormat.csv‎
I'm trying to format my email generated of excel sheet to have an HTML formal before they are sent out using MS Outlook.
I have googled, searched your forums and also tried to figure out a solution with the existing helps on the internet. I need help on how to format emails genarated out of my excel sheet. I know a bit of HTML syntax but a newbie to VBA. I'm guessing the formatting has to be done in the Emailbody text function in my code.
I'm also trying to understand the logic behind the code and also learning excel vba bit by bit at the minute.