I am comfortable with getting a macro to copy & paste where I need it to, however how to get the macro to recognise when a certain value has stopped.
For example in Column B on "DataDrop" are all the divisions that exist in my company i.e East Anglia, Essex, London etc. There is an empty row between each division.
I have a spread sheet with bunch of rows and columns. Columns goes all the way from A ... BB and rows from 1 to 40,000
The main focus is column Y and AC
If there is a value of FALSE found in column Y then macro should go to previous row and copy the value that contains in column AC and then move down to the next row, , where the value FALSE was found and that's in column Y and paste that value in column AC the NUMERIC VALUE
Below is the data ...
Have a workbook with approximately 25 worksheets in it.
I want to copy/paste/value each worksheet and save it as it's own workbook.
My code doesn't work, it hangs up on ws.copy every single time, on Excel 2007 and prior versions.
Sub SaveWS()
Dim wb As Workbook
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Copy
Set wb = ActiveWorkbook
wb.SaveAs "t:dir1expenses" & ws.Name & ".xls"
wb.Close False
Next ws
End Sub
I have a current macro that saves each sheet as their own file. The first 10-15 sheets are not really necessary, so I typically delete them once the macro has run. I am looking to add two things to my current code:
1. Add a tab called Start. Make the macro look for 'Start' tab and then just save each sheet to the right of that as its own file.
2. Save each sheet in the macro as just a copy/paste value. They current have a lot of links and it makes each file close to 3MB. That makes it incredibily difficult to paste all in one email.
Here is the current code:
Sub CreateWorkbooks()
'Creates an individual workbook for each worksheet in the active workbook.
Dim wbDest As Workbook
Dim wbSource As Workbook
Dim sht As Object
Dim strSavePath As String
On Error GoTo ErrorHandler
[Code] ..........
I have to worksheets, the first is filled out and at the end Ive place a button that I would like to open the second sheet and fill in cells that have duplicate info from the first, so just copy & paste but kind of automated for speed.
I have only got as far as opening the second document with the macro, after that im stuck!
The Info I would like to be copied across from the "service Diary" to the "Service Report" would be As follows:
*CUSTOMER
*CONTACT
*MACHINE TYPE
*SERIAL NUMBER
*QUOTE NUMBER
*ORDER NUMBER
What the Macro has to do is this.
It takes an specific word from a certain number of files that you can choose from an add file window.
Then when you choose those files the macro starts to run every file and look for an specific word, in this case is "ROFO March 2013".
When it finds it, The macro creates a new workbook( You create the name of that workbook y the first place of the macro, when you choose the files that you want to look for that word).
So, When the macro finds the word ROFO March 2013, it copys all the columns that has that name, and keeps looking until the file ends.
And paste all that info in the new file, A consolidate File.
I have change all the names of variables of the macro so you can understand it.
Code:
Sub copy_data(ByVal Origin_File_route As String, ByVal destiny file As String)
Dim Origin_File As String, RsBusq As Range, ROFO As String
Dim Rango As Range, Fila As Long, lastrow as Long
origin_file = Dir(origin_file_route)
[Code] .......
I have a table like this:
1/2/14
2345
1/6/14
34665
1/7/14
5473457
For Column A-I would like to create a macro that will copy the info from cell A1 and paste in in A2. This will continue all the way to the last row containing info which will vary. So after the first copy/paste, it well go to A3 and copy paste onto A4, A5 will copy and paste onto A6 etc.
For Column B- The same only it will copy the contents of B2 and paste it on B1, B4 copy/paste to B3 all the way until the last row of data.
I have an invoicing spreadsheet, what I want is to have a main working page and then tabs after separated by vendor, but save myself a bunch of manual work in the future.
I want to be able to work in the main tab and the other tabs automatically update with the new info.
So I want the macro to search the text in the first column and if it finds "AIM Land Services Ltd." then I want it to populate the appropriate tab, in this case "AIM", with all the respective information after it ie: A-O. But I dont want it to select any other vendors/ info.
This is what I have:
Sub MoveData()
For Each cell In Range("A1:A1") ' Where to look for the date
If cell.Value = "AIM Land Services Ltd." Then ' The date
Range("A5:O350").Select ' What is the range to copy
Selection.Copy ' Copy it
Sheets("Invoice_Log").Select ' Select the Sheet it applies to
Range("A5").Select ' Where to paste the data
[Code] .........
I've got a range of data in Column D approx 50,000 rows long and I need to go down this range and when theres a blank cell copy the info from the cell above. I've got some code which loops through this but I need to make sure I put "EOF and the bottom of the info to stop the loop. Is there a slicker way of writing this code?
Code:
Sub TestBlankCell()
Range("D5").Select
Do
[Code].....
I need formula for dynamic ytd calculation for the scenario mentioned below.
YTD YTD YTD
Month Apr-13 Apr-13 Apr-13 May-13 May-13 May-13 June-13 June-13 June-13 May-13 May-13 May-13
Product type x y z x y z x y z x y z
sales executive
a 5 10 15 20 25 30 35 40 45 ? ? ?
b 4 8 12 5 7 11 12 69 79 ? ? ?
Is there an excel format...custom maybe that if I enter 19,500 it will display just 19.5 or just 19?
View 3 Replies View Relatedhow can i write formula like this
if a2 = "multiplications and divisons of number 100", then .......
a2=200
multiplication of 100 =200,300,400,500,....
divisions of 100 = 50,25,12.5,........
*****************************************************
how can i say if all cells from a2:a8 = same number then ....
So I have a list of organizations in Column A, with multiple names (anywhere from 0 to 50 names) for each org. in Column B.
Org
Names
Company A
Brown, Jones, Smith, West
[Code]...
I want to do a find and replace for all of Column B, where all the names are replaced with their respective color values. If possible, I'd like this to all happen with the individual cells (so for example, B3 might go from "Brown, Jones, Smith" to "Red, Red, Blue").
If that's not possible, I could divide all the names into individual cells and then find and replace.
I have a set of data in once cell and its strung out. I'm trying to pull the numbers that follow certain words from that data. That's easy enough with the find and mid function for the first one. But after that I have a repeating word (see below) and different numbers that follow it. So I want to pull the second number string after that word, the third and so on.
I've attached an excel file that explains this better.
I have a spreadsheet with multiple columns, column N contains different countries (some are blank). I want to copy all data in that row (except columnt H) to a new sheet if N = USA.
Macros are disabled so a formula(s) is needed.
I have three columns
"A" is item number: "B" is sales Date: "C" is sales price.
What I would like to do is search column A and when there are two of the same item numbers know what date they sold and for what price. I would prefer to build another table that has only this data.
I am trying to do is when a user puts in an RMA number into a textbox (txtReviseRMA) on the userform, it will search column B on the spreadsheet to find the matching RMA number. Then I want it to look in column G for the Serial number and populate another textbox (txtSerial1) on the userform with that number.
My problem is that there can be a little as 1 serial number to an RMA or as many as 14, and each serial number has to go into a different textbox (they are named txtSerial1 all the way to txtSerial14).
If there are multiple serial numbers being sent in on one RMA, they are on different lines.
For example:
Column B (RMAs) Column G (Serials)
(B1) R66245 (G1) AA-10-1000
(B2) R66245 (G2) BB-20-2000
(B3) R66300 (G3) CC-30-3000
(B4) R66305 (G4) DD-40-4000
(B5) R66305 (G5) AA-10-1000
(B6) R66305 (G6) FF-60-6000
(B7) R66375 (G7) GG-70-7000..................
My current project involves sorting a spreadsheet, selecting specific qualifying data from the sheet, storing it into a variable, the pasting the individual values from the list into individual cells on a different sheet within the same workbook. Here is the code I have so far...
Code:
Sub Test()
'' freeze screen updating to remain on main worksheet
Application.ScreenUpdating = False
'' move to Avaliable worksheet
[Code] ..
When this code runs the first item in the list pastes into Sheet10 A1, but no other values from the list are placed into Sheet10. Previous to this I had been using a variation of this code to push the list into a combobox list within a userform. In that case instead of
Code:
ThisWorkbook.Worksheets("Sheet10").Range("A1") = x
I had
Code:
Combobox1.list = x
And this worked perfectly; creating a list within the combobox (in fact I can use this modification to interrogate my code to determine if the list is being properly generated, and it is).
I have a spreadsheet that is being updated every month with new project information. The spreadsheet is a log of all the hours worked by employees on each project. Due to many employees working on the same projects and each weekly log per engineer having an individual row to display these hours, there are many duplicated cells.
I am wondering if there is a way for me to display in another table, just a list of the projects that exist.
For example, the original table may look something like this:
WEEK MONTH PROJECT HOURS
1 Jan A 5
1 Jan B 4
1 Jan A 7
5 Feb C 5
5 Feb C 3
5 Feb B 8
I would like to display a table that looks like:
PROJECTS
A
B
C
In March there could be a project D and I would like that to be automatically added on to the second table, if possible?
I have the day of the week in B1 Emp Nbr in B2 Hours worked on B11
What I need a macro to do is match B1(day of the week) with the corresponding offset column in F1-R1, Pos and Post the Emp NBR(b2) and Hours(b12) in the corresponding column (F4 and G4)
I then will key in a new Emp and the hours and click the macro to execute the move over to the correct column and down one row.
Do this until I post Emp 999 at which time it will exit the macroOzgrid weekly hours.xlsx
The next day I will change the day in B1 to 2, corresponding to TUE, and it will move over to the correct column (H4 and I4) and post the daily data until I key in EMP 999
Then continue for the rest of the week.
I am using Excel 2008 for Mac. I have a spreadsheet that I am essentially using as a simple cheque ledger so that I can keep track of who I have written cheques to, for what amount, and what has already cleared. I have a the following columns:
Name
Date Written
Cheque Amount
Cleared?
In the 'Cleared?' column I mark an X when it has cleared the bank. I'm trying to find a way to get the SUM of the whole 'Cheque Amount' column that hasn't cleared (ie: has nothing in the 'cleared' column).
I have a spreadsheet with 20 sheets, with each sheet representing work done by an individual. The sheet would contain all the tasks and the percentage of work alotted. I now need to capture all these in a summary spreadsheet BUT only if the task is "IN PROGRESS" which is indicated in one of the cells.
Currently, we're doing it manually and it is a pain to go through all the names and copying the rows one by one. The summary should show the names with the corresponding project name and allocated time (copy whole row).
I'm trying to come up with a macro which will go through column A once searching for a value (ex. "apple") and upon finding a cell with that value, take cells 79-100 from that row, copy them to cells 82-103 of the next row, and delete the row which contains the value.
I believe the functions I need to use are:
I have the following code. It runs fine and I get the popup msg at the end but it doesn't do what I need.
Range w2 is a date. I want the macro to search for this date in Sheet2 just in Column B and when found copy everything on the row it is found down to the end of the worksheet whatever row that might be and then go to sheet1 and paste this below the last used row!
Code:
Sub test222()
Dim FindWord As String, Found As Range
Dim wsDest As Worksheet, ws As Worksheet, wb As Workbook
Dim Nextrow As Long, Lastrow As Long
Set ws = ThisWorkbook.Sheets("Sheet2")
[Code] ........
I know this is probably something easy, but I can't seem to find the answer. I am trying to create a macro that will look for the number 5 in column C and if found, copy and paste the entire row below. I need it to find all instances of the number 5 in column C and copy the row below.
I found the code below that seemed like what I needed but when I changed it to look for the number 5, it didn't work. I have tried putting 5 in quotes "5" and that doesn't help. Below is the code and sample data.
Sub OT()
If WorksheetFunction.CountIf(Columns(3), 5) > 0 Then
Dim xRow&
xRow = Columns(3).Find(What:= 5, LookIn:=xlFormulas).Row
Rows(xRow + 1).Insert
Rows(xRow).Copy Rows(xRow + 1)
End If
End Sub
Original Data
J4R N166888520J4R N166888180J4R N40018524J4R N400185176J4R N40018558
What I want it to look like
J4R N166888520J4R N166888520J4R N166888180J4R N40018524J4R N400185176J4R N40018558J4R N40018558
I'm using Macros. I have a workbook composed of three sheets "Anzahl" "Tabelle 1" and "Sheet1".
For "Sheet1" I want to create a macros that will search for the word "Sonntag" in the B column and then copy the corresponding values in the C and H column to a different part of the sheet. I have used the following code
Sub SearchAndInsert()
Dim i, k, k1 As Integer
k = 3
k1 = 3
For k = 1 To 355
If UCase( Range("b" & k).Value) = "Sonntag" Then
Range("c" & k).Select
Selection.Copy
Range("p" & k1).Select
ActiveSheet.Paste
Range("h" & k).Select
Selection.Copy
Range("q" & k1).Select
ActiveSheet.Paste
k1 = k1 + 1
End If
Next
End Sub
However, everytime I hit the run button or press F5, nothing really happens and nothing is copied.
I am trying to create a formula for a work report and I am afraid I am a bit of a novice with the more complex formulas. Here is what I am trying to do: Sheet 1 has Sales order numbers that duplicate based on the sales lines. Sheet 2 has single lines of sales order numbers and their assigned PO number. I would like to have Sheet 3 where it combines all data and duplicates and fills in the PO number to match all of the sales order numbers that repeat on the other sheet. I hope this makes sense. I can't seem to figure out if I need a VLookup or If or Match formula (or all of the above).
View 1 Replies View RelatedMy friend has multiple workbooks, each containing data. Each worksheet with have the same number of columns but a variable number of rows
Thinking through, what I deduced the simplest thing to do would be this: Have a macro that would Highlight all the data except the title row in the active workbookSwitch to a different workbook called "Consolidation"On the next free row, paste the information
They can then just activate another workbook and run the macro again
I want to copy Four Cells of info from one tab to another if the value of a cell is a specific value is this possible?
View 2 Replies View RelatedI want to copy all the information from a name range from a different workbook and paste it in my activeworkbook , but the trouble that I'm having is that it doesn't copy all the cells of the name range, just the first cell of the name range Here is the command that I'm using, not sure if it's the best approach
Code:
ThisWorkbook.Worksheets("US Input").Range("usInput").Value = Workbooks("US Input File.xlsm").Worksheets("US Input").Range("usInput").Value
Forgot to clarify that the name range is the same for both workbooks