Computer Support Forum

Help with this Macro!?!? Excel Macro code

Question: Help with this Macro!?!? Excel Macro code

Hi, I'm new to this forum (actually forums in general) I was wondering if anyone could help me with a macro code. I work with 4 Excel sheets for my shippments, 2 sets for one plant and 2 for the other but all within the same file.

What I am trying to do is set up a macro that will automatically search for changes within sheet 1 (also for sheet 3, the other plant) When I change the rows color from blank to green/red, I need the macro to automatically cut that whole row and paste it into sheet 2 on the next blank row.

This doesn't seem like it would be that hard to do, but I know know the whole macro language.

If you can answer this please send me an email to soulblazor(@)hotmail.com

Thank you for your help.

More replies
Relevance 100%
Preferred Solution: Help with this Macro!?!? Excel Macro code

I recommend downloading and running Reimage. It's a computer repair tool that has been proven to identify and fix many Windows problems with a high level of success.

I've used it in the past to identify and fix everything from blue screens (BSOD's), ActiveX errors, corrupt files and processes, dll/exe/sys errors, recover lost memory, Windows update problems, defragging, malware removal etc.

You can download it direct from this link http://downloadreimage.com/download.php. (This link will automatically start a download of Reimage that you can save to your computer.)

Relevance 100.45%

I am using the MSN Stock Quote addin with MS Excel 2003. Prior knowledge of this should not be necessary, but I just thought I'd provide full context to my issue. I wrote a macro that essentially records daily stock prices. Because I don't want to store old quotes into perpetuity, i need to ensure that the update quotes button has been hit. I use sendkey() to accomplish this, and then the macro copies the quotes and pastes them elsewhere. My main goal is to have the task scheduler open this file daily so i dont ever have to think about this again, and i'm so close. Essentially my problem is that it takes a few seconds after the update button is hit for the add-in to fetch the new prices from the internet, and this process appears to be delayed until the macro is finished. This is a problem because the macro ends up recording un-updated quotes. I have tried the Application.Wait procedure, and I've tried time consuming loops, do while loops, etc. All of these approaches result in the same thing: Can someone please help me with this? I'm too lazy to hand update this spreadsheet every trading day for the rest of my life (or until i quit). Thank you in advance!
 

Answer:Excel Macro - Allow cells to update query before macro completes

Can't you just put the update macro code in a module of it's own and call it from the main macro?

Regards,
Rollin
 

1 more replies
Relevance 99.22%
Question: EXCEL macro code

I am trying to macro code a button "UPDATE" in excel where i want to it to function as if column M5:M50 has a "Y" in it then copy the entire row and paste it in sheet 2 and delete it from the old. I am new to coding and have no idea how to do thismy rows start with info at row 5 there is no end to how many i may have but i will say it probably will never hit 50 Please Helpthe sheet i want it to paste is called SCG transfers

Answer:EXCEL macro code

You didn't give us the name of the sheet that contains the original data, so I used Source Sheet in this code. Before you run it, you'll need to change every occurrence of Source Sheet to the name of your sheet, or change the name of your sheet to Source Sheet.I strongly suggest that you try this code in a backup copy of your bookwork since it deletes rows and those deletions can not be undone.Sub MoveY()
'Determine how many rows of data in Source Sheet
lastRw = Sheets("Source Sheet").Range("M" & Rows.Count).End(xlUp).Row
'Loop through rows
For nxtY = 5 To lastRw
'If there is a Y in Column M, determine the next
'empty row in SCG transfers and copy the Row
If Sheets("Source Sheet").Range("M" & nxtY) = "Y" Then
nxtRw = Sheets("SCG transfers").Range("M" & Rows.Count).End(xlUp).Row + 1
Sheets("Source Sheet").Range("M" & nxtY).EntireRow.Copy _
Destination:=Sheets("SCG transfers").Range("A" & nxtRw)
End If
Next
'Loop through Source Sheet rows in reverse order so that they can be deleted
For nxtY = lastRw To 5 Step -1
'If there is a Y in Column M, delete the Row
If Sheets("Source Sheet").Range("M" & nxtY) = "Y" Then
Sheets("Source Sheet").Range("M" & nxtY).EntireRow.Delete
End If
Next
End SubClick Here Before Posting Data or VBA Code ---> How To Post Data or Code.

2 more replies
Relevance 99.22%

Sub GetData()
Application.ScreenUpdating = False
If ThisWorkbook.Sheets("Raw Data").Range("DD").Value = "YesData" Then
Worksheets("DD").Range("A1:Y100").Value = Worksheets("Raw Data").Range("A1:Y34").Value
End If

In the above code I am trying to populate another sheet "DD" with specific information from "Raw Data". In the If statement I want the data populated to only retrieve data rows from "RawData" that contains the word "YesData" in the D column.

Please help.
 

More replies
Relevance 99.22%

Hi,
I am using excel 2007. I have some cells in sheet 1 (made to look like a form) and i have created a macro to take the data from these cells and put them in a row on sheet 2. However, what i cannot figure out it what command i need to insert (and where) to have the data go to the next empty row.
This is what i have recorded on the macro:
Range("D9").Select
Selection.Copy
Sheets("Sheet2").Select
Range("A2").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
Range("D11").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
Range("B2").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
Range("D13").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
Range("C2").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
Range("D15").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
Range("D2").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
Range("D17").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
Range("E2").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
Range("D19").Select
Application.CutCopyMode = False
Selection.Copy
Sh... Read more

Answer:help with Macro code in excel

Copy selected cells to next sheet

This will work for you

Code:
Sub move_info()

Dim ws1 As Worksheet, ws2 As Worksheet
Dim Rng As Range, c As Range, x, Rws As Long

Set ws1 = Worksheets("Sheet1")
Set ws2 = Worksheets("Sheet2")
Rws = ws2.Cells(Rows.Count, "A").End(xlUp).Row
Set Rng = ws1.Range("D9,D11,D13,D15,D17,D19,D21,D23")
x = 1

For Each c In Rng.Cells

ws2.Cells(Rows.Count, x).End(xlUp).Offset(1, 0) = c
x = x + 1

Next c

End Sub

Range Selection Codes
 

3 more replies
Relevance 98.4%

I'm working on a Bill of Material creation automation project that requires some expertise in VBA, and I have none.
The objective is to run a macro from an excel spreadsheet called "PGE BOM", to do the following:
1) Go to the folder "C:\Documents and Settings\Desktop\Auto Project"
2) Find all the .mdb databases in this folder
3) Find "HistoricalMaterialItemsAll" table in EACH of those databases in step 2, and import the data from the columns listed below into PGE BOM.xls's columns C through G:
DrawingNumber
ItemNumber
Quantity
PgeCode
Description
The following is a VBA code that my friend had written in Excel 2007. Unfortunately I have an older version (2000) and the code does not seem to be compatible with Excel 2000.
Sub ImportAccessData()
Stop
dPath = "C:\Documents and Settings\Desktop\Auto Project\"
sFile = "*.MDB"
strSrch = dPath & sFile
Set TargetWB = Application.ActiveWorkbook
Set TargetWS = TargetWB.ActiveSheet
sRow = 2
bFile = False
If Dir(strSrch) <> "" Then
strFlNm = Dir(strSrch)
bFile = True
End If
Do Until bFile = False
strPath = dPath & strFlNm
Call GetData(strPath)
strFlNm = Dir
If strFlNm = "" Then bFile = False
Loop
End Sub
Sub GetData(fl)
Stop
strSQL = "Select HistoricalMaterialItemsAll.* From HistoricalMaterialItemsAll"
Workbooks.OpenDatabase fl, strSQL, xlCmdTable
Set WB = Application.ActiveWorkbook
Set WS = Applicatio... Read more

Answer:Excel Macro - VBA code to import access data to excel

Hello, and welcome to the board!

When posting code, please use CODE tags, which extremely helps with readability.

Perhaps you could try using the code constant???...
Code:

Option Explicit

Sub ImportAccessData()
Dim dPath As String, sFile As String, strSrch As String
Dim TargetWB As Workbook, TargetWS As Worksheet
Dim sRow As Long, bFile As Boolean, strFlNm As String, strPath As String
dPath = "C:\Documents and Settings\Desktop\Auto Project\"
sFile = "*.MDB"
strSrch = dPath & sFile
sRow = 2
bFile = False
Set TargetWB = Application.ActiveWorkbook
Set TargetWS = TargetWB.ActiveSheet
If Dir(strSrch) <> "" Then
strFlNm = Dir(strSrch)
bFile = True
End If
Do Until bFile = False
strPath = dPath & strFlNm
Call GetData(strPath, TargetWB, TargetWS)
strFlNm = Dir()
If strFlNm = "" Then bFile = False
Loop
End Sub

Sub GetData(fl As String, WB As Workbook, ws As Worksheet)
Dim strSQL As String, iRow As Long, sRow As Long
strSQL = "SELECT HistoricalMaterialItemsAll.* FROM HistoricalMaterialItemsAll"
Workbooks.OpenDatabase fl, strSQL, 3 'xlCmdTable
iRow = 2
Do Until ws.Cells(iRow, 1) = ""
ws.Cells(sRow, 7) = ws.Cells(iRow, 5) 'Get the Description
iRow = iRow + 1
sRow = sRow + 1
Loop
Application.DisplayAlerts = False
WB.... Read more

1 more replies
Relevance 97.99%

Hello to All!

Does anyone have a code/macro that will find a certain text in cells (*total) in a column, then based on the text found, search another column for the totals association with it, format the color & bold that total, and calc the sum in a 3rd cell?
Column 1 would have something like, abcTotals
defTotals
ghiTotals .........etc.
Column 2 ...something like, $63.00 (blue & bold formatting when found) ........ etc.
Column 3 would hold the sum of all the*Totals

Thanks!!!
 

Answer:Solved: Excel 03 Macro/Code

8 more replies
Relevance 97.99%

Hi,

I'm a very new VBA user and have spent the better part of 2 day on this great website looking for a similar problem i've run into.

My problem is that i want information from "test" workbook~"results" worksheet to automatically update the "Tech Data" worksheet as well as export an update to the "Master" workbook.

FYI, two separate people input data: iron&manganese and Corrosion&scale inhibitor residuals. This data is exported to the 'results' worksheet and the same simple formula can be used to export to "tech data" worksheet. I am having a hard time getting the 'tech data' worksheet to export to the master workbook. I'm hoping someone can help me ensure the master file compiles all informaiton being exported to it. Note that the master file has several other headings as i was hoping to expand on the starter macro.

I created the tech data worksheet so data placement matched the master workbook format.
 

Answer:Solved: Excel-macro code help

15 more replies
Relevance 97.99%

I've actually written a line of code that is too long to fit in the macro window. How can I continue the code on another line in the programming window? I've tried using &'s but it doesn't work. Thanks.
 

Answer:Excel Macro - Code Continuation

6 more replies
Relevance 97.99%

I have a macro in excel which auto fills a specified column with the formula in the first cell.

Assuming that Row 1 contains my headers, the code looks as follows:

Range("M2").Select
Selection.AutoFill Destination:=Range(M2:M1845)

Is there a way instead of specifying the range to ask the macro to just carry on filling the formula until it reaches the end of the data entered, i.e. the last row of data?

