Free Microsoft Excel 2013 Quick Reference

merging cells/data using a Macro

I have a 6,000-line macro. At a certain point, I need to merge data from
various cells into other cells.

I want to merge (append?) data/text from cells B1 and C1 onto existing text
in A1 but then delete B1 and C1 so that A1 "stretches" across that range
(A1:C1). I never know what the actual data/text will be in the cells, though.

So, let's say that:
A1 contains the words "I like"
B1 contains the words "chocolate" and
C1 contains the words "ice cream."

I want to end up with "I like chocolate ice cream" in Cell A1 and, in
effect, delete the text in B1 and C1 so that A1's text "carries over" into
those cells. Or, after joining (?) the data, merge A1, B1, and C1.

Of course, I can do it manually and "record a macro" but it records the
actual words but that won't work in real life because the words/text are
always different.

Thanks, in advance.

Dan


Post your answer or comment

comments powered by Disqus
I would like to set a cells data using a formula... is this possible?

For example is there a function to set cell A1 to have the value "Bob"

=set(a1,"bob")

Ok, first to say that I'm glad to be here. I surely hope we will all get along.
This would be my first time here writing, but not wisiting your forums.
The problem is I am still a newbe in VBA programing (I have the knowledge of basics from collegue).

Let me get to the point:

This is an problem I can not get to work; every time I open the excel workbook, when I press the combination of 2 keys (using a macro), the program code should update the using excel worksheet from an XML file.

I tried to find some solutions and I came across this one:


	VB:
	
 DOMDocument 
    Dim brtn As Boolean 
    Dim onode As IXMLDOMElement 
    Dim intr As Integer 
     
     
    brtn = xml_doc.Load("D:Documents and SettingsrivoseviDesktopwipToolib_databaseza export u xmlxml_output.xml") 'xml_doc
exiting reference
     
     
    intr = 2 
    If brtn Then 
        With Sheets("Sheet1") 
            For Each onode In xml_doc.selectNodes("//tag") 
                .Cells(intr, 1) = onode.getAttribute("name") 
                .Cells(intr, 2) = onode.getAttribute("type") 
                .Cells(intr, 3) = onode.text 
                intr = intr + 1 
            Next 
        End With 
    Else 
        MsgBox "Unable to open XML File" 
    End If 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

The excel worksheet contains a lot of columns and even a lot of more rows that contain numerical data.
The XML file is exported from an another excel file (not too important now) that is kinda similair to the above mentioned one.

The part I am stuck is when I run the macro (yet not being used by the key combination) nothing happens (no error reports, and no changes in the excel file).
But the computer clearly does an operation for about 3-4s as, but without any result.

I really hope you can get to help me as I tried to find help on other places too, with not too much help given. :/

P.s. Sorry if you didn't understand the whole point of the problem, I can try and explain more if it will be needed.

Hi, I'm trying to build/refresh a summary sheet using a macro that pulls
key data from other sheets.
I have started with listing the names of the other sheets with the do
loop below and this works ok. The command lines commented out is where
I am having trouble. I would like to populate rows in the summary sheet
with some data from the other sheets. I tried to use 'evaluate' , but
not sure how to call the sheet using sheet numbers, I also tried a temp
variable for the sheet name, but could not get that to work either. Can
anyone help with the correct way to do this?

For i = 1 To Sheets.Count - 1
Cells(1 + i, 2).Value = Sheets(i + 1).Name
' Cells(1 + i, 8).Value = Evaluate(Sheets(i + 1).Name!h6)
' tempb = Sheets(i + 1).Name
' Cells(1 + i, 8).Value = Evaluate(tempb!h6)
Next i

Thanks
Neil

--
neilgrant
Posted from - http://www.officehelp.in

Am trying to edit data in the worksheet where I have to edit only part
of the text in the cell - using a macro.

146-P1-0502 L
145-P13-0502 L
145-P8-0403 L
145-P9-0504 L
145-P10-0406 L

I only want to change the 0502 part to 0706 in all the cells. Tried
using a macro to select only part of it but it changes the other data
parts also.

I tried the code below but then it doesn't work for 145-P13-0502 L
where we have 2 digits after the P.

Dim StartChar As Integer
Dim EndChar As Integer
StartChar = 8
EndChar = 3
ActiveCell.Characters(StartChar, EndChar).Insert (206)

Please help.

Hi,
I am trying to transfer variable data from one spreadsheet to another using a macro. I have managed to record the macro but when i go in an change the data and run the macro again it overwrites the previous data. How can i stop this happening?

