I have some values in Range F2, F26 ,F48 and so on.
I need to transfer these values using a macro to
C8776
C8777
C8778 and so on.
Since there are a lot values , I would like to have a macro for this operation;
I have a report where, month in month out, i have to append columns on the right, to give a Year to date figure. Now there are many rows with different numbers but one of the columns is call [NOTES]. This is where the user can provide commentry on the weekly figures.
Now whats getting annoying is i have to copy this commentry and then create a comments box then paste the text in there and finally, hide the comment.
So what i want to be able to do is be able to Copy the cell, then, have a right click menu button saying "Paste As Comment", so it paste the selected cells contents into a comment and hides it.
I'm trying to copy the cells from a sheet and transfer only the values to other sheet.
I did it via code and it worked fine, except for the dates. In the new sheet the months and days are swapped.
The original date is composed via the concatenate function, since it gets inputs (day, month and year) from the user in different cells. It's in the format D/M/YYYY (examples: 4/2/2014, 10/12/2013). I believe the excel interprets it as Text, even if I formated the cells to Date.
I think it may have something to do with the default format in different countries. Here in Brazil we use DD/MM/YYYY, but my Excel is in english and in US the format is MM/DD/YYYY, am I right?
If i have a basic formula that reads a6=(a2*a3*a4)/a5
i need a5 to be entered as one value but be equal to another. Such as 12=6530 and 10=10380 ...
I am trying to transfer a set of values from a column on sheet1 into cells on sheet2. Sheet2 contains labels I made up so the cells I want the values transferred to are not in column format. Is there a way to write a formula so that it will transfer the values indirectly? By indirectly I mean that I want sheet1 a1 to go to sheet2 a1 then I want sheet1 a2 to go to sheet2 a32.
View 5 Replies View RelatedI am currently trying to run a macro that will take all of the values from one column in a workbook, copy and paste them in another sheet in the workbook. The data may vary in size and the macro must run until the preceding columns value (on the same row) is empty, which will signify that there are no more values to copy. Also this data may duplicate, which in this case I would only like to paste unique values.
View 1 Replies View RelatedIn a proceedure I'm trying to copy some ranges including: text,cellcolor and borders to a number of different places. What I've got from modifying the macro recorder was
View 2 Replies View RelatedI have a Userform1 that is launched on a Before_DoubleClick Event. This UserForm1 has a CommandButton1 on it. When CommandButton1 is clicked I would like for it to transfer information into "MySheet" based on the current Cell address. I have the below code but it is not working correctly(The red parts are where I am having difficulty). Here is the Worksheet Before:
Worksheet Click Event is Initialized Assume Active Cell is A2:
Excel 2012
A
B
C
1
BatchDate
BatchNumber
Initials
[Code] ....
MySheetThe data above this instance will be contiguous for Example:
Excel 2012
A
B
C
1
BatchDate
BatchNumber
ID Number
[Code] ......
MySheet
Sheet to Paste Data After Procedure:
Excel 2012
A
B
C
1
BatchDate
BatchNumber
ID Number
[Code] ..
MySheet
Code:
Private Sub CommandButton1_Click()
Dim LastRow As Long
Dim BatchDate As Range
Dim BatchNumber As Range
BatchDate = Range(Selection.Address).Value
[Code] ......
why the last line below errors when the line before doesn't?
Error 1004 Method ' Range' of object '_Worksheet' failed
Sub tester
Dim wbThis As Workbook
Dim wsSrce As Worksheet
Dim wsDest As Worksheet
Dim lastSrceRow As Double
Dim lastDestRow As Double
Set wbThis = ActiveWorkbook
Set wsDest = Workbooks(wbThis.Name).Sheets("Detination")
Set wsTmpl = Workbooks(wbThis.Name).Sheets("Source")
I've been through countless threads, specified xlapp.... etc.
I have two workbooks with the same sheet names.
One workbook is called "Main" and the other is called "Subset".
Column D in each sheet is mostly identical between the two workbooks and contains the LOOKUP references.
I would need a macro (called from the "Main" workbook) which allows the user to browse for the "Subset" workbook.
Once selected, the macro should cycle through all sheets of the "Subset" workbook and for column D values LOOK them UP in "Main" workbook and transfer the adjacent values from column C of the "Subset" workbook to column C of the "Main" workbook.
Appropriate error handlers need to be in place for:
- Selecting the appropriate workbook (i.e. one which contains identical sheet names)
- The "Main" workbook is likely to contain additional sheets which should be ignored by the macro
- Column D LOOKUP entries which are not found in the "Main" workbooki should be ignored (and vice-versa)
I have Information found on Sheet 1. I need the program to take the value found in Column B and try to find matches found on Sheet 2 in Column B. Here's the thing it is only going to take the first 3 characters found in the Cell on Sheet 1. But in sheet 2 it will need to pull all information that matches those 3 characters. I included a sheet on what the finished product should look like for two of the sheets. If the program finds a match it will transfer the original and the copied match to a sheet Named "Name". The correct format can be found on "Finished Sheet Name". If no matches are found it will place the original information from Sheet 1 onto a sheet called "New".
Test2.xlsm
I am using a calendar control 11 in a user form. I would like to create a combobox on a user form that when you click the drop down button it opens the calender then the user can select a date which is then returned to the combobox. I beleive the way to do this is to trap the dropbuttonclick event. Tho it dosent seem to work.
Private Sub ComboBox2_DropButtonClick()
Calender.Show
ComboBox2.Value = Calender.Calendar1.Value
End Sub
I need a macro to get the values from cells D29 and H24 in the Resource Calculator sheet and populate it into cells N8 and O8 in the Input form.
Users will then be able to change the information in the calculator and click the macro again to populate N9 and O9 and so on.
Is there a way to do this?
I've attached the file for you to see.
Put in a statement within a Macro that populates cells with the values that I want it to but instead of populating all at the same time, is it possible for the values to be delayed.
I have designed a mock spreadsheet (attached) it has two columns 'Before' and 'After'
After = Before values (in this mock)
When you press the button, the values are populated straight into the 'After column' can we add the delay between the values? So that the values dont come up straight away.
I wanted to separate my cell values with comma into a column
Item
DepIDs
No
IDA
1
2000
1
2000
[Code]...
the output will be below table,
Item
IDA
DepIDs
1
2000
2000
[Code]...
I have a main workbook that is meant to summarize data from other workbooks
In Row 6 from column H on I have workbook names in each cell
Column G in all workbooks (including the main one) contains our branch #'s for our offices
For each workbook listed in row 6 , I need to open that workbook (I have that setup with the code below---notice there is an AX.xls that is appended to the file names listed in row 6 in order for the names to match what is in the windows directory)
In the newly opened workbook, I need to, for each value in column G, copy the value from adjacent cell in column H (the dollar value) then search column G of the main workbook for a matching branch and paste the value to the appropriate row under the workbook name column (remember workbook names are in row 6)
The trouble is, for each branch in column G in the newly opened workbook that cannot be found in the main workbook, I need to paste the new branch # at the bottom row of/in column G and the $ value (H column value from the newly opened workbook) to the corresponding row under the workbook name column
Sub OpenWBs()
Dim Rng As Range
Dim WB As Workbook
Dim MyPath As String
Dim lastCol As Integer
Dim newRange As Range
lastCol = Cells(6, Columns.Count).End(xlToLeft).Column
Set newRange = Range(Cells(6, 1), Cells(6, lastCol))
MyPath = "F:AccountingAPAdvertising AccountsLA TimesAgentExtractorCompletedLIST"
For Each Rng In newRange '
I have a new spreadsheet that I made to give us the break down of each invoice we create. what we want is to see each job that is done (by code), the amount we charge per each, the cost of goods per each, and the profit margin. In this spreadsheet I have two tabs. in tab one it has the form I made that has a place to input the data that will change with each invoice as well as a section that is broken down into columns. In these columns I have it listed this way. CA=Job code, CB=Job Description, CC=how many, CD=Charge ea., CE=rate total, CF=COGS ea., CG= COGS total, CH=profit
I have the formula done for all of the math the get the totals. My idea was to make a second tab. In tab 2 I have everything listed in columns that will go into tab one. It is broken down like this. CA=Job Code list, CB=Job Description list, CC= charge ea. CD= COGS ea.
What I want to be able to do is when I type in the job code in the first cell under the column A on tab one, it will bring over all of the info from tab 2 and put it in the right spot for that code. So if I typed in say TT001 in that cell it would bring over the description matching that code and put it in the cell under B, along with the charge in column D, and the COGS under column F. And I want it to be that way for each code I type in and it will be different each time I fill this out. How do I explain this part? Ok so I a new invoice made its # 22. I take this spread sheet and do what I want and get all the totals so I print it out. I then want to be able to clear all the info and or just type in a new set of codes that will be on invoice # 23 and then #24 etc.
So what I am saying is the codes will be different each time I fill it out. that's why I have them listed on a different tab and will just put them in tab one long enough to get the total and print it out. Is that clear as mud? I will stop here and wait and see what questions I get back.
i have the following macro which transfers credit control data from an outstanding tab to a paid tab:
View 9 Replies View RelatedI have a macro that transfers data from one workbook to another, this works fine, however the problem I have is that one of the files has the name changed frequently to another name, so each time i run the macro I have to change the original file name to the revised file name.
here is a copy of the macro
Dim NextRow As Long, Isht As Worksheet, Lsht As Worksheet
Application.ScreenUpdating = False
Set Isht = Workbooks("RMG Daily.xls").Worksheets("Retail Team 1")
Set Lsht = Workbooks("Weekly Monthly YTD Scores.xls").Worksheets("Raw Data")
NextRow = Lsht.Cells(Rows.Count, 2).End(xlUp).Row + 1 'next empty row in col A
the "RMG daily.xls" is the file change is used as a template but then saved as ie "010108.xls"
rather than me editing the macro each time I need to create a pop up box that will allow user to type in file name for the transfer - this will then link in with this part of my VBA code
"Set Isht = Workbooks("RMG Daily.xls").Worksheets("Retail Team 1") to instantly recognise where my data is being transferred from.
I am trying to do some kind of sorting?
if the first character of the cell is number or * then copy that cell(row i ) and the cell above(row i -1) to sheet2. I am not sure how to check the first character is *.
The idea is this:
[Code]] .....
1) Numerous cells in columns A and B on a w/s have a combination of two fonts each.
For example, cell A1 shows # 15 on the Formula Bar. The # sign is in "Wingdings 3" Font and 15 is in "Arial".
Cell A1 correctly displays |15 (i.e.; up arrow,space,15).
2) Other cells on the w/s reference those cells.
For example, cell C1:: =IF($J$5="Vortex",A1,B1)
When the above condition is true: Is there a way to return | 15 (i.e.; up arrow,space,15) in C1 , exactly as displayed in A1 ??
I created UserForm for people to enter there details into this form. I designed it ok, but i need it to, once it is filled in to copy what is entered onto a page in the spreadsheet.
So someone enters there details into a form that appears but then i need their details to appear on the spreadsheet at the click of a button.
I have the mask edit box functioning quite well. However, when the data is transferred from the userform to the worksheet, I cannot get the data to format to the preset format of the cell it is populating. The phone number displays as ########## instead of (###) ###-#### as it is formatted to do. Here is an excerpt of the transfer code I am using:
Private Sub CommandButtonAdd_Click()
Application. ScreenUpdating = False
ActiveWorkbook.Sheets(" Roster").Activate
Range("C1").Select
Selection.End(xlDown).Select
I have a form to fill out and I would like a macro to input the data on the form (column) to another sheet in the next open row.
See attached. DATA_EXAMPLE.xlsm
I am wanting to create a spreadsheet for my stationary orders with some macro's and I have no idea on how to set this up. All the product information will be in Sheet 1 and the Order Form will be in Sheet 2 of the document.
In "sheet 1" we will make a list of all items that get ordered on a regular basis. This can be up to 500 items but
I have given you an example of 6 to play with.
We need the "QTY", "Unit Price" & "Total" in both "Sheet 1" and "Sheet 2" to sum up with a formula
Once the user has chosen the line item they will click on the "add" button next to the line item.
This will inturn take that particular line item and insert only the "Code", "Product", "Unit Price" & "Total" columns in "Sheet 2"
and leaving out the "Page Number", "Item Number" & "Description" & "QTY" fields to Sheet 2 (Order Form)
The user will manually change the "QTY" field on the "Sheet 2" (Order Form)
When the user selects another product and clicks "add" then this new line item will be added below the first line item
on Sheet2 (Order form) and so on.
We will probably only be ordering 10 - 50 items each time so we wont need a huge list on the order form (Sheet 2)
I'm having trouble with this macro I'm trying to write. I have a worksheet that's subtotaled like this:
Cash 100,000
Land 65,000
Equipment 104,000
Kroger Foods Total 269,000
Cash 200,000
Buildings 25,000
Equipment 25,000
Prepaid Rent 50,000
Whole Foods Total 300,000
The data is obviously just an example, but that's how it's set up (a subtotaled worksheet with three columns). My issue is that I need to write a macro to copy these numbers in column 2 (ex. land - $65,000) and paste them in a cell in another worksheet that finds the particular cell to paste it in based off column 1 and column 2.
For instance:
Cash Equipment Land Prepaid Rent
Whole Foods
Kroger
Essentially, I'm trying to find a way to copy the totals in column three and paste them into a cell in a certain row (based on column 1), and column (based on column 2). I'm pretty lost, and I just need some guidance as to how to tackle this. So far my code sets column 1 and 2 as strings and sets the columns in the second worksheet to these strings, and from there I'm lost
I need a macro for a command button to perform the following:
If B8 is the active cell and I click the command button,
transfer value from:
B3 to B8
D3 to D8
E3 to E8
F3 to F8
G3 to G8
H3 to H8
I3 to I8
Likewise, if B9 is the active cell and I click the command button,
transfer value from:
B3 to B9
D3 to D9
E3 to E9..........
Two macros have been developed in order to write entries to a sheet called Detention Register from another sheet named Database.
The first macro (AutofilterDatabase1) filters for two criteria in the Database sheet: 1. All dates 7 or more days prior to today's date (5th field called rDate) & 2. The smallest positive value for a person in field 10 (called rSmallest).
Once these filters have been applied a second macro (named WriteDetentionRegister) is supposed to write the filtered entries to a sheet called Detention Register. However, if no enties at all are found by the filter it writes all entries to the Detention Register. Is there any way of stopping the writing process if no entries at all are found after the filtering process?
I'd also prefer it if after the writing process (or not as the case may be) that all autofilters were returned to displaying 'All' data in the Database sheet for fields 5 and 10. Is this an easy tweak to make to the existing code?
I enclose the code of the 2 macros.
Sub AutoFilterDataBase1()
Application. ScreenUpdating = False
'Get today and convert to serial value, subtracting 7 days
d = CLng(Date) - 7
DBase.Activate
DBase.AutoFilterMode = False ......................
Workbook A contains data that feeds into Workbook B. Workbook B retrieves data from Workbook A (via a macro) and then puts data into cells in Workbook B, Workbook B process the information and does calculations etc... on the data from A. Now I want to transfer the results back to Workbook A in specific cells.
Example (just for information only and not actual problem): Workbook A, contains employee information, years in company, position etc... This is transferred to Workbook B (this portioin I have coded and tested). Workbook B calculates pay, benefits etc... Now I want to put the pay information back to Workbook A.
I'm building a report and need to transfer data to a History Tab each time the macro is run.
"Report" Tab contains data to be transferred: C6:F6 to the "Historical" tab B:E in the appropriate date row.
The Date would be found from the Oldest date (furthest into the past) on the "Calculations" sheet, Column U.