I'm sure it was something like Range = xlDown, but every attempt seems to fail.

I am running Excel 2007 by the way.
 

Answer:Solved: Excel Macro Code

Try using

Selection.AutoFill Destination:=Range("M2:M" & Cells(Rows.Count, "M").End(xlUp).Row)

Rollin
 

2 more replies
Relevance 97.99%

I am trying to create an excel (2007) macro (in windows XP) which will compare a list of data on 1 worksheet with that on another worksheet within the same workbook and then have an out put in the first spread sheet.
Example.
ID Number​​​​​​first name​​​​​​last name ​​​​​​job type code​​​​​​qualified​​​​​​
1234​​​​​Bill​​​​​Smith​​​​​BLK​​​​​
Compare
5678​​​​​John​​​​​Citizen​​​​​REV​​​​​

1000​​​​​Paul​​​​​Person​​​​​MIF​​​​​
Clear

(on sheet 1) columns 1,2,3 & 4 would be prepopulated (via a copy and paste method), an action button "Compare" would be used, this would then compare the data in the "Job type code" fields with the "qualifications" listings from a range that would be listed on worksheet 2 of the same workbook. Example see below

BLK Qualifications
Certificate of economics
Certificate of accounting
REV Qualifications
Certificate of leisure
Certificate of Hope



MIF Qualifications
Certificate of S... Read more

Answer:help creating code for an excel macro

You say what you are trying to do, but...
What is the question?
 

3 more replies
Relevance 97.99%

I'm trying to code a macro with (among other things) a section using countif(). For now let's just assume that the range is fixed, say C3:C43. The criteria needs to reference 2 other cells. I want the countif to equal