Thanks in advance,

Sean.

I am trying to poulate a word document with excel data using a macro

I have the following macro that will shift a selection of data to the left by one month so that people can enter and view data throughout the year in three month 'views' and shift each month as they go by. The problem is that the comments do not get moved (cut and pasted I assume). They stay attached to the same cell that they were entered into originally. Is there something that can be added to this code that will move the comments along with the other cell data when the macro is executed? I have some programming skills but this is all new to me! I would appreciate any help!

Thanks

The code:

Sub ShiftToNewMonth()
'
' ShiftToNewMonth Macro
' Keyboard Shortcut: Ctrl+m
'
    Range("E1").Select
    ActiveSheet.Unprotect
    Range("J21:J169").Select
    Selection.Copy
    Range("H21").Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Range("L21:L169").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("J21").Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Range("P21:P169").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("N21").Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Range("R21:R169").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("P21").Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Range("V21:V169").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("T21").Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Range("X21:X169").Select
    ActiveWindow.SmallScroll ToRight:=0
    Application.CutCopyMode = False
    Selection.Copy
    Range("V21").Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Range("X21:X169").Select
    Application.CutCopyMode = False
    Selection.ClearContents
    Range("R21:R169").Select
    Selection.ClearContents
    Selection.ClearContents
    Range("L21:L169").Select
    Selection.ClearContents
    Range("J20").Select
    Selection.Copy
    Range("H20").Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Range("E1").Select
    Application.CutCopyMode = False
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub


I have a requirement to read data from a excel sheet using a macro, the excel sheet will have some merged column cells , can any one help me how to write a macro to read data from merged cells

I have a list of names in a workbook. I want to use each of these names in turn in a loop in a macro but I cannot get anything near a correct code which will take the cell data from the worksheet in turn and use it within the macro itself

Any suggestions?

Sample work book attached with some better explanation of what I am trying to do.

I would like to use a macro to perform two specific functions:

1) Link data from a .txt (or .xls) file to a specific cell. I can do this using a hard link, but cannot figure out how to do it using a macro.

2) I would like to be able to link to variable folders, that is, have it link to a particular file regardless of where the file is located. Is this possible? Again, using hard links I do not know of a way to do this.

My ultimate goal is to have a macro that links to a particular file located in the current folder I am working in. For example, I like to use a single .xls file to analyze data in many different folders, and these folders are most likely not be the folder the parent .xls file is located in.

Hello,
I am trying to select a range of cells using a macro. The range grows each
day starting in cell B5 and ending in H* (* = the row with the last day's
worth of data). Two lines down, there is a grand total line, and two rows
from that is a footer of sorts, with date and a page number.

I found a way to get to the last used cell using a macro, however I do not
want the last cell. I want the last cell minus 4 rows.

Also, I am not sure how to select the entire range from B5 to H*. I am able
to select one cell or the other, but not the entire range.

I only dabble in VBA and have not had any program training. Any help that
anyone can provide will be greatly appreciated!

Let me know if you need more details.

Thanks in advance!

Hi guys!
I'm trying to do the following:-
I have a work sheet containg 42 columns and up to 1000 rows of data.
On a Second Worksheet I have a drop down Menu containg my search criteria ( linked to a cell). This Data can be ALPHA , NUMERIC, ALPHANUMERIC
I want to use that Cell contents for a filter on my fist sheet, then copy that data etc.etc.
I am falling down on the code to relate the Cell data from the drop down to the automatic filter on sheet 1. I want to do this from a macro attached to a button on page 2.
I'm sure you guys know the code, I just can't find any reference any where.

I'm writing a macro and I want to add an information window using data
validation. My macro creates the message in the cell and when I manually
click on it and add unauthorized data, it displays the window when I tab off
the field. I enter the same data through a macro and then select a new cell,
but the display window does not apear. How can I get the window to show?

I have a macro which filters information in a worksheet and then copies
certain columns of the filtered data to another worksheet. The macro that
does this already contains the filter criteria. What I would like to do is
allow a user to type information into a given cell which the macro will read
and use as the filter criteria (when the macro runs). Can this be done?

Hello all,

Background
I work for a manufacturing company that has three major buyers. We have a Master Schedule Workbook (with all parts in process listed), and then a workbook for each major buyer (i.e. GE Schedule Workbook). The "sub-schedule workbooks" are actually just INDEX MATCH formulas that pull from the Master Schedule.

