I have a worksheet which has data in column A but I want to repeat it until their a change in data.
I.E say cell A1 has LTP BY001 and the other cells in column A are blank until lets say A30 when LTP BY002 appears. Am I able to run a macro or something that copies the data down until the data changes and then carries on with new data until again it changes and the end of the worksheet
I'm new to Macros and below is my requirement.I need to split my data into multiple rows based on count and the first row should have the value but the other rows should have a value as zero.
Input
Count Value
1 400
2 101
3 300
4 450
Output
Count Value
1 400
2 101
20
3 300
3 0
3 0
4 450
4 0
4 0
4 0
The following sub will look in the file ("FY09 SOF"), in column "A", search for the strings that begin with "2109", "3009", or ends in "-1", and copy the entire row. It will then paste these in the file ("FY09 PR Log Blank").
I have found that in the file ("FY09 SOF"), if things are filtered in any row, it will not copy those necessary items.
The data filter is on row 13 of each sheet. Is there a way of fixing this? (i.e. having the macros select "all" on the filter before copying the sheet? There are 60 sheets so a macros will be necessary.
Sub get_data()
Dim wb As Workbook, wbDest As Workbook
Dim ws As Worksheet, wsDest As Worksheet
Dim lngCalc As Long
Dim FoundCells As Range
Dim FoundCell As Range
Set wb = Workbooks("FY09 SOF")
Set wbDest = Workbooks("FY09 PR Log Blank")
Set wsDest = wbDest.Worksheets("Paste all here, then sort")
With Application
.ScreenUpdating = False
lngCalc = .Calculation
.Calculation = xlCalculationManual
End With
For Each ws In wb.Worksheets.............................
I want to copy some macro's from one workbook to another one. But I don't want to copy all macro's to the new workbook. For example: I have 20 macro's, and only want to copy 2 of them every time.
View 7 Replies View RelatedI am trying to create a macro that will take data inputed into a set range in sheet one and go to the appropriate sheet to paste in the data as a value in the right spot. The range on sheet one is A2:I2. The data in cell reference D2 in that range will be the same as the name of the sheet to go to where i want to paste the information as a value.
Then the data in cell reference E2 in that range will be a date, the macro will need to find that date on the sheet it has gone to and paste in the information in the row below it, first inserting a blank row to ensure that any data that has already been inputed is not pasted over but shifted down one row to ensure no data is lost. Then i will require the same information to be pasted into the last 2 sheets simply added a row and inserting the data before returning to sheet 1 and deleting the data entered ready for the next entry to be made.
The process should take information entered in Sheet 1, in this case to make a reservation on a trip, paste it into the relevant trip sheet (D2 cell reference will be a trip code) under the relevant trip date (E2 cell reference will be the date). Then also paste a copy on the second last sheet which will be the Invoice sheet and the last sheet which will be a sales tracking sheet.
Macro in Excel to search a column from workbook1 in another workbook2.
Example:
Workbook1> Sheet1>Column1 has many account#.
Workbook2> Sheet1, Sheet2, Sheet3 > Column1 also as account# & Column2 has Name of the the client's
What i want to do is for each account# on workbook1, search in all the worksheets in workbook2 for and copy the name of the client on column 2 and paste in workbook1 IF FOUND. if not found skip, leave it blank and continue to search for the next account#.
I'm desperate for a copy paste macros that will:
1) Copy H3:H143 (141 rows) from "Sheet 1" and place this on "Sheet 2" on D4:D144 (141 rows)
2) Copy I:3:I143 (141 rows) from "Sheet 1" and place this on "Sheet 2" on I4:I144 (141 rows)
So, in essence, it will copy every single column across on "Sheet 1", but will paste every 5 columns on "Sheet 2" .
I then need this to loop 33 times to continually copy and paste across columns.
I want to disable cut,copy,paste functionality (shortcut keys also) from excel.
View 1 Replies View RelatedI would like to place a command in a macro to have a workbook replicate itself WITHOUT replicating its macros. Is that possible? Is there a command? I am using Excel 2003.
View 9 Replies View RelatedI need a macros that can start of with this:
Sheet1
DEFGH50123DAMAGE6
ABCD7
ABCD8
ABCD9
ABC
10
ABC
11
AB
D12
ABCD13..........
In a workbook with several worksheets i would like (as part of a backup function) make a copy of one of the worksheets.
The code of the complete project is distributed over a module (module1) and several Microsoft excel objects ("thisWorkbook", but also in a number of the worksheets (e.g. in "sheet3 (Risk Management Plan)").
The backup copy of the worksheet (in this example also containing e.g. the Microsoft excel object "sheet6 (Backup of Risk Management Plan)" should be free of any macro code. this to avoid unintended problems by inexperienced users, but also to keep the file size limited.
Probably it would be possible to copy the worksheet without of copying also the code. Or it would be possible to strip the code only from this particular worksheet without influencing any other code, forms, ... . Or, perhaps, there would even a totally different approach.
I use Windows XP pro Sp2, and Excel 2000 Sp3.
In my program I have data coming from an outside workbook. The amount of data coming into my program changes (Additional Rows) could be more or less. What I want is for the macro to recogize the change in data size and copy the new amount automatically. ie Change copy range. I would post the program but it is proprietary.
View 8 Replies View RelatedUsing a macro, how do I tell Excel to:
1. Copy the xth row and paste it before the yth row
2. Copy the mth column and paste it before the nth column
3. Copy the cell a,b and paste it in he position c,d
where x,y,m,n,a,b,c,d are variables, the value of which the user inputs, say in cells A1, A2, A3, A4, A5, A6, A7 and A8 respectively.
In other words, I need to read numbers m and n which are the values of cells A1 and A2, then I need to go to column number m (for instance, if m=4, I go to column D), copy the whole row, go to column number n (for instance, if m=6, I go to column F), and insert the copied column.
Also wondering if VBA provides any way to quickly horizontally and vertially flip copied tables while pasting. While I am aware of the transpose function which swaps rows with columns, I am looking for a way to reverse the order of either the rows or the columns as per my need, without having to copy-paste row-by-row or column-by-column.
I need to copy my worskheet multiple times to create about 10 of the same worksheets within a workbook. I know if you click in the very most top left hand corner of the worksheet, then click copy, go to the new worksheet and click paste, it copies the whole page, but it does not copy over the VBA Code.
Also, for some reason, it does copy over my macro buttons, but does not put some of them in the right spot?
i have created a form in excel sheet1 (not a VBA form) and there are 10 cells with headings in the form which need to fill the data but i want to copy certain cells (suppose 6 different cells) from my form which is in sheet1 and paste to another sheet2 to certain columns horizontally.
View 1 Replies View RelatedI have an Excel 2003 program that contains macros. One of the macros hides certain command bars and disables the worksheet menu bar. On close the opposite is true. The problem is, if a user uses the disable macros when opening then the worksheet menu bar and other command bars are still available. I would like to hide all of the data sheets and display another sheet that would normally be hidden displaying a message that the macros have to be enabled for the program to work correctly if disable macros is chosen. When the enable macros are used I would like the Error page to be hidden.
View 6 Replies View RelatedI have a spreadsheet where I'm copying data from one tab to another and also copying values down in certain columns. I have a "chunk" of data already on the tab I'm working on and want to copy an additional "chunk" below it. I want to fill a whole column with the same data but only in my second "chunk". So in this instance...it's an account number and all of the rows in the first chunk have account number 42243 and in the second chunk, I want it to be 78300. The account numbers will always be the same but the number of rows will not be. So for the first chunk, I was able to use the following code...
Code:
'Copies account number 42243 into column C for all lines
MyCount = Application.CountA(Range("a:A"))
Range("C2", "C" & MyCount).Value = 42243
Range("C1").Select
And right now, I have the following in for the second chunk. But obviously what this is doing is putting 78300 in the account field for the entire column, not just the second part.
Code:
'Copies account number 78300 into column C for all lines
MyCount = Application.CountA(Range("A:A"))
Range("C2", "C" & MyCount).Value = 78300
Range("C1").Select
I am importing data from access into a worksheet, and I see the data on my screen in the cells. On Worksheet_Activate, I then want to import the data and then call 2 macros which look at the cells I have imported, and if they are not empty, then manipulate or use the data in another cell. These subs all work as they should, as I can test them by hitting F8 or by running the import sub, then the subs which manipulate that data seperately. When I run them all on activate, the vba works as if the cells are empty, not doing the data manipulation, thought I see they are not empty with my own eyes. Is there some kind of update call I must make? I have tried to add Application.Calculation = xlCalculationAutomatic
View 3 Replies View RelatedI'm working with a data logging system to collect data on a circuit. The logging software allows you to export the data collected to Excel in real time using DDE, and having talked to the logger company I managed to obtain a macro to get the data into excel (I'll put it at the end of the post). This works fine, and I can now press a button and Excel updates itself with the new data every second. What I want to do is quite complicated and I have no idea where to start:
1. The data obtained via the macro only displays in static cells, that is when the new data comes in it overwrites the old one. I want Excel to take a copy of each new bit of data as it comes in, and copy and paste it into a column on the same sheet so that it can be stored. I then want it to move down a row and take the next reading and so on, so that each time a new reading comes in, Excel takes the value and puts it in the next row down in a set column
2. Having a time elapsed measure would also be very useful, so that it records the time after starting the other macro in a column next to the ones collecting the data as described in 1. In this case, each new row would be 1 second further on. The actual time is not massively important, just the 'relative' time since the data collection started.
3. Once the data is copied into the columns to be stored (1.) I then want to calculate two sets of moving averages and place those in columns next to the data. My current plan is one average of 30 samples and another of 5.
4. Once I have the moving averages I can then get Excel to plot some graphs to show the progression of the two values
5. Since my project is revolving around detecting a short circuit in a coil of wire, it is looking for drops in resistance. I want Excel to be able to monitor the two calculated moving averages and if the difference between them exceeds a set threshold for more than 5 samples then some alarm is triggered (for now just a cell changing colour to red or a pop-up box or something).
Unsure how to start this off, any help would be great.
I have a spreadsheet with raw data in it, and am looking for a way to sort the raw data into separate sheets within excel using a Macro / VBA.
Here are the different columns of data I would like to copy into a different sheet.
Man Ord No.
Product No.
Operation Description
There is a Work Centre column I would like to use this column as a way to select the different rows of data.
So if there are five different rows of data for ACT#01 in the Work Centre column I would like it to copy the rows of data, delete all the columns that are not needed that’s all of them except for the above three, then add three new columns below.
Add a column called “Run Time
Add a column called “Resrce Name”
Add a column called “Available”
Work Centre will become the name of the sheet in Excel
The titles of the columns will have to be added in also, via the macro.
See the example excel file that I have started to make by hand, this is just one part of the process that am looking are automating there are other parts as well but just getting started for now.
i have a user form & I would like to run macros when a texbox gets data. If there a "P" I want it to do nothing but if theres an "F" I would like the user Form to unload
View 3 Replies View RelatedI have 2 different Clear all data Macros
I will post the 2 different Macros now, then explain a slight modification needed if Possible;
Sub clear()
Dim lastrow As Long
lastrow = Cells(Rows.Count, 1).End(xlUp).Row
If lastrow < 3 Then
MsgBox "Nothing to clear!"
Exit Sub
End If
Range("A3:A" & Rows.Count).EntireRow.ClearContents
End Sub
Clear Macro 2
Sub clearalldata()
Dim lastrow As Long
lastrow = Cells(Rows.Count, 1).End(xlUp).Row
If lastrow < 5 Then
MsgBox "Nothing to clear!"
Exit Sub
End If
Range("A5:A" & Rows.Count).EntireRow.ClearContents
End Sub
Basically can the following be added as a fail safe.
I assign a button to each macro (which are on different sheets in workbook).
If I click on say the assigned macro button called "Clear All" for 1 of the macros, can a pop up box appear?
And say, "are you sure you want to proceed" or it could say "Clear All" and have a "yes" and "No" check box?
Quite simply, I've accidentally clicked on these a few times
If this was included in the macro, then I wouldn't loose all my data accidentally
In the attached file (I'm sorry it is zipped, it was just too big), I gave you a snippet of my database. The actual one has 9000+ records. Over in Y4, you'll see the Sum of all the amounts (column X). Run the first macro, that is, Copy Dups, and you'll see it splits the item up by the numbers in column N. This works perfectly (or so it appears.) Then, run the Summary macro (the only other macro in there) and you will see that it pulls the Sums of each spreadsheet and places them on one nice overview page. The summary page. The only problem is, the total of the subtotals on the summary page do not match the total on the main (unadultered) page. I hope i am explaining this right. Somewhere in the macros, I am losing data from my spreadsheets so that the summary page is not equal to the un-macro'd data.
/scratches head
I have a user that keeps a maintenance log in an Excel worksheet and sends an updated copy once a week to a board member. Two weeks ago, the board member started complaining that he was prompted to enable/disable macros on opening and became worried when my user stated that no macros were used in the book. He is now concerned that we have sent him a virus.
I know the file is clean because I've scanned it, and when I look at the file in VB, there are no modules or classes present just the Sheets 1-3 and the ThisWorkbook file. None of these objects have any code in them. My user does have some macros in PERSONAL.XLS but they are not used in the workbook in question.
No one else gets the prompt for enabling/disabling macros. Even if I set my security to prompt for any macros, I get no message. I'm convinced that there must be some setting in his Excel that is causing this individual to get this message. Is there anything else other than a macro that would cause this?
I want to use data validation so that if macros are not enabled, data validation in a particular cell will not allow any entry. Maybe a helper cell that is TRUE if macros have been enabled? How is this done?
View 2 Replies View RelatedAs you can see, I have 2 worksheets. I want that all entries in columns B,D,E and F automaticly fill in in multiple tables in sheet2. Also, if possible that macro creates tables (for example: if I have 100 rows, I want to extract 100 separetly values from column B,D,E and F and to enable automatic creation for new table - for 101 entry )
here is the dropbox link of the file:
HTML Code:Â
[URL]
So, I want when I click on the button I want to automaticly import all mentioned values into sheet 2 (I have marked with RED where to put values from which column). In this example, I expect, when I clik on Print all tables button, to print 7 tables in sheet2. In sheet2 I have 2 tables as you can see.
I have a large excel file with the following columns:
unique ID | Date | Time | D or N | Open | High | Low | Close | Volume
I would like to figure out how to create a table showing:
Date | Highest High of the Day | Time the High occurred | Lowest Low of Day | Time Low Occurred
I think this can be done in a macro where I sort first by the Date column then By the High column but I can't figure out how to get the rest.
I am relatively new to macros and trying to understand how to modify a certain macro that I recorded. Here is what I'm trying to do...every month I have a file with a bunch of data. I need to take that file, filter it, and pull certain data from it into separate tabs within the same workbook. The number of rows of data will change from month to month however I'd like to have a macro that will capture however many lines of data. I have several rows/columns of info I need to copy between tabs but I recorded a macro with just one filter and one row of data for now just to try and understand what I need to do. I have copied it below.
Sub Intersegment()
'
' Intersegment Macro
'
'
ActiveSheet.Range("$A$1:$AF$727").AutoFilter Field:=14, Criteria1:= _
"Intersegment"
ActiveWindow.ScrollColumn = 2
[Code] ........
1- how can i open an xls file from a macros to get some data from it?
2- how can i set a wait function, till i press a special button before macros will be able to continue.
e.g.:
[macros.xla]
transpose raw data to column macros ...
View 9 Replies View Related