(the # of values <= the # in cell(31)) - (the # of values <= the # in cell(30)), both in a different column.

I can select the range but I don't know excel's code for using countif() in a macro. Here's what I tried so far:

Range("C3:C43").Select
a = Selection.CountIf("<=m-<=p")

where m=# in cell(31) and p=# in cell(30)
I get an Object doesn't support this method error.

How can I get there? TIA
 

Answer:excel vb macro code for countif()

7 more replies
Relevance 97.17%

one more little problem.

I have this code (which I got from recording a macro) for placing a chart on a page:

Range("C920").Select
Charts.Add
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SetSourceData Source:=Sheets("FixedPointsBlank").Range("C920"), PlotBy _
:=xlColumns
ActiveChart.SeriesCollection(1).Delete
ActiveChart.SeriesCollection(1).XValues = "=FixedPointsBlank!R9C3:R20C3"
ActiveChart.SeriesCollection(1).Name = "=""Frequency"""
ActiveChart.Location Where:=xlLocationAsObject, Name:="FixedPointsBlank"
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "Frequency Plot for Fixed Measurements"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = _
"Upper End Value (cpm)"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Frequency"
End With
ActiveChart.HasLegend = False
ActiveChart.HasDataTable = False

On the 2 lines that have the Range function I have replaced inside the parenthesis with: Cells(30, 8), Cells(lastUEV, 9)
which works just fine. I have to do this because from one run to another the range size will change.

The other line that needs to be changed the same way is:

ActiveChart.SeriesCollection(1).XValues = "=FixedPointsBlank!R9C3:R20C3"

However, when I replace the R9C3:R20C3 with Cells(30, 8), Cells(lastUEV, 9) I get an App... Read more

Answer:Solved: excel vb macro code for chart

10 more replies
Relevance 97.17%

I have a workbook that has 30 worksheets inside it. I need to be able to save each worksheet as an excel file with the tab name being the file name. I have been able to save them to PDF files but I can't seem to get it to work for the excel side. Thanks

Answer:Macro Code, Save as Excel Document

I image it'd be something like this:Dim s As Variant
For Each s In ActiveWorkbook.Sheets
s.SaveAs s.Name, ActiveWorkbook.FileFormat
Next 'sHow To Ask Questions The Smart Way

3 more replies
Relevance 97.17%

I have a massive excel spreadsheet containing 6 worksheets and need to hide certain rows throughout the workbook if certain cells in 'sheet1' are left blank. I also need excel to automatically hide the rows b/c other people will be using the workbook. I don't want them to have to hit "run macro" each time they modify the cells in 'sheet1'. Also, ALL the rows in worksheets 2 thru 6 contain formulas if that matters. Sheet1 does NOT contain any formulas.If 'Sheet1' B22 is left blank, then I need excel to hide the following rows:'Sheet2' row 11'Sheet4' row 10, 26,27,28,57,68,78,90,107,108,109'Sheet5' row 7'Sheet6' row 6,7,8,9,50,51,52,53If 'Sheet1' B23 is left blank, then I need excel to hide the following rows:'Sheet2' row 12'Sheet4' row 11, 29,30,31,58,69,79,91,110,111,112'Sheet5' row 7'Sheet6' row 6,7,8,9,50,51,52,53etc.I'm trying to make slight adjustments to the following macro to accomadate my worksheet but it's not working at all:Private Sub Worksheet_Change(ByVal Target As Range)Dim MyCells As Range'Which cells do you want to monitor for changes?Set MyCells = Range("A1:A42")'Check to see if the changed cell is one we care aboutIf Intersect(Target, MyCells) Is Nothing Then Exit SubIf Target.Count > 1 Then Exit Sub 'Don't do anything is multiple cell changes'Turn off system settingsApplication.EnableEvents = FalseApplication.ScreenUpdating = FalseWorksheets("Sheet1").Rows(22 + Target.Row).Hidden = Target.Value = ""Application.EnableEvents = TrueApplication.ScreenUpdating ... Read more

Answer:Excel Hide Rows If Macro Code

First, a posting tip for this forum:Please click on the blue line at the end of this post and read the instructions on how to post VBA code in this forum.As for your question, what is this line supposed to be doing?Worksheets("Sheet1").Rows(22 + Target.Row).Hidden = Target.Value = ""That is not an instruction that VBA will recognize and I don't see how it relates to your requirements. Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

3 more replies
Relevance 97.17%

Is there a code to where I can have a macro run every 5 seconds? If so, where would I place the code?
 

Answer:Solved: Excel Macro/Run Code Every 5 Seconds

Hi
here's how Ive done it. In this example C1will contain a single digit representing the # seconds so that I can control it from the sheet rather than editing the macro. If you just want to hard code the 5 seconds you can set Freq="00:00:05" or get rid of that line and edit the next one to

TimeToRun = Now + TimeValue("00:00:05")

There are 2 macros involved ....

the first sets the time period to run the routine you want and then "calls" that macro
the second is the routine you want to run every 5 seconds. The final line in this macro is to call the first macro to start the timer again
Code:

[B]Sub ScheduleCopyPriceOver[/B]()
Freq = "00:00:0" & Range("C1")
TimeToRun = Now + TimeValue(Freq)
Application.OnTime TimeToRun, "[B][COLOR=red]CopyPriceOver[/COLOR][/B]"
[B]End Sub[/B]
'
[B]Sub CopyPriceOver[/B]()
'+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
'Do what you need to do
'+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Call [B][COLOR=red]ScheduleCopyPriceOver[/COLOR][/B]
[B]End Sub[/B]
Finally, if you want to start the timer when the the file is opened you can add another macro like this
Code:
Sub auto_open()
Call ScheduleCopyPriceOver
End Sub
All of these macros can be in a normal module.
 

2 more replies
Relevance 97.17%

Hi all, hope someone can help.

I have a list of contracts, each run for 3 years exactly.
One starting 12/04/2007 ends: 11/04/2010.

I have the following code:

Code:

Sub Add3Years()
If IsDate(Range("B2")) Then
ActiveCell.FormulaR1C1 = DateAdd("yyyy", 3, Range("B2"))
ActiveCell.FormulaR1C1 = DateAdd("d", -1, ActiveCell.FormulaR1C1)
End If
End Sub

However, as you can see the initial range is as the specific cell B2. What I need is for the macro to see that I am on row 20, goto Column $B on row 20 and use that value instead of
Range("B2")Click to expand...

.

I've tried offsetting, but that doesn't really work, and I've seen
varRowNumber=Activecell.Row will return the row numberClick to expand...

If someone could help join the dots, that would be appreciated.
Thanks.
 

Answer:excel vba/macro: relative reference in code.

7 more replies
Relevance 97.17%

Hello,
I have the following bit of code as part of an Excel macro:

'In G1, enter formula to apply 6 character French code for AUT vehicles
ActiveWindow.ScrollColumn = 1
Application.Goto Reference:="R1C7"
Range("G1").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNUMBER(FIND(""AUTFORD"",RC[-6])),""FORD00"",IF(ISNUMBER(FIND(""AUTVAUX"",RC[-6])),""VAUXHA"",IF(ISNUMBER(FIND(""VNVF1"",RC[-6])),""NISSAN"",IF(ISNUMBER(FIND(""VF1"",RC[-6])),""RENAUL"",IF(ISBLANK(RC[-6]),"""",""MISC00"")))))"
Range("G1").Select

I'd like to be able to add more IFs to it, but there are already 7 conditions in there, which I believe is the max number of nested IFs allowed.
Specifically, at present, I want to add:

IF(ISNUMBER(FIND(""AUTNIS"",RC[-6])),""NISSAN""

But I may need to add more in the future.

Can someone help me write this in a better way? I'm no VB genius...
 

Answer:Excel 2003 macro - rewrite a little bit of VB Code

16 more replies
Relevance 97.17%

Hey,
Can someone tell me what I need to do to have a macro in excel, open another file?
For example, I need the following file opened:
c:\FolderA\FolderB\FileC.xls
How do I do this, and also, at the end of the macro, I want that file closed. Please help!
Thanks,
 

Answer:VBA Code to open file in Excel Macro

Give one of these lines of code a shot...
Code:

Workbooks.Open("Book2.xls")

or
Code:

Workbooks.Open("C:\MYFILES\BOOK2.XLS")

What are you going to do with the spreadsheet once opened?
 

2 more replies
Relevance 97.17%

.In a bridge tournament each team plays 8 rounds (eight other couples).

.the spread sheet uses column A to list all teams (from 1 to x). There could be

as many as 25 teams.

.After each round each teams score in entered in that rounds column .

For example after the first round each teams score is entered in column C







A B C D E F G H I J K



________________ROUND_____________



Team # Names 1 2 3 4 5 6 7 8 Total Score

1 joe-sandy 1650 1650

2 al-sally 980 980

3 tom-ann 1100 1100





When all scores are entered the spread sheet (score sheet) is sorted in

descending order by column K (total score of each team) and posted on a

Bulletin board. This shows every team their relative position (1 place, 2 place, etc.

to last place).

.This means that there will be 8 series of entering scores onto a master spread

sheet and 8 sorts showing every teams position after each round. (rounds 1,2,3,4,5,6,7 & 8(final))



The problem is that if the scoreke... Read more

More replies
Relevance 95.94%

Hey everyone,
So I used this code to copy a range of cells from multiple workbooks and paste it into one workbook. It used to work fine but now it gives me the 400 error. I've been adding files to the directory that it pull the files from and it was working fine, but all of a sudden it's causing this error. Can someone please help me out?

Dim xcell As Range
Dim ycell As Range
Dim sheetname As String
Dim wblist() As String
Dim i As Integer
Dim wbname As String
Dim j As Integer

i = 0
j = 0
FolderName = "C:\Documents and Settings\s.k\Desktop\CRs\LOG"
wbname = Dir(FolderName & "\" & "*.xls")

Application.ScreenUpdating = False

Do While wbname <> ""

i = i + 1
ReDim Preserve wblist(1 To i)
wblist(i) = wbname
wbname = Dir
Set ycell = Range(Cells(i + 3, 2), Cells(i + 2, 28))
Set xcell = Range(Cells(2, 3), Cells(2, 28))
sheetname = "loging form"

ycell.Formula = "=" & "'" & FolderName & "\[" & wblist(i) & "]" _
& sheetname & "'!" & xcell.Address
Loop

Do While j < 100
Cells(j + 3, 1).Select
ActiveCell.FormulaR1C1 = "=LEFT(RC[6],4)"
'ActiveCell.FormulaR1C1 = "=LEFT[RC[6],4]"

Cells(3 + j, 1) = Val(Cells(3 + j, 1))
Cells(3 + j, 2).Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-1],'[CR Status.xlsx]Sheet1'!R3C1:R189C3,3,FALSE)"

If Cells(3 + j, 1).Value = 0 Then
Cel... Read more

Answer:Excel VBA Macro Problem - Code used to work, now 400 error

7 more replies
Relevance 95.94%

Hi there!

So I've found the code below here: http://www.google.com/support/forum/p/Calendar/thread?tid=5e4909ca64cffe90&hl=en - Full credit goes to user 'tpuiatti'

Code:
Sub Generate_ICS()
Dim rng1 As Range, X, i As Long, v As Long
Dim objFSO, objFile
Dim FilePath As String
FilePath = "G:\Service.ics"
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.CreateTextFile(FilePath)
Set rng1 = Range([a5], Cells(Rows.Count, "H").End(xlUp))
X = rng1

objFile.write "BEGIN:VCALENDAR" & vbCrLf
For i = 1 To UBound(X, 1)
objFile.write "BEGIN:VEVENT" & vbCrLf & "DTSTART:" & Format(X(i, 2), "yyyymmdd") & vbCrLf & "DTEND:" & Format(X(i, 2), "yyyymmdd") & vbCrLf & "RRULE:FREQ=YEARLY" & _
vbCrLf & "SUMMARY:" & X(i, 1) & vbCrLf & "END:VEVENT" & vbCrLf
Next i
objFile.write "END:VCALENDAR"
End Sub

This code takes the info from 2 columns in Excel: A which is the event title and B which is the event date. Then it export these informations to an .ics file which will add the event dates to the Oulook Calendar.

The macro and the .ics work perfectly, however i wan't to make a few changes/additions to fit my needs. Additionally I wan't this macro to:

- Pick up dates from column &... Read more

Answer:Additional code to Excel macro - export to Outlook

7 more replies
Relevance 95.94%

In a database exported to Excel, a number of records come in for multiple sales people. Each row is a record. However the sales person's name in Column A is only shown once and for each of his records below that, the cells in column A are blank until the first record of the next salesperson, where his name is shown only once, then all his records are shown in subsequent rows, but cells below HIS name are blank (they do not repeat his name) until the first record of the next salesperson, and so on.

In order to sort these records I need to autofill the blank cells below EACH salesperson's name down to where the next salesperson's name first appears, and then autofill the next salesperson's name down to the next one, etc.

This step is part of a larger macro, but I have to end the macro there and do this manually because I can't figure out how to write the code to do it automatically. ??? Hope someone can help!

Thanks, Jim
 

Answer:Solved: Need Excel Macro Code to Autofill Column

12 more replies
Relevance 95.94%

Hi. I am trying to run a macro to copy items on 1 sheet and have it paste them on the 2nd and 3rd sheet. I am trying but can't quite seem to get the excel code to do what I want. I can't post a copy of it because I accidentally deleted it.

Can anyone help me?
 

Answer:Solved: Excel: Copy/Paste Macro Code Help

9 more replies
Relevance 95.94%

Sirs,I have a workbook in excel with 3 sheets. Sheet 1 ("COLUMNAR")ID DATE PAGE NO OR NO NAME OF PAYEE AMOUNT SECTION PARTICULARS 1 1/5/11 PAGE 1 0000701 JOEY BANTOG 26.00 DRY GOODS Sheet 2 ("SAMPLE") Page 1 Serial No. Amount(From - To) PayEE Acct. Code Description A B C D EI need to automatically transfer Serial No, Payee and amount to Sheet Named "Sample" which is the report form leaving Acct Code blank however using the Page No as the ref cell Sirs please help me my job is at stake with this report. Thanks

Answer:Macro Code to copy range of data in excel

Please repost your data after reading the How To referenced in the following line:Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

2 more replies
Relevance 95.94%

Hello Everyone
I'm a pro atlete (quite iliterate in Biz Apps) that based on previous post (from Zack Baresse and computerman29642) has unsuccessfully tried to set up a code to merge my next year training calendar (xls format) into outlook. I herewith included the code I'm using and the test xls file I'd love to merge. Any help would be really appreciated
Sub ExportAppointmentsToOutlook()
Dim olApp As Outlook.Application
Dim olApt As AppointmentItem
Dim blnCreated As Boolean

'Read the table with appointments:
Dim arrAppt() As Variant, i As Long
arrAppt = Range("A2", Cells(Rows.Count, "E").End(xlUp)).Value
On Error Resume Next
Set olApp = GetObject(, "Outlook.Application")
If olApp Is Nothing Then
Set olApp = CreateObject("Outlook.Application")
blnCreated = True
Err.Clear
Else
blnCreated = False
End If
On Error GoTo 0
'Create the outlook item for the table entries:
'Rows:
' Row 1 = Startdate
' Row 2 = Finishdate
' Row 3 = Subject
' Row 4 = Location
' Row 5 = Body
For i = LBound(arrAppt) To UBound(arrAppt)
Set olApt = olApp.CreateItem(olAppointmentItem)
With olApt

.StartDate = arrAppt(i, 1) + arrAppt(i, 2)
.Finishdate = arrAppt(i, 1) + arrAppt(i, 3)
.Subject = arrAppt(i, 4)
.Location = arrAppt(i, 5)
.Body = arrAppt(i, 6)
.BusyStatus = olBusy
.ReminderMinutesBeforeStart = 5
.ReminderSet = True
.AllDayEvent = True
.Save
End With
Next i

Set olApt = Nothing
Set olApp = Nothing
End Sub... Read more

More replies
Relevance 94.71%

Hi,I need an Excel Macro code for split the Cell contents and copy the split data into 2 or more number of lines.My Requirement. Sheet1:Column A2 : 1 Column B2 : Omega,M120,001Column C2 : External hard drive 2.0Column D2 : EachColumn E2 : New,New,NewColumn F2 : 250GB,500GB,1TBColumn G2 : ABC Tech, IT info, Tech ShopColumn H2 : 3yrs warrantyColumn I2 : 1QtyCondition verification : i have this raw data. here first i need to split the Column B2 into Three Column and then have to verify how many item in Column E2, that much i split into new row and same thing for Column F2 & G2 and remaining data should be copy the same.i need output like thisSheet2 :Column A2 : 1Column B2 : 1Column C2 : OmegaColumn D2 : M120Column E2 : 001Column F2 : External hard drive 2.0Column G2 : EachColumn H2 : NewColumn I2 : 250GBColumn J2 : ABC TechColumn K2 : 3yrs warrantyColumn L2 : 1QtyColumn A2 : 1Column B2 : 2Column C2 : OmegaColumn D2 : M120Column E2 : 001Column F2 : External hard drive 2.0Column G2 : EachColumn H2 : NewColumn I2 : 500GBColumn J2 : IT Info Column K2 : 3yrs warrantyColumn L2 : 1QtyColumn A2 : 1Column B2 : 3Column C2 : OmegaColumn D2 : M120Column E2 : 001Column F2 : External hard drive 2.0Column G2 : EachColumn H2 : NewColumn I2 : 1TBColumn J2 : Tech Shop Column K2 : 3yrs warrantyColumn L2 : 1QtyPlease help me anyone..Thanks & RegardsLJ

Answer:i need excel macro code for spliting cell data & copy

Let's start with getting the terminology right.You used the terms Column A2, Column B2, etc.A2, B2, etc. are Cell references, not Column references. A, B, etc. are Columns, 2,3, etc. are Rows, A2, B2, etc. are Cells.As for your question, the output in your example doesn't make sense.You have requested that all of your output go into Sheet2!A2:L2. In other words, you have requested that all 3 sets of data end up in the same row (2) in Columns A through L.That can't happen. They have to be in different rows or the last entry will overwrite the earlier ones.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

5 more replies
Relevance 94.71%

Hallo again! How are you keeping?

I have an Excel 2003 spreadsheet with data in columns A thru J and no header row. This will always be the same.
At present, I perform a Find & Replace on the data, to convert any commas into tildes. One of the columns sometimes contains address info, and as such can contain commas that need to be retained.

I then save the file as a *.csv (comma delimited) file.
Then I drag the csv file into another program (PSPad) which allows me to Find & Replace the commas (created by saving as a csv file) with little sqaures (by pressing Alt + 030).
I then convert any tildes back to commas and save the result as a text file which we can drop into our system to update a massive database.

I'm wondering if I really need this stage of converting to a csv in the middle - I'm sure the find & replace stuff can be done in VB code, and I can export directly from Excel into .txt format. I could do record a macro of myself doing it manually, but I'm not sure how to refer to the wee squares or export a .txt version of the file - i.e. would I just save as a *.txt (Text tab delimited) file or something?

Any help with such code would be greatly appreciated!
 

Answer:Excel 2003 macro code converting comma delimiters

9 more replies
Relevance 86.51%

I'm trying to run an Excel 2007 macro from Access 2007, when I get to the point where I want the macro stored in the active workbook I'm getting the following run-time error: run-time error '91' object variable or with block variable not set . It won't run the macro at all. I would greatly appreciate it if someone could help me fix my code so the macro will run. The code is listed below; step 10 is where I get the error.
Code:
Option Compare Database
Sub GetJournal_Entry_Data_transfer_to_Excel()
'Step 1: Declare your Variables
Dim MyConnect As String
Dim MyRecordset As ADODB.Recordset
Dim MyQueryDef As DAO.QueryDef
Dim MyDatabase As DAO.Database
Dim MySQL As String
Dim MyRange As String
Dim s As String


Dim Db As Database
Dim xl As Excel.Application
Set xl = CreateObject("Excel.Application")
Dim xlwkbk As Excel.Workbook
'Dim xlworkbk As Excel.Workbook
Dim xlsheet As Excel.Worksheet
Dim i As Integer


'Step 2:Declare your connection string
MyConnect = "Provider=Microsoft.ACE.OLEDB.12.0; Persist Security Info = False;Data Source= P:\FINANCE\Balance Sheet\Inventory\Project TAN\Project TAN.accdb; User ID = Admin;"

'Step 3: Build Your SQL Statement
MySQL = "Select* From [mtb-TantasticJE's]Where [mtb-TantasticJE's].[Dscrptn_Text]='Culls_Stat34'and [mtb-TantasticJE's].[Co_Code]='1381'"

'Step 4: Instantiate and specify your recordset
... Read more

Answer:Access 2007 VB code to run Excel 2007 macro in active sheet

7 more replies
Relevance 81.59%

How do you keep a macro running until you reach the end of a page? Currently I just hold down the control or Alt keys. I have long documents so holding down the Alt + whatever key is not always practical.

Here is a sample macro. How do I make the macro run until the macro reaches (in this case) the top of the page?

I think the "do until" feature is used in the code but I do not know the syntax for "do until".

Code:
Sub DelimitBoldSentenceOnRightWithATab()
Selection.Find.ClearFormatting
Selection.Find.Font.Bold = True
With Selection.Find
.Text = "*"
.Replacement.Text = ""
.Forward = False
.Wrap = wdFindContinue
.Format = True
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = True
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Selection.Find.Execute
Selection.TypeText Text:=vbTab & vbTab
Selection.MoveUp Unit:=wdParagraph, Count:=1, Extend:=wdExtend
Selection.MoveLeft Unit:=wdCharacter, Count:=1
Selection.TypeText Text:=vbTab & vbTab
Selection.MoveLeft Unit:=wdCharacter, Count:=3
Selection.Find.ClearFormatting

End Sub

Thank you in advance for your replies.
 

Answer:Word Macro: How to keep macro running until end of page is reached

You can evaluate the current page number using the following property:

Selection.Information(wdActiveEndPageNumber)

Just use a loop and have it execute while Selection.Information(wdActiveEndPageNumber) = 1

Can you post your sample document?
Regards,
Rollin
 

2 more replies
Relevance 81.59%

Hi Im an amatuer photographer and i'm looking to dupe my heap of old 35mm slides using my canon 180mm 3.5f macro lens... I'm wondering if anyone has tried doing so with a macro lens similar ..
I use a 40d digital canon camera. The shortest focusing distance for the lens is just under half a metre.
I also have a 1.4 extender that gives extra magnification but dont think i would need it.
I imaging the idea is to photograph down a piece of pipe (ie fit it to the lens somehow) and have the 35mm slide illuminated from the rear using some sort of slide holder and external flash behind....
The macro lens will do 1to1 life size is made to shoot a fairly flat field and the camera is capable of automatic exposure and bracketing... does that sound like a lot of rot ? or is it a proposition... I havnt seen a commercially made gadget to use on a dslr for this purpose. I copuld buy a dedicated dupe machine but im wondering if the optics and software are any better than the camera i've got?
Maybe someone is already doing it! I'm just new to this forum so a bit inexperienced in this process.... Regards to all
jim t
 

Answer:anyone tried duplicating 35mm slide via macro a macro lens

12 more replies
Relevance 79.95%

I have set up a macro to print multiple reports. Using the following:
OpenReport
PrintOut
OpenReport
PrintOut etc.

I get a Print Macro Definition message between printing each report that I cannot make go away.

Please, make it go away!!!
 

More replies
Relevance 79.95%

Hello!

I've been tasked to compile in a sheet the monthly ending balances (from July to Dec) of customer receivables for 493 CUSTOMERS. That's a lot! I already started, but I know continuing until the end is crazy and even impossible. So please help?

Below is a picture of the first sheet containing the ledger data of the 493 customers dated July to December. I just blocked the center and changed the customer names in an attempt to maintain confidentiality.

As an example, for Customer 1, I need the July, Aug, Sept, Oct, Nov, and Dec ending BALANCES (last column) of the receivables.

(I ALSO ATTACHED FILES OF THE FF 2 PHOTOS WHICH I UPLOADED IN FLICKR JUST IN CASE)

For july 31, since the first entry is already 08.15 or August 15, this implies that the July balance must be 0 which I have to input into the July 31 column in the second sheet as shown in the second picture.

For Customer 1 August 31 2012, the closest date to August 31/8.31 entry indicates a balance of 0 so this must be the August 31 balance.

For customer 1 Sept 31 2012 to November 2012, no entries are inputted for these months (9-11) so the August 31 balance mustn't have changed and is the standing balance as of November 31.

For Customer 1 December 31 2012, the outstanding balance is 0.

Oh, as an added note, just in case, debit adds to the balance and credit deducts from it.

I've had to manually mine the ending balances for 90+ customers so far. HUHUHU. I just know macros and YOU wil... Read more

Answer:Macro Help: Macro for Copying Certain Values Using Certain Criteria

It will be easier if you make sheet1 like a database table.
That is
Column A is all customer name with no blank rows.
Column B is the invoice date
Debit and Credit can be in 2 col or 1 col.

Try not to "beautify" your source data with empty rows and columns, it makes summarisation difficult.

When all is done, you may use sumif to sum all values before a particular date.
 

1 more replies
Relevance 79.95%

hello everyone,

i would like to ask for help,

i already made a macro for mac excel 2011, the macro is only move and copy an active worksheet to the last position of sheets.

however, i found something wierd, suddenly there is code inside the new sheet module. the code is

'True
End

this code makes me un able to run macro anymore, but after i remove that code, i can run again the macro.

could any one help me regarding this problem?

Thankss...

the code i was using as follow

sub test123()

activesheet.copy after:= sheets(sheets.count)

End Sub
 

Answer:un able run macro in 2011 after adding new sheet by macro

Are you sure thes isn't any code in the source sheet?
 

3 more replies
Relevance 79.95%

Hi, I'm creating a very basic macro to include headers and footers in an excel workbook. I want this to be used by any open file so I'm storing it in the Personal Macro Workbook. If I type in text to be displayed like my company's name, its fine, but if I use the fields which suppose to pull the data from the file, like page number or file name, When I run macro I do not see it. I can see the typed text but not the page number or file name. When I go to the VBA window the coding for the field is there but its not showing in my file. Macros are also enabled in my options box, so can anyone tell me what is causing this?,

Answer:Macro problems in the Personal Macro Workbook

Since we can't see your workbook or macro from where we're sitting, it's a little tough for us to offer a solution.We need more info.Please click on the following line, read the instructions found via that link and then post whatever you think we'll need in order to help you.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

2 more replies
Relevance 79.54%
Question: Macro COde Help

Hi,
I am trying to write a macro code to swap 2 columns.I have a code taken from the internet but it is quite complicated & im having trouble in executing it.The code that im talking is in this link : http://www.mvps.org/dmcritchie/excel/swap.htm
This is only a reference so that noone sends me the same code.
Could anyone help me with a simple code?
Thanks.
Regards,
Cali
 

Answer:Macro COde Help

That link also tells you how to swap columns manually. If you're having trouble getting the code to execute, you could record a macro while doing it manually. Click the "record" button on the toolbar or go to Tools/Macro/Record New Macro and then perform the swap manually. Hit "stop" when you're done. You can name the macro and have it available whenever you need it. Hope that helps.
 

1 more replies
Relevance 79.54%
Question: VBA - macro code

hiI would like a macro where I have an amount of hours of 459.50 in column B and FTE 12.25 in column C. FTE is calculated by 459.50/37.5.I want a list to be created per staff showing 37.5 in column B on all the lines and 1 in column C in all the lines until 37.50* 1 = 459.50. There will be 12 staff at 1 FTE and 1 staff at 0.25. so 13 staff in total.Please can someone help in creating a code.many thanks

More replies
Relevance 79.54%

Hi Guys,

The following code is what I use to copy rows from my excel report depending on the outcome of a particular colum to another sheet. However what I would like to know is instead of just copying the rows over, I want to move the rows completly out of the "Main" Sheet to the specified sheet.

Code as follows:

Dim Mfile As String
Dim Dsh As String
Dim Rend As Long
Dim Dd As Long
Dim Nend As Long

Dsh = "Main"
Mfile = ActiveWorkbook.Name

Workbooks(Mfile).Activate
Sheets(Dsh).Select
Rend = Cells(65536, 1).End(xlUp).Row

For Dd = 1 To Rend

Select Case Cells(Dd, 6)
Case "1"
Nend = Sheets("Missing1").Cells(65536, 1).End(xlUp).Row + 1
Rows(Dd).Copy Destination:=Sheets("Missing1").Cells(Nend, 1)

End Select
Next Dd

Sheets("Sheet2").Select
Columns("F:F").Select
Selection.ClearContents
Range("A1").Select

End Sub

Now this part of my code is used to copy the lines:

Select Case Cells(Dd, 6)
Case "1"
Nend = Sheets("Missing1").Cells(65536, 1).End(xlUp).Row + 1
Rows(Dd).Copy Destination:=Sheets("Missing1").Cells(Nend, 1)

But what I want to know is there away to change the "Copy Destination" to a move function?

Hope I have giving enough info.

Cheers

Colin
 

Answer:Macro/VBA code help

10 more replies
Relevance 79.54%
Question: Macro code

I want to be sure that a spreadsheet being used for a pivot table is all inclusive when I update the pivot table. The spreadsheet range will change day to day. Looking at the code I have now which was obtained through recording a macro I am not sure that each day all the data is being captured. How do I expand the range so I don't have to worry about how many rows my spreadsheet is each day?
 

Answer:Macro code

7 more replies
Relevance 79.54%
Question: VBA - macro code

hiI would like a macro where I have an amount of hours of 459.50 in column B and FTE 12.25 in column C. FTE is calculated by 459.50/37.5.I want a list to be created per staff showing 37.5 in column B on all the lines and 1 in column C in all the lines until 37.50* 1 = 459.50. There will be 12 staff at 1 FTE and 1 staff at 0.25. so 13 staff in total.Please can someone help in creating a code.many thanks

More replies
Relevance 79.54%
Question: Macro Code

Hi,
I am trying to write a macro code which inserts input buttons like Plates,Sequence Number etc(these are just the names of the buttons).When I click any one button,a graph must be plotted based on the values taken from xl sheet that has a table of 8 rows & 12 columns.
Kindly help.
 

More replies
Relevance 79.54%

Im using Windows 7 and I used a macro which worked totally fine under Excel 2003. Now, under Excel 2007 it does not run anymore but displays the following error message:

"Run-time error '445':
Object doesn't support this action.

The function of the macro is to open up all other Excel spreadsheets located in the same folder and copy data out of them into the spreadsheet in which the macro is stored.

When I click on "Debug" the Visual Basic Editor highlights the row which says: Set FilSrch = Application.FileSearch

Can you help me with this? I've searched for a solution for a long time but could not find anything which worked.

The following is the start of the macro code:

Sub UpdateTable()
Dim X As Range, I As Integer, J As Integer, RecNo As Integer, CopyVal As Variant
Dim SourceBk As Worksheet, DestBk As Worksheet, StartRow As Integer, IndRange As Range
Dim ServRange As Range, SrcOpen As Boolean, SourceName As String
Dim FilSrch As Object, MyFilArray() As String

'MsgBox "This function not available in this version.", vbInformation, "Function Not Available"
'Exit Sub
'Sheets("LookUp").[a12] = Now
With Application
.ScreenUpdating = False
.StatusBar = "Counting source files ..."
End With
Set FilSrch = Application.FileSearch
With FilSrch
.NewSearch
.LookIn = ActiveWorkbook.Path
.FileType = msoFileTypeExcelWorkbooks
If .Execute > 0 Then
ReDim MyFilArray(.FoundFiles.Count)
For I = 1 ... Read more

Answer:Solved: Excel 2003 Macro Doesn't Run in Excel 2007 - Help Requested

7 more replies
Relevance 79.54%

Hello again,

I have a word doc that has a form in it. The form may have any number of fields (boxes) to type text answers into, or be a drop down list to select an answer from. I need an excel macro that extracts all data from the form, but with every five fields starting a new row in excel. So all data from the form will appear within 5 columns in an excel spreadsheet.

field 1 field 2 field 3 field 4 field 5
field 6 field 7 field 8 field 9 field 10
etc

Is that possible? I have had no luck to do it myself.

Sample form attached, thank you
____________________________
 

Answer:Solved: Excel macro to extract Word form data into Excel

7 more replies
Relevance 79.54%

Hi,

I was wondering if anyone could help me. I need to extract data from multiple excel files into a new excel file and sum the values. The data comes from excel files that are made from a template so the data is always in the same place or same cells. For example, i have data saved daily in excel files with the following naming convention 080204, 080304, 080404,..... and so on. What i would like to do is have a macro that will create a week ending sheet by importing and summing the data from that week.

Thanks for reading!
 

Answer:excel macro for copying and pasting data from multiple excel files

thanks for all those who read!!!! let me know if anyone needs the code!
 

1 more replies
Relevance 79.54%

I am trying to create a code that will autofilter data on an Excel worksheet. The code works fine to filter the data, but it does not hide the AutoFilter Arrows.

Below is the code I am using. What (if anything) is wrong with the code below?
Code:

ActiveSheet.Range("A1:D500").AutoFilter Field:=2, Criteria1:="<>", Visibledropdown:=False
 

Answer:Solved: Excel 2007 Macro -> Hide Excel AutoFilter Arrows

I noticed that the code above (in Post #1) that the AutoFilter Arrows were beinf truned off on Field 2, but none of the others.

I have played around, and done some research to come up with the code below:
Code:

Dim c As Range

For Each c In ActiveSheet.Range("A1:D1")
ActiveSheet.Range("A1:D500").AutoFilter Field:=2, Criteria1:="<>"
c.AutoFilter Field:=c.Column, Visibledropdown:=False
Next

Does anyone have a better solution?
 

1 more replies
Relevance 79.13%

Hello,

I need help to create an Excel macro that would

1. ask users to select x number of rows to be copied from one worksheet of one Excel file
2. once users have selected the rows to be copied, then the rows are copied from the one original Excel file into X number of target Excel files

Notes:
the target Excel files are all based on the same template
there is one worksheet in each of the target Excel files

in the target files, the rows should be copied from the first available empty row, going down

Looking forward to your help!

Thanks a lot.
Mzz
 

Answer:Macro needed to copy x number of rows from one Excel file into a batch of Excel files

Hi, welcome to the board.
Not much info there to get the correct picture.
Sample? Of source and template please
And not to forget, what version of Excel are you and the users using?
 

1 more replies
Relevance 79.13%

Hi,
I want to merge two excel files into a 3rd one.

The data are in the files Book1, Book2 and the result in Book3.

I know that i can use VLookup to do the above. But what i have pasted is just an example. The real excel files contains about 9 columns with at many repeating row!!!

Any help will be much appreciated.
Thx
 

Answer:Excel - macro to merge 2 excel files using a common column

Hi welcome to the board. How are you going to treat the duble items, If you want to merge 2 files adn duplication occurs here must be one cell whihc is common to let's say Book1 and Book2.

Do you have a common value? Index? Whatever you call it?
 

3 more replies
Relevance 78.31%

Hi all

i was working on a macro code to copy Latin text values like font name, size and style into complex scripts panel as shown below



and i recorded macro for that but i have to select every text and then apply macro, i just need adjustment to my macro to make it select all text and apply that change


Quote:





Sub Macro2()
'
' Macro2 Macro
'
'
With Selection.Font
.Name = .NameBi
.Size = .SizeBi
.Bold = .BoldBi
.Italic = .ItalicBi

End With
End Sub





Thanks

More replies
Relevance 78.31%

Excel MacrosI am trying to create a macro for Excel 2010 that will save each Excel worksheet, within a workbook, to be save as a PDF and the file name be the tab name. Does anyone know if this is possible? Thanks

Answer:Macro Code, save as PDF

This seemed to work.Some notes:- You'll have to change the path to match where you want the PDF's saved.- You can change OpenAfterPublish:=True to False if you don't want to see each PDF after it's created.- The code will overwrite a file with the same name without asking.You have been warned! ;-)Sub CreatePDF()
For sh = 1 To Sheets.Count
Sheets(sh).ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"C:\Documents and Settings\username\Desktop\" & Sheets(sh).Name & ".pdf", _
Quality:=xlQualityStandard, IncludeDocProperties:=True, _
IgnorePrintAreas:=False, OpenAfterPublish:=False
Next
End SubClick Here Before Posting Data or VBA Code ---> How To Post Data or Code.

10 more replies
Relevance 78.31%

Hi I am trying to create a macro that once I copy an email into a worksheet it takes that data and brings it to another worksheet within the same excel file. I am able to do this by recording a macro and copying and pasting the formula BUT when I go to run the next days data the macro replaces the old data with the new data. I want the new data to go to the next line. What is the macro code for doing this?!?!?

Answer:need macro code for data

A line similar to this will return the Row number of the last cell that contains data in e.g. Column A:lastRw = Range("A" & Rows.Count).End(xlup).Row The next empty Row would be:nextRw = Range("A" & Rows.Count).End(xlup).Row + 1Therefore, you might want to paste your data using one of these, depending on which of the above lines you choose to use:Range("A" & lastRw + 1)

or

Range("A" & nextRw)Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

7 more replies
Relevance 78.31%

Hi,
I would like to protect my macros codes by a macro - after a certain time.
I know I can protect it before, but I want to do it after a certain time...
The reason is when the user clicks on a button, so the document is finalised and it's ready to be sent to an external client.

I tried to delete the macros, but when I write the code, my anti virus dects a virus and it will not work!
Here is what I've put:
Application.OrganizerDelete Source:=ActiveDocument.FullName _
, Name:="AutoClose", Object:=wdOrganizerObjectProjectItems

Any ideas what / how to do?

Thanks,

Fred
 

More replies
Relevance 78.31%

Hello! Can anyone help me speed my code up? I need to reference One workbook to another huge workbook which consists of 1000's and 1000's of columns/rows. My macro does what it needs to do but yet is taking hours to load. Any ideas?Private Sub CommandButton1_Click()Dim counter As IntegerDim openedWorkbook As WorkbookDim sourceSheet As WorksheetDim strFileToOpen As StringDim continue As BooleanDim result1 As StringDim result2 As StringDim result3 As StringDim x As StringConst sourceStartRow = 5Const activeSheetStartRow = 2strFileToOpen = Application.GetOpenFilenamecontinue = FalseIf strFileToOpen <> "" Then Set sourceSheet = Application.ActiveSheet Set openedWorkbook = Workbooks.Open(strFileToOpen) continue = TrueEnd IfIf continue Then counter = 0 With openedWorkbook.Sheets(1) For i = sourceStartRow To .UsedRange.Rows.Count result1 = getReturnValue(openedWorkbook.Sheets(1), .Cells(i, 2), 2, 15, sourceStartRow - 1) result2 = getReturnValue(openedWorkbook.Sheets(1), .Cells(i, 2), 2, 14, sourceStartRow - 1) result3 = getReturnValue(openedWorkbook.Sheets(1), .Cells(i, 2), 2, 3, sourceStartRow - 1) If sourceSheet.Cells(activeSheetStartRow + counter, 1) = result1 Then sourceSheet.Cells(activeSheetStartRow + counter, 1) = result1 sourceSheet.Cells(activeSheetStartRow + counter, 2) = result2 sourceSheet.Cells(activeSheetStartRow + counter, 3... Read more

More replies
Relevance 78.31%

Hi.

Im trying to "grap" the filename and path of "doc 1" and paste the contents of "doc 1" into "doc2" then close "doc 1" save the file with the path and filename information from "doc 1".

Is this possible?

Jon
 

Answer:VB Macro code for Word

7 more replies
Relevance 78.31%

A B C 1Internet. 2. 1-1-1 3. 1-1-1 Red4. 1-1-1 White5. 1-1-1 Blue6.Total Internet7..Phone 8. 1-1-19. 1-1-1 Miami10. 1-1-1 Jersey11. 1-1-1 Arizona12. 1-1-1 Illinois13.Total PhoneI need a serioous code to:(1).For an empty cell in Column C between the rows with Internet and Total Internet In Column A to be filled with the word "Black".(2) If those same rows do not have Black, White or Blue in Column C to change it to the word "Color". In my example "Red" would be changed to "Color" .(3) I will apply a similar code to rows with Phone and Total Phone in Column A. I will jsut teak the code.

Answer:Need a serous MACRO code

re: I need a serioous code...Everyone has needs.Some people just tell us about them, others actually ask for assitance.Which group are you in?

20 more replies
Relevance 78.31%

Hi, I have created an excel macro that runs sensitivities in my model. The excel button runs 5 different cases.

The macro runs fine and does as I want it to.

Once the macro has run, and I go to edit a cell and hit "F2" excel crashes. This only seems to happen in certain cells and not all of the time.

Does anyone have experience with similar questions?

Thanks
 

Answer:Excel Macro runs fine..then excel crashes

11 more replies
Relevance 78.31%

I used a macro which worked totally fine under Excel 2003. Now, under Excel 2007 it does not run anymore but displays the following error message:"Run-time error '445':Object doesn't support this action.The function of the macro is to open up all other Excel spreadsheets located in the same folder and copy data out of them into the spreadsheet in which the macro is stored.When I click on "Debug" the Visual Basic Editor highlights the row which says: Set FilSrch = Application.FileSearchCan you help me with this? I've searched for a solution for a long time but could not find anything which worked.The following is the start of the macro code:Sub UpdateTable()Dim X As Range, I As Integer, J As Integer, RecNo As Integer, CopyVal As VariantDim SourceBk As Worksheet, DestBk As Worksheet, StartRow As Integer, IndRange As RangeDim ServRange As Range, SrcOpen As Boolean, SourceName As StringDim FilSrch As Object, MyFilArray() As String'MsgBox "This function not available in this version.", vbInformation, "Function Not Available"'Exit Sub'Sheets("LookUp").[a12] = NowWith Application .ScreenUpdating = False .StatusBar = "Counting source files ..."End WithSet FilSrch = Application.FileSearchWith FilSrch .NewSearch .LookIn = ActiveWorkbook.Path .FileType = msoFileTypeExcelWorkbooks If .Execute > 0 Then ReDim MyFilArray(.FoundFiles.Count) For I = 1 To .FoundFiles.Count If .FoundFiles(I) <> ActiveWorkbook.Path & "\" & ActiveWorkbook.Name Then ... Read more

Answer:Excel 2003 Macro doesn't run under Excel 2007

As far as I know, Application.FileSearch is not available in 2007. Searching for filesearch replacement turns up lots of hits with various workarounds, such as this:http://groups.google.com/group/micr...Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

2 more replies
Relevance 78.31%

With due respect and credit to DerbyDad03 whose earlier post gave me the code below; is there any way to have the new worksheets created from a variable length field e.g. a persons name in a list or a field of fixed length other than a date as the script below uses?Sub CreateMonthlySheets()Dim lastRow, mMonth, tstDate1, tstDate2, shtName, nxtRowOn Error Resume Next'Turn off ScreenUpdatingApplication.ScreenUpdating = False'Make a copy of the data sheet and sort by date Sheets("Sheet1").Copy After:=Sheets(1) Sheets(2).Name = "SortTemp" With Sheets("SortTemp") lastRow = .Cells(Rows.Count, 1).End(xlUp).Row Rows("2:" & lastRow).Sort Key1:=Range("A2"), Order1:=xlAscending 'Using SortTemp Sheet, create monthly sheets by'testing Month and Year values in Column A'Loop through dates For Each mMonth In .Range("A2:A" & lastRow) tstDate1 = Month(mMonth) & Year(mMonth) tstDate2 = Month(mMonth.Offset(-1, 0)) & Year(mMonth.Offset(-1, 0)) 'If Month and Year are different than cell above, create new sheet If tstDate1 <> tstDate2 Then ActiveWorkbook.Sheets.Add After:=Worksheets(Worksheets.Count) 'Name the sheet based on the Month and Year ActiveSheet.Name = MonthName(Month(mMonth)) & " " & Year(mMonth)'Copy Column Widths and Header Row .Rows(1).Copy ActiveSheet.Rows(1).PasteSpecial Paste:=8 'ColumnWidth ActiveSheet.Rows(1).PasteSpecial 'Data... Read more

Answer:Excel Macro to create new worksheet in Excel 2010

I created a quick macro to help you out. This macro creates a new sheet and names it using the contents of cell "A1".Sub CreateNewSheet()
Dim NewSheet
Dim SheetName As String

SheetName = Worksheets("Sheet1").Cells(1, "A").Value
Set NewSheet = Sheets.Add(After:=Sheets(Worksheets.Count), Count:=1, Type:=xlWorksheet)
NewSheet.Name = SheetName

End Sub
Law if Logical Argument: Anything is possible if you don't know what you're talking about.

4 more replies
Relevance 78.31%

Hi guys,

I have been on the lookout for a macro that will compare 2 worksheets within MS Excel and output the differences on a third sheet.

I am being directed to the URL below but cannot for the life of me work out how to implement this:

http://exceltip.com/st/Compare_two_worksheets_using_VBA_in_Microsoft_Excel/477.html

There are two snippets of code which I will include here for convenience but not sure whether these should be pasted into Workbook Code (i.e. right-clicking Excel icon left of "File" - top-left), Worksheet Code (i.e. right-clicking worksheet tab) or inserting a new Module - or some other method. Is there anyone here that could guide me to how to implement this code?

Quote from webpage - "With the macro below it is possible to compare the content of two worksheets.
The result is displayed in a new workbook listing all cell differences. "

Code:
Sub CompareWorksheets(ws1 As Worksheet, ws2 As Worksheet)
Dim r As Long, c As Integer
Dim lr1 As Long, lr2 As Long, lc1 As Integer, lc2 As Integer
Dim maxR As Long, maxC As Integer, cf1 As String, cf2 As String
Dim rptWB As Workbook, DiffCount As Long
Application.ScreenUpdating = False
Application.StatusBar = "Creating the report..."
Set rptWB = Workbooks.Add
Application.DisplayAlerts = False
While Worksheets.Count > 1
Worksheets(2).Delete
Wend
Application.DisplayAlerts = True
With ws1.UsedRange
lr1 = .Rows.Count
lc1 = .Column... Read more

Answer:Solved: How to use this Macro in MS Excel (compare excel worksheets)

Apologies... I have sorted this... For those that might be looking at this for help here's what I missed:

These are both Sub's that should be created as a Module - both in the same module. I am just comparing worksheets within the Active Worksheet so just removed:

' compare two different worksheets in two different workbooks
CompareWorksheets ActiveWorkbook.Worksheets("Sheet1"), _
Workbooks("WorkBookName.xls").Worksheets("Sheet2")
Click to expand...

I was then able to run this from Macro's. All sorted.

Apologies again
 

1 more replies
Relevance 78.31%

Does anyone know how to run Excel 2003 print macro on Excel 2007 or higher? When I tried running my 2003 macro, the VBA editor pops up w dialogue box, " Run-time error '1004': Method 'ActivePrinter' of object'_Application' failed ".

This is my code: Application.ActivePrinter = "Epson LQ-300 ESC/P 2 on USB003"

Can anyone help?

Thanks!
 

Answer:How to run Excel 2003 print macro on Excel 2007?

Welcome to TSG hope we can help you and you can help others.

I'm not an Excel macro guy but you code looks reasonable. What happens if you run this. I suspect printer name is a little off.

MsgBox "The name of the active printer is " & Application.ActivePrinter
 

1 more replies
Relevance 78.31%

I run a weekly excel 2007 macro that converts a spreadsheet to a Word document, but it stops after row 29 of data. Any spreadsheet with 29 rows or less works fine. If I have 34 rows of data, the macro hangs. See below for script. Any help would be appreciated.

Public Sub AddControls(WrdApp As Word.Application, CurRow As Integer)
Dim OptChecked As Boolean
Dim GrpName As String
GrpName = "Grp" & CurRow
'Calculate the colors based on the cell information.
CurRange = "F" & CurRow & ".." & "F" & CurRow
Range(CurRange).Select

If InStr(1, UCase(ActiveCell.Text), "X SRM") > 0 Then 'SRM
OptChecked = True
Else
OptChecked = False
End If

SetCellBG WrdApp, True
WrdApp.Selection.TypeText Text:="Governance:" & vbCrLf

Set RadioObj = WrdApp.Selection.InlineShapes.AddOLEControl("Forms.OptionButton.1")
With RadioObj.OLEFormat.Object
.GroupName = GrpName
.Caption = "SRM"
.Font.Name = "Arial"
.Font.Size = 8
.Value = OptChecked
End With

If InStr(1, UCase(ActiveCell.Text), "X PPO") > 0 Then ' PPO
OptChecked = True
Else
OptChecked = False
End If

WrdApp.Selection.Collapse Direction:=wdCollapseEnd
WrdApp.Selection.TypeParagraph

Set RadioObj = WrdApp.Selection.InlineShapes.AddOLEControl("Forms.OptionButton.1")
With RadioObj.OLEFormat.Object
.GroupName = GrpName
.Caption = "PPO"
.Font.Name = "Arial"
.Font.Size = 8
.Value = O... Read more

Answer:Excel Macro - converting excel to word doc - stops after row 29

13 more replies
Relevance 77.49%

I'm using a piece of software that automatically sends data from a register in a PLC straight into Excel, all you have to do is reference the program as below:

=cslite|net1_Node1!'R345'

'CSlite' is the name of the software and 'net1_node1!' is the PLC, this part never changes, I just change the R345 to whatever register I want.

I hope this makes sense so far?!

The register value changes every second so the excel document is constantly changing when left alone! What I want to do is get the value copy it to the next line and then insert a line ready for the next value. I've managed to get the copy and insert parts working but I need it to loop round and keep going whenever the value changes, basically creating a self updating table.

Any ideas how I can do this? I'm not too hot on VB and the functions I've tried seem to either create an infinite loop or just don't work!

Any help gratefully recieved.

Mike.
 

Answer:Excel Macro Help please!

Here is my suggestion:

I would keep your original formula =cslite|net1_Node1!'R345' in a particular cell such as cell A1. Now take the first value you see for this formula and manually enter it into cell A3 (this is to manually create a blank row between your dynamic cell A1 and your static list which begins in cell A3) Next add a macro to the the Worksheet_Calculate event. This macro will fire everytime the value in A1 changes. The macro would copy the current value from cell A1 and place it in the next available cell below. In this case, the next available cell would be cell A4.

Right click your worksheet name tab and select VIEW CODE and then copy and paste the code below into the worksheet module. BTW....how often does the data change and how big do you expect this table to get. I'm asking because you'll run out of rows sooner or later.

Code:


Private Sub Worksheet_Calculate()

Range("A65536").End(xlUp).Offset(1, 0).Value = Range("A1").Value


End Sub



Rollin
 

2 more replies
Relevance 77.49%

Hi,
I need some somewhat simple excel macro/scripting help.

Say I export a report from our database program into excel, basically, it is a 100+ page report with headings on each page. When we export to Excel, it shows the headings throughout. It is something like:

:BLANK SPACE:
:BLANK SPACE:
:BLANK SPACE:
Database: XYZ PAGE 2
USER ID: BLAH
:BLANK SPACE:
:BLANK SPACE:
:BLANK SPACE:

:Actual Data:

:BLANK SPACE:
:BLANK SPACE:
:BLANK SPACE:
Database: XYZ PAGE 3
USER ID: BLAH
:BLANK SPACE:
:BLANK SPACE:
:BLANK SPACE:

Etc.

What's an easy way to strip out that information so we have have concise spreadsheet? My guess, is: Search for the word "Database" and delete 4 lines above it and 5 lines below it.

Also,

When the report is exported to Excel, all the font formatting is stripped. So if we have lines like:

Office Supplies $300 (This is a total line, but doesn't state the word total)
Pens $100
Paper $200

Is there a way to search for a specific string, say I insert the word "total" to the certain lines, so it searches for that word "total" and it bolds entire land? Thanks for the help in advance, you guys rock!
 

Answer:More Excel/Macro help :)

16 more replies
Relevance 77.49%
Question: excel macro

Hello computer people,

I am a graduate student in microbiology and I need some help writing a macro in excel. My data (volt and current information) are collected every minute for 36 hours. I want to take the average of these values for hourly increments. I have attached a txt file where I tried to demonstrate what I was trying to do. Any help or suggestions would be appreciated as I am completely lost...

So I tried to create something like =average D(valueG2)(valueH2), so that I can calculate these ranges over the hourly increments. Ideally I would be able to do this for every hourly increment. For example-

For hours 0-1
the value in column g2 (or on the txt file 'value1')= 2
h2 ( 'value2')=61
I want the average D261 (voltage)calculated for mean volts

For hours 1-2:
D1= 'value 1'=62
D2= 'value 2'=121
average D62121
 

More replies
Relevance 77.49%
Question: Excel Macro Help

Hi I am wondering if someone could help me with an excel macro.

Basically I want to search a worksheet between two columns to see if t shirts exists between shorts & jeans and to exit if it does or if not insert a blank column.

Thanks Jumbo
 

Answer:Excel Macro Help

Jumbo , do you want to select T shirts from the excel sheet?
Just press ctrl and F,then type "T shirts ",you will get the results.
 

3 more replies
Relevance 77.49%
Question: excel macro

Hi,
Everyday, I get a file with a list of items and thier prices.How would i create a macro in EXCEL to automatically copy the same data to four regions. This can be anywhere in the sheet and the item and price need to be in the same columns.For example:
item# price
5555 10.00
6666 20.00
7777 30.00
8888 40.00

I would have to do the same with regions 1,2,3,and 4
item# region price
5555 1 10.00
6666 1 20.00
7777 1 30.00
8888 1 40.00
5555 2 10.00
6666 2 20.00
7777 2 30.00
8888 2 40.00
5555 3 10.00
6666 3 20.00
7777 3 30.00
8888 3 40.00
5555 4 10.00
6666 4 20.00
7777 4 30.00
8888 4 40.00
 

Answer:excel macro

6 more replies
Relevance 77.49%

I'm trying to update (& create) some macros in Excel (for WinXP) and all of a sudden it's using absolute values instead of relative values. Fine, okay, I select the relative value button and try again, but now the macro is skipping a column between each entry, when it should just be going to the next column. I'm at my wit's end and I need to get this fixed NOW. Help!!!!
 

Answer:Excel Macro - Help!

13 more replies
Relevance 77.49%
Question: excel: macro help

hello, i got a little problem with my macro i wrote... simply put, i wrote a macro to parse through data from a transportation program and it gives me back certain rows and columns which im interested in.

now i want to go through each cells by row and columns, and search for "<" and ">" signs in each cell.
for example,
c3 c4 c5
r2 EBT 1> 15
r3 EBR 1 176

so i search with for r to lastrow step 1 and for c to lastcolumn step 2
if i have >, then it will take the first 2 letter of th string in r2c3 (hence "EB"), and save it to a string named temp. Then it will add "L" or "T" or "R" to the temp string depending on whether "<" or ">" are present.
and then i will replace r2c3 with the temp string.

that was the logic, and now th eproblem
i worked thru the whole logic part, but i got an object error when i run thru the program
the line was something like...

(line which selects cell r2c3) <--- forgot...
cell.value = temp

i havent used vb in a while so im pretty clueless as to how to fix it...
can anyone help??

thx in advance

oh just forgot... another thing i want to do is change the value in c5, so say if I search thru the column and row, and found > in r2c4. THen i would want to add the values r2c5 and r3c5 together and replace the value in r2c5 with the new value. i can probably figure out how to do it myself after some helpful person teaches me the proper way to replace th... Read more

Answer:excel: macro help

It's too general. Please copy here the error message and the problematic code section.
 

1 more replies
Relevance 77.49%
Question: Excel Macro

Excel_Macro
I am working with two worksheets. I would like to look at the value starting from A4 and down the A column in my master worksheet and when the value is greater than zero, copy all the data in that row from A to G and paste it in my destination worksheet from A to G. When pasting in my destination worksheet, i need to get back to the next empty row. Can anyone help me. I need this for my school project asap. Thanks.
 

Answer:Excel Macro

What kind of school project are we talking about. We normally don't provide the answers for what is otherwise considered "homework"

Rollin
 

1 more replies
Relevance 77.49%
Question: Excel Macro?

Hi,

I have to adjust a database that is in the incorrect format required for statistical analysis, and I hoped to record a macro to help me out. My experience with this is very limited, and I fear that this is too great a task for me! Could someone please help out? What I need is for my current data, which looks like this:

a b c d e f g h i
to look like this:

a d g
b e h
c f i

Basically, the data is grouped into three, and I need every 2nd and 3rd bits of information to be transposed into a column under the 1st bit of information.

Can anyone help me?

Many thanks!
 

Answer:Excel Macro?

I should mention that this is a good sized database, . . . >1000 rows and >150 columns, . . . don't know if this changes anything, . . .
 

2 more replies
Relevance 77.49%

Hello,

I'm looking for some help with a macro I am working on in Excel. So far my macro leaves me with the data on sheet 1 of the attached book1.xls file. What I would like the data to look like after the macro is run is on Sheet 2.

It's difficult to explain in words but I am hoping someone with experience will see this as pretty simple. I'll try to give a representation below of what I am looking for so you don't have to look at the attached file to get an idea of what I need. The changes I need the macro to make are highlighted in red on sheet 2.

Sheet 1:
100 | 328
100 | 328
100 | 328
100 | 328
95 | 95
45 | 45
100 | 245
100 | 245
100 | 245

Sheet 2

100 | 328
100 | 328
100 | 328
28 | 328
95 | 95
45 | 45
100 | 245
100 | 245
45 | 245

Any help is greatly appreciated!
 

Answer:Excel VBA Macro Help

8 more replies
Relevance 77.49%
Question: Need excel Macro

Good dayI need help creating an Excel Macro. I have some names in a column, followed by some numbers in the next column. I need to have Excel copy the names and numbers for each, in some other cells.Since I am a beginner I do not know of the basic commands so even this IF then is a mistery to me. If anyone can help explain how it should be written, I can do the rest. Thank you.

Answer:Need excel Macro

You'll need to supply some more information, perhaps with some "before and after" examples of your data.Data can be "copied" with simple formulas, so I'm assuming you need something more sophisticated than that.Before you post any data, please click on my signature line and read the posting instructions available via that link.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

2 more replies
Relevance 77.49%
Question: Macro in excel

I would appreciate help with a macro to separate numbers in a line of text within a cell, and also to change the value of that number,
for example remove the text and keep the number or
remove the text and change the number to another value

Mining & Energy Plate-C3
Ryan & McNulty Sawmilling (68)
Black & Black - Bm65

Thank you
 

Answer:Macro in excel

6 more replies
Relevance 77.49%
Question: Excel Macro Help

I am trying automate the process of outputting about 20 rows of data in excel to 20 different text files*1 file per each row*, i have tired several methods but nothing has worked effectively because it doesn't appear to saving exactly what the row contains and its exact formatting. Also i need it to check if there is a file by the name its trying to save as i.e. fc1 to increment to fc2. my current macro:
Sub Macro3()
'
' Macro3 Macro
'

'
Range("A1").Select
Selection.Copy
Workbooks.Add
ActiveSheet.Paste
Application.CutCopyMode = False
SaveFileAs "fc", "C:\Users\Illusion\Desktop\Free Content Stuff\"
ActiveWindow.Close savechanges:=True
Windows("CSV FileMaker Macro.xlsm").Activate
Rows("1:1").Select
Selection.Delete Shift:=xlUp
Application.Run "'CSV FileMaker Macro.xlsm'!Macro3"
End Sub
Function SaveFileAs(sFilename As String, sPAth As String) As Boolean

Dim fn As String
Dim check As String
Dim ok As Boolean
Dim index As Long
fn = sPAth & sFilename & ".php"
check = Dir(fn)
ok = (check = "")

Do Until ok
index = index + 1
fn = sPAth & sFilename & index & ".php"
check = Dir(fn)
ok = (check = "")
Loop

ActiveWorkbook.SaveAs fn, FileFormat:=xlTextPrinter
End Function

works but like i said, it doesn't output the file exactly like its formatted which is causing a big problem...

does anyone have any ideas/suggestions that ... Read more

Answer:Excel Macro Help

I think you did it perfectly...
What is the problem then?
Only the formating? try to paste special by retaining formating
Record a macro with past special W/Formating and copy it's code
regards...
 

1 more replies
Relevance 77.49%
Question: Excel macro

hi.

I have an excel sheet with many rows and columns. the headings for the columns contain an expiry date, who the agreement is with (hyperlinked to a pdf), type of agreement, time until expiry and type (7 different types). I need a macro that will -
search each row and look for when 'time until expiry' = "less that 2 months" and 'type' = (one of the seven)
then send an email to a person whose email is in a different cell off to one side of the worksheet.

After it has done this, i need to run another macro which checks the same thing but with a different type (and email address)

Once all this is done, i need a macro to do all of the above but with "7 days" as the 'time until expiry'.

I also need a macro to run and search the rows for "unsigned" in the comments and again go through each type and send an email to the respective person.

Can this be done?!?!? It would be great if the macro could run automatically when the workbook is open.

Thanks so much in advance!!!
 

Answer:Excel macro

welcome to TSG, jenrowlands
while this is indeed a "do it yourself" problem, we have a forum much better suited to a business application problem....it's called "business applications

i'm moving your thread there.
 

3 more replies
Relevance 77.49%

I'm trying to use an if statement and I can't get Excel to recognize a formula similar to above. I need it to read if this range is greater than value A AND less than value B, then do this. How do I get the if statement to recognize two constraints? Is there a "between" function? I've searched all over the help file and I can't make it work. Please help. Thanks.
 

Answer:Excel Macro - 0 < X < 10

6 more replies
Relevance 77.49%
Question: Excel Macro

I have values entered in the C column that look like the following

'MAK, REP'

I need to find a way to make the values like the following:

'MAK','REP'

Any suggestions would be very much appreciated.
 

Answer:Excel Macro

14 more replies
Relevance 77.49%

1. Find and select the last column that contains data from worksheet1.2. Copy this entire column to worksheet2.Please help! Thanks!

Answer:Need a VBA Excel macro to do the following:

You will need to play with this but it should give you some ideasSub MyTest() Dim iEndRow As Long, iEndCol As Long iEndRow = Range("B65536").End(xlUp).Row 'find a column that always has data iEndCol = Range("AZ" & iEndRow).End(xlToLeft).Column MsgBox iEndRow & " " & iEndCol Columns(iEndCol).Copy Sheet2.Select Sheet2.Range("A1").PasteSpecial (xlPasteAll) 'change the column to one of your choice End Sub

2 more replies
Relevance 77.49%
Question: Excel Macro

I've got a macro written in Excel but I need to have the macro pause while I select a cell with my mouse and the have the macro restart and do a simple copy. Anybody know how this is done?
 

Answer:Excel Macro

You'll probably require a message box and everything, grumpy. Can you copy your code into here? Or perhaps the second item listed at http://www.erlandsendata.no/english/downloads/userforms.htm is what you need? I didn't download it to check it. The people at http://www.wopr.com/cgi-bin/w3t/wwwthreads.pl are very good at this kind of thing, but don't leave us!!
 

2 more replies
Relevance 77.49%
Question: Excel Macro help

Hi, I've had great success with this form before and was looking for some more help on an Excel spread sheet. I've created a macro that inputs data onto a separate sheet, entered from a template sheet. The macro executes using a button and i was wondering if there was a line of code that could bring the input cell back to the first entry position, after the button in clicked and the inputs are erased. I've attached the input section of my program if anyone would like to look at it. Thanks for any help.
 

Answer:Excel Macro help

After which button is clicked? (you have 3)

Regardless, just add Range("C2").Select at the end of the code.

And while you're at it, replace:

Range("C2:C3:C4:C5:C6:C7:C8").ClearContents

with:

Range("C2:C8").ClearContents

HTH
 

2 more replies
Relevance 77.49%
Question: excel macro

how to extract multiple excel files contents into one excel files , that all excel files are in one directory with directory reference plz anybody help, i need macro code
 

More replies
Relevance 77.49%
Question: Excel Macro

I am using Excel97. I have a twenty column worksheet with a combination of text and numbers. The data will be inputed by numerous sites and then sent to a central site for collation.
I have written a macro to do a specific sort automatically at the individual sites - Using column p (text) as Sort Key 1 and column q as Sort Key 2. Here is the part I cant figure out Column P is a mixture of blank cells and text entries. In order to sort the blank cells first I have the sheet replace the blanks with 1s and then I have it sort column P first and then column Q. But when that is done, I want the macro to ignore all the cells with 1s and switch so that the sort is done on Column Q first. After the first sort, can I then specify a range based on cell contents?
 

Answer:Excel Macro

7 more replies
Relevance 77.49%
Question: Excel macro help

Hi,

I want to open the pdf using the pdf's url link and save it.

for Example:

In Cell A1 = http://amsdc1-s-9729-a2.europe.shell.com/ReportServer?/Shell.SSRS.CSandA.Reports.PrintToPDF/Report&rs:Format=PDF&rs:Command=Render&rc:Toolbar=False&rcarameters=False&FormID=416032&AccountGroup=YSTP&Country=FR &CompanyInformation=1&Credit=1&TaxVat=1&Order=1&Delivery=1&Payment=1&FormSupport=1&ExtendedWorkFlow=1&NameAddress=1&InternationalAddress=1&Classification=1&BusinessPartners=1&Setuponcaps=1&Assesment=1&Response=1&SalesOrder=1&CustomerMaterial=1&ListingExclusion=1&OrderTemplate=1&ShippingReceiving=1&MRN=0
In Cell B1 = The pdf name

How to do this in excel macro. It's very urgent can anyone help me on this....

Pleaseeeeeeeeeeeeeeeeee

Swamy
 

More replies
Relevance 77.49%
Question: excel macro help

I am trying to copy selected cells from a source worksheet into a seperate woksheet in the same workbook.

Each record in the source worksheet (named 'master') starts with a transaction number (column A), Bank name (Column B), Amount (C),Booking unit (D), confirmation date (E), and various different fields with the last field in column AF.

When I go to column A of a particular record (in my 'master' worksheet)and press ctrl+t (my shortcut to the macro) I want all the cells in that particular row, from column A to column AF to be copied into the other worksheet called 'slave'. I want this to be pasted starting from column BB row 2 in the slave worksheet.

When I go to another record in my master worksheet and press ctrl+t I want the same thing to happen, BUT NOW i want this pasted in the slave worksheet in coloumn BB row 3.

I want this to happen every time I select a record to be transfered.

I know this may be asking for a lot of help, but would really appreciate any kind of help on this as it is an urgent requirement for my work.

Thanks in advance
 

Answer:excel macro help

6 more replies
Relevance 77.49%
Question: Excel Macro help

Few questions we've been having around the office. As the most junior on the programming team I get the short straw with the IT stuff cause our IT guy only comes once every couple of weeks.

Our accounting team has been utilizing more macros in their Excel work (2007) and we're having problems with editing macros. A macro won't work the way they want it to so they'll go to edit or delete it and come back with an error about hidden workbooks.

Did a little research and I think the problem comes from the settings in the workbook itself about having macros visible or enabled for that particular file because when I go to the macro settings and bring up their individual file they're working on the macro list is empty. (This also might by why the macros aren't working the way they want them to.)

So I think I need advice on how to edit macro settings for individual documents. If I haven't identified the problem correctly I would appreciate any information on how to fix my problem.
 

Answer:Excel Macro help

AFAIK, there's isn't really an easy way to edit macro settings for individual files. You can change the security level in Excel to something like low which would enable most macros by default but this isn't typically a recommended practice.

It's also possible that the user has disabled macros from running (i.e. clicked the Disable button at the opening dialog) and then can't edit / run any.

Something else to look at is if the macro has the designation Private. You can check this by opening the code window (ALT-F11) and then double-clicking the various sheets and modules in the left-hand pane. If any of the various modules have Private Sub listed at the top of the subroutine, it won't show up in the macro list (Tools > Macros), but will be available to run, and be available to edit through the code screen.

Also, for hidden workbooks, check under Window > Hide or Window > Unhide.
 

3 more replies
Relevance 77.49%

I have a report that gives me order numbers, that I then have to provide hyperlinks to the original file. I have limited space on the report, so am converting the full hyperlink into a short name. I also need a # days elapsed and data validation list for each row. I need to be able to do this for each entry in column A as the report can be quite large. Currently I have the macro set up to run after manual entry of the first hyperlink. I then have it copy the hyperlink from the first entry and use the SUBSTITUTE function to update it to correspond with the next entry. All work well. What I don't know how to do is set up the code to create a loop to perform the same action for each row there is data. Think I need a Do Until loop, but am at a loss where to go from here. Here's the code I have so far, it's not pretty but (almost) does the trick:

Manual entry of the first hyperlink, then:

Range("I2").Select
ActiveCell.FormulaR1C1 = "=HYPERLINK(RC[8],RC[-8])"
With Selection.Font
.Name = "Arial"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleSingle
.ColorIndex = 5
End With

And what I want to happen each row after:

Range("Q2").Select
Selection.Copy
Range("Q3").Select
ActiveSheet.Paste
Range("R3").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=SUBSTITUTE(RC[-1],R[-1]C[-17],RC[-17])"
Rang... Read more

Answer:Excel Macro: Do Until?

I didn't read the code but something like this will go thru each row

intRow = 2
Do Until oExcel.Cells(intRow,1).Value = ""
' Execute some code...
intRow = intRow + 1
Loop

Setting 'intRow to 2 gets you past the header.

Do Until oExcel.Cells(intRow,1).Value = ""
is checking the first cell in each row for null.
 

1 more replies
Relevance 77.49%

First off, I am trying to create a balance sheet for a check cashing store. I have 4 seperate Teller Balance Sheets, and a main sheet where I want the values to be entered.

The problem:

On the main sheet are a bunch of categories that ask the employee what they have done during the day (Money Orders, Checks Cashed, Etc.) On the right hand corner, I have 4 buttons which would direct the values over to their appropriate sheet. What I have tried to do was copy and paste the values over while recording a macro, but as soon as the original value was deleted, everything would go blank.

What I want to do:

I want a way where I can enter all the values, click on a button, and have those values go from the main sheet over to one of the teller sheets. From there, I could go back to the main sheet and proceed to do the next 3 employee's sheets.

If this is too confusing, let me know.

All help is appreciated.

-Jon
 

Answer:Excel Macro Help...Is this possible??

6 more replies
Relevance 77.49%

I need some help designing a macro to read from one column of ages, then put a group of Characters in another:
Here would be the structure:
IF(K# > =65)
THAN
D# = "65 and Older"
ELSE
IF(K#<=64 && K#>=55)
THAN
D# = "55-64"
ELSE
IF(K#<=54 && K#>=45)
THAN
D#="45-54"
ELSE
IF(K#<=44 && K#>=35)
THAN
D#="35-44"
ELSE
IF(K#<=34 && K#>=25)
THAN
D#="25-34"
ELSE
IF(K#<25)
THAN
D# = "Younger than 25"

Can someone write this up because I have tried numerous times to write this.
 

Answer:Help on a macro for excel

9 more replies
Relevance 77.49%

I have a large excel spreadsheet. Each row is a separate record with multiple columns. The first column is an identifier (ID_x) and the remaining N columns are numerical features (features 1, 2, .... N). There are multiple rows (M) for the same identifier ID_x and corresponding N features. See the following example:

ID_1 feature1_1a feature1_2a .............feature1_Na
ID_1 feature1_1b feature1_2b.............feature1_Nb
...
ID_1 feature1_1M feature1_2M............feature1_NM
ID_2 feature2_1a feature2_2a .............feature2_Na
ID_2 feature2_1b feature2_2b.............feature2_Nb
...
ID_2 feature2_1M feature2_2M............feature2_NM
....

I want to create a new spreadsheet that contains a single record per ID_x. For each single record, the features are replaced with the average (or median, or stdev, ...) of the corresponding features. This is what I mean:

ID_1 average(feature1_1a,1_1b,...,1_1M) average(feaure1_2a,1_2b, ...,1_2M) ... average(feature1_Na, 1_Nb,...,1_Nm)
ID_2 average(feature2_1a,2_1b,...,2_1M) average(feaure2_2a,2_2b, ...,2_2M) .....average(feature2_Na, 2_Nb,...,2_Nm)
.....

Is there a macro to do this?

Thank you.
 

Answer:Help with Excel Macro

Hi Vicky,
Welcome to the forum.
In general macro's an be written for everything.
I don't know if it would help in your case but you could try a pivot table
A sample with non-private, non-sensitive data would help give a better picture and also you should always mention the version of Excel you're working with.
 

1 more replies
Relevance 77.49%

OK, I have a simple spreadsheet - 2 tabs (1 with raw data, and 1 for reporting results from the data). For this problem I have 4 columns on the data tab I'm concerned with: Instance, Date of Instance, Time of Instance, and Instance Description. The Instances have about 50 different names. I need the macro to search for a specific instance (call it "Network Fail"), and when this instance is present on the data sheet; record the Date of Instance, Time of Instance, and Instance Description in the appriopate cells on the reporting results tab. I am fairly new at macros, but have a good understanding of excel - can anyone help me please??!!

Answer:Need Excel Macro Help!!!

Is there more than one occurrence of a given "instance" e.g. Network Fail that you need to pull data about?If not, could you simply use VLOOKUP?Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

6 more replies
Relevance 77.49%

I have a macro written in VB that works but not "perfectly".
Basically it looks at a range of cells and replaces X text with Y text. Very simple.
The problem that I have is I would like for it to replace the text with a literal match.
For example:
Replacing "B" with "BOMB" on a row that has "BOMB" on it would result is "BOMBOMBOMB"

What happens is it takes every "B" and replaces it with "BOMB".
What I would like it to do is to replace cells that have literally only "B" in them with "BOMB".
If a cell has anything else than just "B" it is ignored.

Here is the code:

Code:

Sub Replace_ALL()
Dim lr As Long, c As Integer, r As Integer
With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False
.DisplayAlerts = False
.EnableEvents = False
End With

lr = Cells(Rows.Count, "A").End(xlUp).Row

For r = 1 To lr 'cycle rows
For c = 1 To 1 'columns A to A
Cells(r, c).Value = replace(Cells(r, c).Value, "B", "BOMB")
Next c
Next r

With Application
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
.DisplayAlerts = True
.EnableEvents = True
End With
End Sub
 

Answer:Excel VB Macro help...

Here's a quick and dirty solution, just test for the length of the cell before substitution:
Code:
Sub Replace_ALL()

Dim lr As Long, c As Integer, r As Integer
With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False
.DisplayAlerts = False
.EnableEvents = False
End With

lr = Cells(Rows.Count, "A").End(xlUp).Row

For r = 1 To lr 'cycle rows
For c = 1 To 1 'columns A to A
If Len(Cells(r, c).Value) = 1 Then
Cells(r, c).Value = Replace(Cells(r, c).Value, "B", "BOMB")
End If
Next c
Next r

With Application
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
.DisplayAlerts = True
.EnableEvents = True
End With
End Sub
 

2 more replies
Relevance 77.49%
Question: Excel macro

Hi all you excel professionals. I'm still a rookie but I'm slowly getting there. I wonder if the follwing is possible. I'l be very brief. In column A, form cell A1 to A500, I have typed in 0011, 002, 003 , etc up to 00500. In column B, from cell B1 to B500 I have typed the word "total". Now I want to put the word total into Cells A1 to A500. For example, cell A1 would now read 001 Total and cell A2 would now read 002 Total etc all the way to cell A500. I have tried to merge the two cells but I loose the word Total. Is there a macro that will do this? If so can someone out there do it for me?

Please help me.
 

Answer:Excel macro

6 more replies