Issue
We have a few columns which track the progress of the buyer's part through the shop. A grey cell means the process is completed, and a green cell means that the part is undergoing that process right now. Since INDEX MATCH doesn't copy cell formatting, the sub-schedules don't have the cell colors and thus we lose the "part progress data" that is so important to us. I'm assuming the route to take here is to use a macro to pull this data?

I don't know much about VBA or Macros, so some very specific instructions would be much appreciated. (My fellow employees know nothing about VBA, but perhaps a button would serve them better than a shortcut key or a manually running the macro.

Thanks in advance!

Hi,
Is there anyway to obtain the value of the last nonblank cell in a column using a macro?

Thanks.

I want to insert a picture into a sheet.
I want to do this by using a macro.
The macro will read in a filepath entered into a cell.

Any ideas

Thanks

Does anyone know a good way to use a macro to paste data copied from an
Excel data table into a Word table. The data will always go to the same
location in Word. For example data copied from the Project Desc field in
Excel will always go to the Project Desc field in Word, data copied from the
Project Location field in Excel will always go to the Project Location field
in Word, etc..

I have advanced knowledge of using macros to move data around in Excel, but
know very little about how to place it into word.

Any help would be greatly appreciated.

David

Hi,

I am using a loop to send an email to each relevant person from a range of email addresses. Each time the loop runs a temporary file is created, which I then save afterwards which a unique name for each individual. This was working fine until I attempted to amend the RangetoHTML function to use a macro enabled template rather than just an ordinary workbook. Now the email generated is blank and I can't understand why.


	VB:
	
 Range) 
     ' Changed by Ron de Bruin 28-Oct-2006
     ' Working in Office 2000-2010
    Dim fso As Object 
    Dim ts As Object 
    Dim TempFile As String 
    Dim TempWB As Workbook 
     
    TempFile = Environ$("temp") & "/" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm" 
     
     'Copy the range and create a new workbook to past the data in
    Application.DisplayAlerts = False 
    rng.Copy 
    [B]Set TempWB =
Workbooks.Add(template:="[URL="file://dartgroup.plcdata1Jet2FlightOperationsJet2FltOpsEFBChange"]dartgroup.plcdata1Jet2FlightOperationsJet2FltOpsEFBChange[/URL]
RequestsChangeTemplate.xltm") 
    [/B]    With TempWB.Worksheets("Sheet1") 
    .Cells(2, 1).PasteSpecial Paste:=8 
    .Cells(2, 1).PasteSpecial xlPasteValues, , False, False 
    .Cells(2, 1).PasteSpecial xlPasteFormats, True, False, False 
    .Cells(2, 1).Select 
    Application.CutCopyMode = False 
    On Error Resume Next 
    .DrawingObjects.Visible = True 
    .DrawingObjects.Delete 
    On Error Goto 0 
End With 
Application.DisplayAlerts = True 
 
 
 'Publish the sheet to a htm file
With TempWB.PublishObjects.Add( _ 
    SourceType:=xlSourceRange, _ 
    Filename:=TempFile, _ 
    Sheet:=TempWB.Worksheets("Sheet1").Name, _ 
    Source:=TempWB.Worksheets("Sheet1").UsedRange.Address, _ 
    HtmlType:=xlHtmlStatic) 
    .Publish (True) 
End With 
 
 'Read all data from the htm file into RangetoHTML
Set fso = CreateObject("Scripting.FileSystemObject") 
Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2) 
RangetoHTML = ts.ReadAll 
ts.Close 
RangetoHTML = Replace(RangetoHTML, "align=center x:publishsource=", _ 
"align=left x:publishsource=") 
 
 'Define the unique file name and save the document in the user's folder.
Dim TempSaveAs As String 
 
TempSaveAs =
"[URL="file://dartgroup.plc/data1/Jet2FlightOperations/Jet2FltOps/EFB/Change"]dartgroup.plcdata1Jet2FlightOperationsJet2FltOpsEFBChange[/URL]
Requests" & Me.txtcvalue.Value & "" & "EFB" & Me.txtConfigNo.Value & " " & Me.txtTitle.Value 
 
 
TempWB.SaveAs (TempSaveAs) 
TempWB.Close 
 
 
 'Delete the htm file we used in this function
Kill TempFile 
 
Set ts = Nothing 
Set fso = Nothing 
Set TempWB = Nothing 
End Function 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I have changed a few bits and pieces from Ron de Bruin's original code, but it is using the template (in bold) which has caused it to stop working. The debugger doesn't highlight any lines, it just doesn't populate the body of the email.

