VBA Macro To Process Data After Paste With Text To Columns..
Mar 24, 2009
My problem is that data from previous applications are "bleeding" into new applications while running my macro.
I am utilizing the macro below to paste an application and then to convert the application to upper case in addition to some additional formating changes. After performing the macro I save the newly created document to a different folder. With new applications I repeat the above steps.
I tried to incorporate the clearing of the clipboard in my macro using: Edit=>office clipboard=>clear all. The keys strokes during the " record macro" process do not seem to record in the macro.
I am using Excel 2003, SP2
Sub Process_Application()
'
' Process_Application Macro
' Macro recorded 3/23/2009
'
'
ActiveSheet.Paste
Columns("A:A").Select
View 9 Replies
ADVERTISEMENT
Jul 26, 2009
I have two macros one of which imports the data and the other processes the data. These macros were created at different times and need to be joined into a single macro that will combine their operations – with slight modification to the importing macro. Let me describe what each of the macros does:
PROCESS macro:
Cycles through the CONTROL CELL on the “1” tab using the VALUES TO TEST. For each value to test it copies the values form the output tabs and pastes them into the A+B tab. Then it removes the duplicate rows there.
Option Explicit
'======================================
Sub PROCESS() 'This subroutine is called when clicking on the first button
Dim i As Long, j As Long, k As Long 'Those are the variables that will browse through the whole sheets
Dim l As Integer 'this will be used to browse through the values to test
Dim TheSearch(1 To 2) As Object 'This is an array of objects that will be used for the search
Dim TheRange As Range 'This will be used to flag the range of duplicates in the output
'Searching for the Text string 'VALUES To TEST' in the sheet "1" in order to know where the array of values to be tested start
Set TheSearch(1) = Range(Cells(1, 1), Cells(ActiveSheet.UsedRange.Rows.Count + 10, ActiveSheet.UsedRange.Columns.Count)).Find(What:="VALUES TO TEST", LookIn:=xlValues, Lookat:=xlWhole)
View 9 Replies
View Related
Nov 21, 2011
I have a macro that would check data in Column A and validate if a particular number is repeating, then for that number go to column B, Take the Values from there go to a new sheet and paste the values in a row.
CurrencyDateRef CodeIDAccountAmountDes.USD07152011XDVU4315210.4200.C5001.USD-18,606,772.190Distr Payable 07152011USD07152011XDVU4315210.4200.C5002.USD-111,131.450Distr Payable 07152011USD07152011XDVU4315420.4240.C5001.USD18,606,772.190Distr Payable 07152011USD07152011XDVU4315420.4240.C5002.USD111,131.450Distr Payable 07152011
I get the data in the below format
CurrencyDateRef CodeIDAccountAccountAmountDes.USD07152011XDVU4315210.4200.C5001.USD420.4240.C5001.USD-18,606,772.190Distr Payable 07152011USD07152011XDVU4315210.4200.C5002.USD420.4240.C5002.USD-111,131.450Distr Payable 07152011
I need to the macro to get the data not from the second cell.
Below is my macro
Sub test()
Dim idRange As Range, c As Range
Dim uniqueID As String
Dim destSht As Worksheet, sourceSheet As Worksheet
Dim r As Long
Dim i As Integer
Dim map As Object, key, item
[code]....
View 2 Replies
View Related
Apr 1, 2014
I have a report that runs, and it shows users that are in different groups. Within each group are primary users, and sometimes secondary users. Attached is a spreadsheet showing an example of the report in "A7:A52"
What I want to do is have a macro scan column A, and if a group is found, create what I have made in the rest of the spreadsheet (CD6 and below). I manually filled in the data for group 1, and would like it to continue off to the right in the same format.
There are some areas that dont have groups, such as there might be group 1, group 2, and then nothing till group 10 (as shown in the example)
View 12 Replies
View Related
Aug 1, 2012
I am writing a macro that will allow me to copy all the data in a set range (A2 and below) after checking that B1 contains the text "Year_id".
Right now, I am able to copy all the information, and paste it onto "Sheet 4". I am unable to code for the part where the macro would check for the text. The code that I have (for copying-pasting the date) is below.
Any code that would check the information in B1 into this macro code below:
Code:
Sub Copy_Allinfo()
Dim Sht As Worksheet
Dim Rng As Range
For Each Sht In Sheets
If Sht.Name = "Sheet4" Then
[Code] ........
View 7 Replies
View Related
Nov 18, 2013
I have a workbook with four tabs or four sheets.
Tabs 1, 2 and 3 have a column of data (Column A) on each sheet. They all start from the same cell. Each cell of data are just numbers. The column (an array) of data will not have blanks between.
But, they are not the same number of data. They vary.
Meaning, Tab 1 may have 15 numbers (A1 to A15), Tab 2 may have 20 numbers (A1 to A20) and Tab 3 could have 5 numbers (A1 to A5). Each iteration of the workbook may have different number of data in the A column on these tabs.
Now on Tab 4, I want combine the data from all three tabs into one column (in column A).
So, Tab 4 has a column A with data from Tab 1 copy and pasted to (A1 to A15) as values, then (A16 to A35) have Tab 2 data copy pasted as values and (A36 to A40) have Tab 3 data copy pasted as values.
Basically, the macro on Tab 4 has to count the number of rows on each Tab that are populated with data values and figure out to copy all the data on Column A from each three tabs and paste the data value into the Tab 4 in one column of data (in values).
View 6 Replies
View Related
Dec 5, 2008
I have mananged to find and tweak the following code in order to serve my copy / paste to master sheet requirements.
At the bottom of the code I have added a delete rows based on column contents routine although I'm not sure if it is actually the right one to use.
I have included it in the same module in order to tidy up the whole process and have it all operate under one click.
As stated on the sheet in this exmple, The paste could consist of any number of sheets although the range is always the same on each sheet. (only amounting to around 20 rows that we are dealing with so no reams of data with odd empty rows that would take an age to find otherwise).
The data can look like it does on Sheet 2 here and also could look like it does on sheet 3. (this data is coming from a sales rep's order sheet)
From the routine in module 1, I just don't know where an "add an empty row" or delete all empty rows bar 1" code would need to be inserted in order to keep the process going until all sheets are copied.
I'm aware that with the delete code at the bottom, the whole copy process is completing before the delete process then does it's bit so think I'm on the right track in thinking that the delete all empty rows but leave one" requirements needs to be further up the code but I just don't how to get the result I need or where that code should break into the routine.
The only other way I can think to acheive the result is to allow the range to increase by 1 row on the form but somehow make sure that the last row in the range contains a character in column B to "trick/force" the delete routine to leave that row in. That would be do-able but the trouble is, how do you get a value into a cell that the delete proces would treat as data but not be visible so keeping my spacer line tidy? I've tried just putting a space in the last row of the range in column B but the row still gets deleted.
View 7 Replies
View Related
Apr 17, 2014
I am trying to get a macro to run in excel that takes a simple text to columns command in one line of data and runs the command on a loop through however many rows of data there happen to be.
I've attached two screenshots - one with what I've got now (Before.jpg) and what I'd like to have after the macro runs (After.jpg). The code below is what I used to get the first text to column breakout, which I can hopefully run on a loop to breakout anything in the DEPT column that contains a "/". It can ignore the rows that only have one department to begin with.
Selection.TextToColumns Destination:=Range("K2"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="/", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
Eventually I'll want to create another macro that transposes the breakout text back into the DEPT column and populates it with the corresponding data, but I figure I'll take things one step at a time.
Attached Images
Before.jpg‎
ter.jpg‎
View 10 Replies
View Related
Mar 1, 2010
i have written a macro to parse data in to four columns using the text to columns fixed width option, but unfortunately the data i get changes its spacing and configuration every day, meaning that the fixedwidth columns dont separate the data correctly. below is how it looks some of the time.
46632hac5 Jpmcc 2007-ld12 a5 19,340,005 315
0738qac5 bscms 2007-pw17 a5 23,142,005 265
61746wcz5 msdwc 2000-prin a5 3,600,005 305
32108hp75 bacm 2007-2 a5 2,000,005 465
46630edf5 lbubs 2006-c1 a5 1,000,005 285...............
View 9 Replies
View Related
Sep 10, 2009
Hi I have a process in which I have to create a selection without bias. There are 9 categories in this selection process. The 1st and 2nd category already have code and they work differently from the rest. For the 3rd through the 8th categories I have to choose one from the 3rd category, then one from the 4th, etc. After the 8th category I start back at the 3rd and go to the 4th, ect. It ends when cell A1 reaches 30%.
Its a little difficult to explain but the process is simple enough to understand once you see it. I have attached an example of the process. I have also started the code and left explanations within the code.
View 14 Replies
View Related
Jan 29, 2014
I would like to implement specific cell ranges from two specific worksheets each within 33 workbooks (which all have several tabs) into a summary page in a separate workbook.
The cell ranges are going across my spreadsheet in rows and I would like for them to transpose into a columns depending on the data which I have separated by catergory on the summary page. They are all on the same location in each workbook which is separated by country. The cell ranges are E26:P37 and I would like to transpose them and have them put below eachother without overwriting for my format on the summary page, how I can put this together in a macro?
View 1 Replies
View Related
Jun 30, 2006
I need to know if it's possible to log the process of a series of macros
either to txt file or to a Sheet, (either way won't matter but txt file would be perfect)
For example:
Sub MyMacro1 ()
code
End Sub
Sub MyMacro2 ()
code
End Sub
If this process was logged, then the log file would look something like:
Date Time : Sub MyMacro1
Date Time : End Sub
Date Time : Sub MyMacro2
Date Time : End Sub
But it would be a in text just like a log file.
Is this possible with Excel ?
The reason is because my Workbook is huge and I am forever tweaking and adjusting or fixing and I require reference points.
View 4 Replies
View Related
Aug 30, 2009
I wanted to know If there a way to hide the process of executing macrob (vba)?
I have a long macro that runs on a file and I want to hide the process. I know there is an option to it, I just don't know it.
View 5 Replies
View Related
Jun 19, 2009
I have the following macro. I need a loop that runs untill there is no more data. The loop should increment at each pass the following 2 Ranges and 1 Rows by 1. What is the VB code that will accomplish this for Excel 2003?
Range("A3:V3").Select
Selection.Cut
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 10
Range("W2").Select
ActiveSheet.Paste
ActiveWindow.LargeScroll ToRight:=-1
Rows("3:3").Select
Selection.Delete Shift:=xlUp
View 4 Replies
View Related
Oct 25, 2008
As i have a pretty "complex" macro, which takes around 20-30 minutes to do (honestly)....
I was wondering if there was a way to create a progress bar just showing me how many % it has done so for?
Or even tell me which sub routine it is up to? (but % would probably be better)
And preferably a pop-up box one?.. not one on the status bar because i have stuff updated on the status bar already...
View 14 Replies
View Related
Oct 12, 2007
I am trying to speed up this macro, ive already tried turning screenupdating and calculation off, but it still takes forever, and I dont understand why.
The code is basically searching for a given string in column E. There are about 9000 lines to my sheet, which shouldnt take more than a 30 seconds to a minute to complete.
Private Sub CommandButton1_Click()
Application.Calculation = xlCalculationManual
For MY_ROWS = Range("E12000").End(xlUp).Row To 1 Step -1
If Range("E" & MY_ROWS).Value UserForm1.ComboBox1.Value Then
Range("A" & MY_ROWS & ":E" & MY_ROWS).Delete (xlUp)
End If
Next MY_ROWS
Also anyone know how to add another condition to make the macro stop the delete process at lets say Range("a1:e8")?
View 9 Replies
View Related
Dec 15, 2006
I download some data from a commercial real estate site about properties and their owners and process it in Excel. Out of 1,000 records, maybe 20 or so will have the data end up in the wrong fields. This is an artifact of the data source the commercial site uses.
Anyway, what I need to do is to get the data back in the right fields. So, I sort the data to pull together at the top of the sheet all the records with data where it's not supposed to be. So far, so good.
Now from one data download to another the number of records which end up in the sort will be different. And, here's the problem.
I try to record a macro mimicking my selection of the range of the data that needs to be moved. Fine, no problem. And, on the same dataset it works like a charm. But as soon as I put a different dataset into the spreadsheet with a different number of records that need to be corrected the macro fails.
Apparently, this is because the macro has been defined with a certain range of cells selected in the first data set and this same range is used for subsequent datasets with different numbers of errant records.
Basically, what I'm trying to record in a macro is the Shift/Control End and Shift/control arrow commands. But they don't record as such.
View 9 Replies
View Related
Feb 4, 2010
I'm using a pre-made spreadsheet from my stock broker (Interactive Brokers) that retrieves and displays real-time quotes, and allows one to retrieve historical stock data, among other things. I've created a little macro within it to try and automate some common tasks I do everyday - basically I want to retrieve a year of daily stock quotes for "stock 1" from IB's servers, then have the macro wait for the retrieval to be done and written to the spreadsheet (takes anywhere between 10 and 30 secs). After that's all done, then I want to do the same for "stock 2". Then when that's done, the macro proceeds to go ahead and do some calcs on stock1 and stock2. The problem is after my stock1 data request, the macro just keep on trucking through to the next commands while stock1's data retrival is still going on.
so things are getting all balled up. How can I get my macro to wait until stock1's data retrieval is all done?
View 14 Replies
View Related
Jan 27, 2008
I am trying to write a macro that will run another macro (already written) through all the sheets/tabs in a workbook one sheet at a time and will stop once it reaches the last sheet/ tab.
Can you assist me in writing this macro.
View 9 Replies
View Related
Sep 12, 2007
I have a collection of about 200 .xls files in a folder, each contain the same macro. I now need to run this macro in all 200 files and save them but to do this manually will take hours. is there anyway to set excel to batch process a macro in multiple files?? Or maybe i can write a new macro to open up each workbook, run, then save and exit??
View 3 Replies
View Related
Jun 11, 2014
I am running a auto filter macro to I need to change it to only copy column A:B and past in column C:D in the sheet called "Diary". How to change my current macro to only select column A:B and paste it. It is currently pasting the entire row.
Here is the part of my code:
[Code] .....
View 1 Replies
View Related
Aug 24, 2012
I have a sheet that has columns from A to BS, and the column headers start in Row two.
I need to 1st filter the data by Column E where the data in Column E should not contain a particular Value, like"Sleeve".
Then it needs to copy the Columns A,D,G, BN, BO, BP,BP,BR & BS.
And it needs to delete Rows 3 & 4 from the current sheet, before pasting it in a new sheet.
And at the bottom of the sheet it needs to give me a count of the rows and the month end date for each month.
View 1 Replies
View Related
May 1, 2013
I'm trying to get a macro together that will take a set of workbooks that I've merged (using Ron de Bruin's RDBMerge add-in) and transpose all columns from B to HB into rows. Now, I know that each spreadsheet is 210 columns and 244 rows large and they are concatenated on one another. Attached is a brief example of what I am trying to go from and what I am trying to get to.
View 1 Replies
View Related
Jul 27, 2009
I have a column "g" with this conditional formatting:- =A2<>A3 Format Bottom Border.
However I will pass this workbook onto someone else who will fill in the text in column "g". They will use copy/paste text from other cells or columns even other workbooks that will not have the conditional formatting.
I have used Cells > Projection > Locked unchecked then used Tools > Protection > Protect Sheet and checked all. There does not seem to be a way to unlock the cell but protect Conditional formatting. Each time I copy and paste from other non formatted cells it wipes out my formatting.
View 4 Replies
View Related
Sep 7, 2012
Im trying to get my macro to open allow me to select multiple workbooks, then have each workbook open up and copy A2:N2 and down and paste these into my active workbook, under each other, ie on the next available row (basically combining all the workbook data together to create a big list.
The below code works to open a single workbook, copy the data and paste it in at the next available row, but I have to keep running the macro for each workbook whos data I need to import!
How can I modify this code so I can select more than one file? I need something like for each wb .....at the end next wb...until the macro has done the below for all selected workbooks.
VB:
Sub openandcopy()
Dim wb As Workbook
Dim ws As Worksheet
Set ws = ActiveSheet
Set wb = Workbooks.Open(Application.GetOpenFilename)
[Code] .....
View 4 Replies
View Related
Jul 7, 2009
i'm working on floyd algorithm macro for a project,
problem is, the spreadsheet matrix is huge, it's a 1043 x 1043 spreadsheet
And this is taking wayyy too long, i've been waiting for almost 2 days already..
here's the file :
http://ifile.it/6v2j39f
(You start the macro using "run" command in "input" sheet)
So is there any way to speed it up?
Or if anyone got a supercomputer, could you help me process this? :p
I'm thinking about changing the 10^5 value into infinite value, but i can't find a way to display infinite value..
And here's some reference for Floyd's algorithm,
http://ifile.it/12p5zle
I have tried to split the algorithm to make it process one iteration at a time, but i've been waiting like 2 hours with no result,
and that's for 1 iteration, can't imagine how long it takes for 1043 iterations
You can do this by deleting the.
View 14 Replies
View Related
Apr 2, 2007
I need to get this macro to process the cells for every worksheet in a book rather than just the active one
Public Sub test()
Dim Lr As Long, i As Long, x As Range, _
v1 As String, v2 As String, v3 As String
Set x = ActiveSheet.Cells.Find("*", searchdirection:=xlPrevious)
If x Is Nothing Then Exit Sub
Lr = x.Row
Application.ScreenUpdating = False
For i = Lr To 1 Step -1
v1 = Cells(i, 2)
v2 = Mid(Cells(i, 3), 1, 1)
v3 = Cells(i, 4)
If v1 "OP00" Or v2 "L" Or v3 "CC" Then Cells(i, 1).EntireRow.Delete
Next
Application.ScreenUpdating = True
End Sub
View 9 Replies
View Related
Aug 1, 2014
I have a macro that works just find on my local machine. but it need to run from a network drive as several different people may need to access it.
A brief description of the macro.
The macro looks in an directory called unprocessed for any file named "*.csv" if the files are found it opens the file in excel and then copies data from that workbook to my workbook. after this the .csv file is closed (unchanged) and then it is moved to the processed directory.
Sub files()
Dim directory As String, filename As String, sheet As Worksheet, i As Integer, j As Integer
Dim filetype As String
Application.ScreenUpdating = False
[Code].....
View 7 Replies
View Related
Feb 12, 2014
I'm currently trying to automate the process of creating Data Validation.
PFA.
I have Grades and Names List.
" GradesList " Range has values "One, Two, Three, Four "
I'm trying to map Names List to GradesList using INDIRECT().
Debug the function DataValidation() to understand the issue better.
View 7 Replies
View Related
May 18, 2014
how to process this non-numerical. I tried to use formulas but it wasn't fruitful. refer the table and.
Name
English
G
Maths
G
Science
G
History
G
Aggregate
Result
Achievement
[Code] ......
****The aggregate derived from the grade of the subjects , where each grade will be given a pointer and the average pointer for all four subjects will be considered as aggregate :
Pointers for grades
A =1
B=2
C=3
D=4
E=5
This is how the aggregate for Alex should be counted:
English A = 1
Maths C = 3
Science B = 2
History A = 1
*****aggregate = (1+3+2+1)/4 =1.75
The result column be showing Pass/Fail. If the student fail one of the four subjects he is considered as Fail.
**Grade D and E =>Fail
The Achievement Column should be indicating the numbers of As, Bs and Cs
for example: O6= A(2),B(1),C(1)
View 7 Replies
View Related