This is the code I am using for the email creation:


	VB:
	
 
Dim OutMail As Object 
Dim cell As Range 
Dim rng As Range 
Dim CellRng As Range 
Dim cvalue As String 
Dim TempHyperlink As String 
 
Application.ScreenUpdating = False 
Set OutApp = CreateObject("Outlook.Application") 
Set rng = TempSheet.Rows("1:16") 
 
With ThisWorkbook.Worksheets("Log") 
    Set CellRng = .Range(.Cells(iRow, 6), .Cells(iRow, 26)) 
End With 
 
On Error Goto cleanup 
For Each cell In CellRng 
     
     
     
    If cell.Value Like "?*@?*.?*" Then 
        txtcvalue.Value = Left(cell.Value, WorksheetFunction.Search("@", cell.Value, 1) - 1) 
        TempHyperlink =
"[URL="file://dartgroup.plcdata1Jet2FlightOperationsJet2FltOpsEFBChange"]dartgroup.plcdata1Jet2FlightOperationsJet2FltOpsEFBChange[/URL]
Requests" & Me.txtcvalue.Value 
        Set OutMail = OutApp.CreateItem(0) 
        On Error Resume Next 
        With OutMail 
            .To = cell.Value & vbNewLine 
            .Subject = Me.txtTitle.Value 
            .ToDoTaskOrdinal = DeadlineDate 
            .TaskDueDate = DeadlineDate 
            .TaskStartDate = Me.txtIssueDate.Value 
            .FlagStatus = 2 
            .FlagRequest = strFlagRequest 
            .FlagIcon = 6 
            .HTMLBody = EmailContent & "    Record  - Please complete the relevant Department Specific Information Fields " &
RangetoHTML(rng) & "
" & "
" & "
" & "Should you require further information regarding the Change Request prior to providing this feedback, please see" & Me.txtOriginator.Value & " prior to the deadline." .Send End With On Error Goto 0 Set OutMail = Nothing End If Next cell cleanup: Set OutApp = Nothing
If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Thanks,

Maria.

Hi,

I have a workbook contining a master spreadsheet, whenever a new row is
created in the master sheet I want to insert it into a specific (but
changing) row in one of the other spreadsheets in the workbook
depending on a value being entered in a specific column in the master
sheet.

I have used a macro with an autofilter function to do this, but it
overwrites data in the row I specify to copy to, is their any way I can
make it add the data to this row and automatically move all the other
data that follows down a row. I ame using the range.copy function

I have been trying to append same type data from one spreadsheet to another
using a macro but since I never know how many lines of data are in each
spreadsheet I am appending it is either not inserting at all (because I am
selecting all -- it is indicating the rows are not the same size) or it is
only copying the number of rows from the second spreadsheet (not the number
of rows from subsequent spreadsheets if they are higher or it copies blank
rows). I am sure there is vb code that allows it to insert from one to
another on the fly but I can't get it to work. Any ideas would be
appreciated.

I want to add information to a worksheet and then sort the data into
alphabetical order all using a macro.

Can not seem to be able to get this function to work.

Can it be done? If so how?

i'm trying to past data from a worksheet into a list sheet using a macro.

when i go through the steps and record it as a macro, it works perfectly but when i try and run the macro it works but it doesn't paste anything into the cell on the list sheet. very upsetting!

this is the code from the macro:

Range("F5").Select
Selection.Copy
Sheets("Database").Select
Range("A2").Select
Application.CutCopyMode = False
Selection.Copy
Application.CutCopyMode = False
IgnoreBlank = Range("A2:A74")
With Selection.Validation
.Delete
.Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _
:=xlBetween
.IgnoreBlank = True
.InCellDropdown = True
.ShowInput = True
.ShowError = True
End With

Any help would be massively gratefully recieved.

Dan

Hello,
I do not know either to use a macro or a function program. I am using couple of sheets in one worksheet. On the first sheet I plan to ask the user to select two data’s from both drops-down menus. The other two data will be inserted by the user in a particular cell that I have named. The third data is in sheet two as a table which I will need the information given above to find the numeric data from the table.
My plan was hopefully use a button called "Calculate" so when the user is done selecting and inputting he or she will click on the "Calculate" button and this function program will enable and conclude.
This program will need to get the data inputted from the user on the first sheet. I know how to do this, I even done the button without the function. I need help using the data given by the user to find the numeric data in the table in sheet two. I need help getting started with the program using the above information to look for the numeric info in the table.


No luck finding an answer? You could always try Google.