Free Microsoft Excel 2013 Quick Reference

Vba to set range cell values based on value in two other cells Results

Using vba, how do I set the values for column C (Range C2:C100) based on values in column A (Range A2:A100) AND column B (Range B2:B100). ??

For example, if column A (type date) is NULL, set column C (type general) to "YES"
OR
if column A (type date) is NOT NULL AND column B (type general) is "YES", set column C (type general) to "YES"

Thanks for all your help.

Hello all

I have an excel database where column B contains date and time, column D may contain "Defect" or some other text. the remaining columns contain other information.

I can loop through the database and extract only rows whos text in column D = "Defect"
I use the following code

	VB:
	
 
Sub MyGetDefects() 
     
     'Only take defects from "Data"
     'and put it into "Info"
    Dim x As Long 
    Dim lLastrow As Long 
    Dim wsCurr As Worksheet, wsTarg As Worksheet 
    Dim rCell As Range 
    Dim vContents As Variant 
     
    Set wsCurr = Sheets("Data") 
    Set wsTarg = Sheets("Info") 
    lLastrow = wsCurr.Range("A65536").End(xlUp).Row 
    x = 1 
     
    For Each rCell In wsCurr.Range("D4:D" & lLastrow) 
        If rCell.Value = "Defect" Then 
            vContents = wsCurr.Range(rCell.Offset(0, -3), rCell.Offset(0, 11)).Value 
            wsTarg.Range("E" & x & ":S" & x).Value = vContents 
            x = x + 1 
        End If 
    Next rCell 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
What I would like is to be able to narrow the loop based on dates.
I could enter the dates in two cells on the sheet but I dont know how to change my code to reference these two cells.

Could anyone help?

TIA
Kenny
using a mix of Office 97 and 2000 with win 98, 2000, and XP
(yeh I know 97 and 98 should go but my company wont upgrade!)

Hi! The linked thread shows almost exactly what I'm looking to accomplish.
http://www.mrexcel.com/forum/showthread.php?p=1572477
(My apologies for finding it on another forum, it came up in a search... I'm really very loyal to this forum cause the folks here rock way harder than anywhere else...)

But as I know very little about VBA, I have a few questions...

I have two different sites entering information onto a spreadsheet. If the first site enters information in Column B, I need column C locked so the second site doesn't write try entering useless information. If the first site does not enter information, column C stays unlocked.

Column B does not actually contain numerical values. It contains references to numerical values from another workbook.

So the following is what VBA Noob suggested in the other thread:

Originally Posted by VBA Noob
Try an event macro > right click sheet tab > select view code > paste in the below.
If A1 formula returns 10 then the cell is locked.

Private Sub Worksheet_Calculate()
Dim Rng As Range
Set Rng = Range("A1")

Select Case Rng
Case Is = 10
Rng.Locked = True
Case Else
Rng.Locked = False
End Select
End Sub
HTH
VBA Noob So how could I make the following changes and, is the fact that there is a reference to another workbook rather than an actual numerical value going to make a difference?
And lastly, how to I apply this to all the rows, not just C1 & B1, but to C2 & B2 all the way down?

Private Sub Worksheet_Calculate()
Dim Rng As Range
Set Rng = Range("C1")

Select Case Rng
 Case Is = B1 is Greater than 0 or Is not blank (whichever would work better)
     Rng.Locked = True
 Case Else
     Rng.Locked = False
End Select
End Sub


What I'm trying to accomplish here is to write a function in VBA that
essentially uses the excel functions index in conjunction with match to
search through matrices in two worksheets in another workbook (OEM
shipping schedule.xls) and return these values based on the inputs of a
"date" and a "part number" provided in my active worksheet.

If the part exists in both worksheets I want it to return the sum of
the values; if it only exists in one I want it to return only that
value; if it exists in none I want it to return 0.

At this point I keep getting a "subscript out of range" error.

Obviously this is based on a formula I created in excel that worked
with fewer "if" statements. But I was forced to try to learn some VBA
to overcome both the max cell character limit and the argument limit.

I tried using names to overcome the argument limit but that wouldn't
work as I want the variables "tDate" and "CustPartNo" to be relative
references so I can extend the formula to other cells in my
spreadsheet, however using names only gives me absolute references.

Please help if you can; like I said I've only just taken up VBA
programming two days ago . . .

Thanks in advance for any help . . .

Here is my code:

Function SHIPREQ(CustPartNo As String, tDate As Date)
Dim VOEMprodn As Range
Dim VOEMSPO As Range
Dim HOEMprodn As Range
Dim HOEMSPO As Range
Dim ROEMprodn As Range
Dim ROEMSPO As Range

VOEMprodn = Workbooks("'OEM Shipping
Schedule.xls'").Worksheets("Production").Range("$A$4:$A$150")
VOEMSPO = Workbooks("'OEM Shipping
Schedule.xls'").Worksheets("'Service Parts'").Range("$A$4:$A$150")
HOEMprodn = Workbooks("'OEM Shipping
Schedule.xls'").Worksheets("Production").Range("$A$4:$AH$4")
HOEMSPO = Workbooks("'OEM Shipping
Schedule.xls'").Worksheets("Production").Range("$A$4:$AH$4")
ROEMprodn = Workbooks("'OEM Shipping
Schedule.xls'").Worksheets("Production").Range("$A$4:$AH$150")
ROEMSPO = Workbooks("'OEM Shipping
Schedule.xls'").Worksheets("Production").Range("$A$4:$AH$150")

If Application.WorksheetFunction.CountIf(VOEMprodn, tDate) > 0 Then
'If date is in set'
If Application.WorksheetFunction.CountIf(VOEMprodn, CustPartNo)
> 0 _
And Application.WorksheetFunction.CountIf(VOEMSPO,
CustPartNo) > 0 = True Then 'and If # is in prodn & SPO set'
SHIPREQ = Application.WorksheetFunction.Index(ROEMprodn,
Application.WorksheetFunction.Match(CustPartNo, VOEMprodn, 0),
Application.WorksheetFunction.Match(tDate, HOEMprodn, 0)) _
+ Application.WorksheetFunction.Index(ROEMSPO,
Application.WorksheetFunction.Match(CustPartNo, VOEMSPO, 0),
Application.WorksheetFunction.Match(tDate, HOEMSPO, 0)) 'return reqs
from prodn and SPO'

ElseIf Application.WorksheetFunction.CountIf(VOEMprodn,
CustPartNo) > 0 Then 'else If # is in prodn set'
SHIPREQ = Application.WorksheetFunction.Index(ROEMprodn,
Application.WorksheetFunction.Match(CustPartNo, VOEMprodn, 0),
Application.WorksheetFunction.Match(tDate, HOEMprodn, 0))
ElseIf Application.WorksheetFunction.CountIf(VOEMSPO,
CustPartNo) > 0 Then 'else If # is in SPO set'
SHIPREQ = Application.WorksheetFunction.Index(ROEMSPO,
Application.WorksheetFunction.Match(CustPartNo, VOEMSPO, 0),
Application.WorksheetFunction.Match(tDate, HOEMSPO, 0)) 'return reqs
from prodn'
Else
SHIPREQ = 0 'if # not in prodn or SPO set return 0'
End If
Else
SHIPREQ = 0 'if date not in set return 0'
End If

End Function

Hi friends

I have a code that shifts data down 1-3 rows depending on what quarter the "CalcDate" is. I've now managed to get the code to shift the data down, but I have two problems (that I can't find answers to on the internet!!!):

1. Column B (from B6 on) contains the end-dates for all quarters running from the CalcDate - I would like to get my VBA to autofill the empty cells so there is a smooth transition - so if CalcDate is 30/09/2011, data is shifted three rows down, and then the VBA autofills the above 3 cells with 31/12/2011, 31/03/2011 and 30/06/2012.

2. I would like the code to copy the data in the top row into all empty rows above it (up until the column headers). This will always be between 1 and 3 rows. I have attached a printscreen.

3. Finally, I'd like to add 1 day to all dates in column B6, so that instead of it saying, for instance, 31/03/2011, it says 01/04/2011.

I'm going to continute searching for answers, but I hope one of you splendid chaps can help. Printscreen attached!


	VB:
	
 FixGraph2() 
     'Outstanding Amount
     
     
    Dim wb As Workbook 
    Dmonth As Integer 
    Dim Ldate As Date 
     
     
     
    Set wb = Workbooks(ThisWorkbook.Name) 
    wb.Activate 
    Let Ldate = wb.Worksheets("Start").Range("CalcDate").Value 
     
    wb.Worksheets("Grafer 2").Range("OutstandingData").ClearContents 
    wb.Worksheets("Grafer 2").Range("OutstandingData").Value = wb.Worksheets("Outstanding Amount").Range("A3:D104").Value 
    wb.Worksheets("Grafer 2").Range("B6").Value = wb.Worksheets("Start").Range("CalcDate").Value 
     
     
     
    Dmonth = Month(Ldate) 
     
     
    If Dmonth  12 Then 
        Range("OutstandingData").Offset(Dmonth / 3, 0).Value = Range("OutstandingData").Value 
        Range("B6").Resize(Dmonth / 3, 4).ClearContents 
         
         
    End If 
     
End Sub 

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


I am new to using VBA, and this is the first userform I have ever made. I'm trying to build a database of a lot of data (as many as 10,000 rows of data) for a clinical research trial.

I've developed a userform with a multipage control that has two tabs. However, the data on each of these tabs is to be entered at different times, so what I would like to do is set the comboboxes at the top of the second tab ("Image Interpretation") so that the correct patient can be identified and the data entered into the correct row. I have thought of two possible ways to do this, but I am not sure about how to go about doing either of these, or which would be the best way to go.

The first solution I thought of was to have the data on the first tab of the multipage control ("Patient Data") save to a separate worksheet, and to then program the userform so that it would automatically fill in the data for the patient (once it was identified using the comboboxes at the top of the second tab), so that it could be entered into a master worksheet with the data from the second tab.

My other idea was to program the "OK" command button on the second tab to automatically enter the data for the patient selected into the correct row.

I've attached a copy of what I have so far, in case that helps.

I already started a thread with a similar question, but I wasn't sure how to adapt the code that the users provided to fit my needs. I'm not sure how to link that thread to this one, but the title of the thread was "adding values to a combobox from a specific range of cells".

I really appreciate any help you all can give me!

Hi,

I was able to use TJ's code from this post perfectly for only one value and one pivot table.

However, now I need to update multiple pivot tables - all with the same filters/pages - in different spreadsheets.

The spreadsheet with the values that should change the filter is "Setup" and then I have about 5 other spreadsheets all with pivot tables, and two other sheets with the data feeding the pivot tables.

This is what I have:
Sub Apply_Name_Filter()
    Dim pvtTable As PivotTable
    Dim pvtField As PivotField
    Dim pvtItem As PivotItem
    Dim filterName As String
    
    Set pvtTable = Worksheets("PIVOT-Sheet2").PivotTables("PivotTable1")
    Set pvtField = pvtTable.PivotFields("Name")
    
    filterName = Worksheets("Setup").Range("B8")
    
    For Each pvtItem In pvtField.PivotItems
        If pvtItem.Value = filterName Then
            pvtField.CurrentPage = filterName
            Exit For
        End If
    Next pvtItem

End Sub
Any suggestions in what I could do? (I'm a beginner at coding.... ;-)
Thanks!

Hi there

I am trying to carry out a relatively simply copy/paste type function between two sheets based on the following criteria:
- range to be copied is from sheet 1A, column G, used range only (from row 6 onwards)
- data is to be "pasted" onto sheet HA2, same row numbers as the data on sheet 1A. The destination column is based on looking up and finding the value "1" in row 6 between columns 9 and 213.

I've tried the following code, using a function defined below, but it doesn't seem to work...

For the lookup of the destination column, I've just tried to find the last (only) column with data....

Please bear in mind, I'm a VBA absolute beginner, and the code I've hatched together is from other code a friend has prepared for me!

Thanks in advance

Sub PostProgressWBS01()
 
Dim cel As Range
Dim rng As Range
'Dim PutCol As Long
Dim PutCol As Long
Dim LastRow As Long
    
    'Get column where data is to be copied to on Sheet HA2
    Set rng = Sheets("HA2").Range(Cells(6, 9).Address, Cells(6, 213).Address)
    PutCol = Find_LastCol_Data(rng)
    
    'Get last Row with DATA on Sheet 1A
    Set rng = Sheets("1A").Range(Cells(18, 7).Address, Cells(9999, 7).Address)
    LastRow = Find_LastRow_Data(rng)
     
    'do the thing
    For Each cel In rng
        If cel.Value <> "" Then
        Sheets("HA2").Cells(cel.Row, PutCol) = cel
        End If
    Next cel

    Set cel = Nothing
    Set rng = Nothing
    
    Application.CutCopyMode = False
End Sub


Function Find_LastCol_Data(rng As Range)

    ' Find the last column
    On Error Resume Next
    Find_LastCol_Data = rng.Find(What:="*", _
                After:=rng.Cells(1), _
                Lookat:=xlPart, _
                LookIn:=xlValues, _
                SearchOrder:=xlByColumns, _
                SearchDirection:=xlPrevious, _
                MatchCase:=False).Column
    On Error GoTo 0
    
End Function

Function Find_LastRow_Data(rng As Range)

    ' Find the last row
    On Error Resume Next
    Find_LastRow_Data = rng.Find(What:="*", _
                After:=rng.Cells(1), _
                Lookat:=xlPart, _
                LookIn:=xlValues, _
                SearchOrder:=xlByRows, _
                SearchDirection:=xlPrevious, _
                MatchCase:=False).Row
    On Error GoTo 0
    
End Function


Hello,

I've been working on this problem now (intermittently) for about a month, and I've Googled to my fullest extent, but I've hit the wall and I need help from a more experienced VBA user.

What I am trying to do is write a macro that will automatically grab multiple data sets, then chart them, format the charts, name the charts, etc. My spreadsheet is set up like this: in the A:A column, I have my x-values. More specifically, this column contains two sets of x-values (time starting at zero), one of which corresponds to an experimental set of temperature data, the other corresponding to a modeled set of temperature data. So this column is discontinuous at one point, where the first x-dataset ends and the second one begins again (at time = zero). The y-data are then listed in all subsequent columns, and are located adjacent to one another...so y-data-set1 lies in columns B:C, y-data-set2 in columns D:E, and so on. If you were to see the data (which I probably shouldn't share on the web!), you would see columns A:B containing data, but the C column data wouldn't show up until hundreds of rows down, when the second time range begins.

In the end, each chart should have two temperature-time curves, one of which corresponds to (for example) the data range (A2:A500, B2:B500), and the other, (A501:A700, C501:C700). As long as this data is correctly grabbed, the formatting part of my code is fine, but something's apparently wrong with my grabbing method. I devised a For-Next procedure to cycle through the data columns based on the variable "col," (see the code below), and I am using a simple If-Then argument to ensure that the macro will stop trying to grab data when it comes to an empty column. However, although the macro seems to loop properly, and the chart formatting/naming is working, my problem is the following:

After the first chart is plotted, the data selection corresponding to that chart remains selected, so that all subsequent charts plot a cumulative data set (i.e. chart1 plots data from A:C, chart2 plots data from A:E, chart3 plots data from A:G, etc.), which I do not want. I cant find a way to deselect the data after charting it, but I dont believe I should have to do so, anyway. I had some other problems regarding sourcedata specification and x-values showing up incorrectly, but they seemed to vanish overnight(!). So here is the code I have right now, which works perfectly except for the fact that its plotting cumulative charts of data:

Sub all_charts_create_and_format()

' create and format all charts macro - for one TC test
'
' Macro created 1/11/2006 by mmf

Dim col As Integer

For col = 2 To 100 Step 2

Sheets("Model vs. Experimental").Activate

If IsEmpty(Cells(2, col)) = False Then

Sheets("Model vs. Experimental").Range("A1:A5000", Range(Sheets ("Model vs. Experimental").Columns(col), Sheets("Model vs. Experimental").Columns(col + 1))).Select 'this is the data selecting method

Charts.Add

ActiveChart.ChartType = xlXYScatterSmooth

ActiveChart.setsourcedata Source:=Sheets("Model vs. Experimental").Range("A1:A5000", Range(Sheets("Model vs. Experimental").Columns(col), Sheets("Model vs. Experimental").Columns(col + 1))) 'I had to use this seemingly redundant sourcedata method because originally, the data was not being plotted correctly

ActiveChart.Location Where:=xlLocationAsObject, Name:="Model vs. Experimental"

(I have excluded all of the chart formatting code, but this is where it lies in the actual code)

End If

Next col

End Sub

I would greatly appreciate any help in this matter; I feel like my data selecting method must contain an error that I am not familiar enough with VBA to understand. And please, try to keep it simple!

Thanks for reading,

Matt

Hello Everyone,

First I would like to thank anyone in advance who is willing to tackle this problem with me.

New guy here. I've been working on this Macro that splits up my data from a master sheet and splits it into many different tabs and names them according to the account number which is in the far most right coloumn. It groups all of the specific accounts activity in the one tab.

The problem I have is after I copy about 15 sheets or so it brings up this error:

Excel cannot complete this taks with available resources. Choose less data
or close other applications.

I push OK

then it says:

Run-Time error '1004':

PasteSpecial method of Range class failed

I push Debug

it highlights

mySht.Range("A1").PasteSpecial xlPasteValues

If i push End

it says:

The picture is too large and will be truncated.

I push OK

and it comes up two more times and the book closes.

vba code

Option Explicit

Private Declare Function OpenClipboard Lib "user32" _
(ByVal hwnd As Long) As Long
Private Declare Function CloseClipboard Lib "user32" () As Long
Private Declare Function EmptyClipboard Lib "user32" () As Long

Sub ExportDatabaseToSeparateFiles()
'Export is based on the value in the desired column

Dim myCell As Range
Dim mySht As Worksheet
Dim myName As String
Dim myArea As Range
Dim myShtName As String
Dim KeyCol As Integer

myShtName = ActiveSheet.Name
KeyCol = InputBox("What column # within database to use as key?")

Set myArea = ActiveCell.CurrentRegion.Columns(KeyCol).Offset(1, 0).Cells

Set myArea = myArea.Resize(myArea.Rows.Count - 1, 1)

For Each myCell In myArea
On Error GoTo NoSheet
myName = Worksheets(myCell.Value).Name
GoTo SheetExists:
NoSheet:
Set mySht = Worksheets.Add(Before:=Worksheets(1))
mySht.Name = myCell.Value
With myCell.CurrentRegion
.AutoFilter Field:=KeyCol, Criteria1:=myCell.Value
myCell.Parent.Cells.SpecialCells(xlCellTypeVisible).Copy
mySht.Range("A1").PasteSpecial xlPasteValues
mySht.Range("A1").PasteSpecial xlPasteFormats
mySht.Cells.EntireColumn.AutoFit
.AutoFilter
ClearCipboard
Application.CutCopyMode = False

End With
Resume
SheetExists:
Next myCell

End Sub

Sub ClearClipboard()
OpenClipboard Application.hwnd
EmptyClipboard
CloseClipboard
End Sub

end vba

Thanks so much for your help...

Dejan

Hi guys,

I have two columns, A and B. Column A has names. Column B has prices that are VLOOKUPed from a closed file (based on the names in Column A). The problem is that the file from which I am pulling in prices is generated daily.

The filename is file_YYYYMMDD.xls and the file is created anew daily, with its filename reflecting today's date. Does anyone have any suggestions on how I could access it? After much research, I've come to the conclusion that INDIRECT and INDIRECT.EXT are not suitable, because I am referencing an external, closed workbook from within a VLOOKUP. Harlan Grove's pull() UDF seems to be built for this, but when I try to write Code:
=VLOOKUP(B11,pull(MacroSheet!C5),3,FALSE)
I get #VALUE! in all my cells.

By the way, the value of MacroSheet!C5 is Code:
'S:[file_20050610.xls]SPB51'!$B1:$D200
.

The code for pull() function is:
Code:
'----- begin VBA -----
Function pull(xref As String) As Variant

Dim xlapp As Object, xlwb As Workbook
Dim b As String, r As Range, C As Range, n As Long

'** begin 2004-05-28 changes **
'** begin 2004-03-25 changes **
n = InStrRev(Len(xref), xref, "")

If n  0 Then
If Mid(xref, n, 2) = "[" Then
b = Left(xref, n)
n = InStr(n + 2, xref, "]") - n - 2
If n  0 Then b = b & Mid(xref, Len(b) + 2, n)

Else
n = InStrRev(Len(xref), xref, "!")
If n  0 Then b = Left(xref, n - 1)

End If

'** key 2004-05-28 addition **
If Left(b, 1) = "'" Then b = Mid(b, 2)

On Error Resume Next
If n  0 Then If Dir(b) = "" Then n = 0
Err.Clear
On Error GoTo 0

End If

If n = 0 Then
pull = CVErr(xlErrRef)
Exit Function
End If
'** end 2004-03-25 changes **
'** end 2004-05-28 changes **

pull = Evaluate(xref)

If CStr(pull) = CStr(CVErr(xlErrRef)) Then
On Error GoTo CleanUp   'immediate clean-up at this point

Set xlapp = CreateObject("Excel.Application")
Set xlwb = xlapp.Workbooks.Add  'needed by .ExecuteExcel4Macro

On Error Resume Next    'now clean-up can wait

n = InStr(InStr(1, xref, "]") + 1, xref, "!")
b = Mid(xref, 1, n)

Set r = xlwb.Sheets(1).Range(Mid(xref, n + 1))

If r Is Nothing Then
pull = xlapp.ExecuteExcel4Macro(xref)

Else
For Each C In r
C.Value = xlapp.ExecuteExcel4Macro(b & C.Address(1, 1, xlR1C1))
Next C

pull = r.Value

End If

CleanUp:
If Not xlwb Is Nothing Then xlwb.Close 0
If Not xlapp Is Nothing Then xlapp.Quit
Set xlapp = Nothing

End If

End Function
Thanks so much for any help, guys! If this could be done any other way, please let me know, I am so stuck with this.

I have two cells with data validation (each using a named range/list that
brings in data from another sheet).

When one changes, it runs through my code and works just fine.

When the other data validation changes, one of the things I have to do is
re-set the other data validation cell to the default value. This part is
working fine, except that it triggers the worksheet_change event to run a
second time.

Is there an elegant way to catch/ignore the worksheet_change event when
changes are caused by code instead of user interaction?

Thanks,
Keith

How to use Visual Basic for Applications (VBA) to change UserForms in Excel

How to Programmatically Manipulate a UserForm

How to Programmatically Create UserForms in Visual Basic for Applications

Download Attachment At Bottom For Live Links

The Hey, Scripting Guy! Archive: Microsoft Office

Microsoft Excel
• How Can I Import a Fixed-Width Data File into Microsoft Excel?
• How Can I Sort a Spreadsheet By Cell Color?
• How Can I Check the Value of a Specific Cell in a Bunch of Excel Spreadsheets?
• How Can I Mark the First Occurrence of a Number in an Excel Spreadsheet?
• How Can I Read Custom Summary Information Properties for an Excel File?
• How Can I Change the Color of a Spreadsheet Cell Based on a Range of Values?
• How Can I Import Multiple Worksheets into an Access Database?
• How Can I Search For Values in an Excel Worksheet?
• How Can I Cut a Row From One Excel Spreadsheet and Paste That Row Into Another Spreadsheet?
• How Can I Determine the Value of the Last Cell in an Excel Range?
• How Can I Convert an Excel Serial Number to a Date and Then Back?
• How Can I Insert New Rows Above the Last Row in an Excel Spreadsheet?
• How Can I Copy Data from Excel to PowerPoint?
• How Can I Retrieve Hyperlink Information From an Excel Spreadsheet?
• How Can I Insert Blank Rows (and Formulas) Into an Excel Worksheet?
• How Can I Update and Then Break All the Links in an Excel Spreadsheet?
• How Can I Open All the Excel Spreadsheets in a Folder and Run a Specified Macro Found in Each of Those Spreadsheets?
• How Can I List the Addresses in My Sent Items Folder in an Excel Spreadsheet?
• How Can I Add a Background Picture to an Excel Worksheet?
• How Can I Replace Numeric Values in an Excel Spreadsheet?
• How Can I Copy Data From One Spreadsheet to Another?
• How Can I Replace Text in an Excel Spreadsheet?
• How Can I Set the Print Area in an Excel Spreadsheet?
• How Can I Delete Specified Rows in an Excel Spreadsheet?
• How Can I Color Every Other Row in an Excel Spreadsheet?
• How Can I Copy Selected Columns From a CSV File to an Excel File?
• How Can I Change the Background Color of Spreadsheet Rows That Have a Date in a Specified Cell?
• How Can I Export an HTA Table to Excel?
• How Can I Delete All the Worksheets in a Spreadsheet Except for the First Worksheet?
• How Can I Assign a Background Color to Cells in a Spreadsheet and Then “Sum” Those Cells?
• How Can I Sort a Row in an Excel Spreadsheet?
• How Can I Remove the Password When Opening an Excel Spreadsheet?
• How Can I Import an Excel Spreadsheet Into an Access Database?
• How Can I Save a Table in an Access Database as a Spreadsheet?
• How Can I Use a Blank Row to Separate Data in an Excel Spreadsheet?
• How Can I Specify the Number of Decimal Places to Display in an Excel Spreadsheet?
• How Can I Configure Excel to Autosave Every 5 Minutes?
• How Can I Save an Excel Chart as a Picture?
• How Can I Change the Font Color in Excel If a Specified Condition is Met?
• How Can I Convert an Excel Spreadsheet to XML?
• How Can I Set the Default File Path in Excel to a User’s Home Directory?
• How Can I Compare a List of Names in One Excel Column to a List of Names in Another Column?
• How Can I Convert a Tilde-Delimited File to Microsoft Excel Format?
• How Can I Search Active Directory for User Names Stored in an Excel Spreadsheet?
• How Can I Add a Total Row to an Excel Spreadsheet?
• How Can I Create a Custom Date Format in Microsoft Excel?
• How Can I Keep the Screen From Updating While Running an Excel Script?
• How Can I Use Information in an Excel Spreadsheet to Rename a Set of Folders?
• How Can I Determine the Background Color of a Spreadsheet Cell?
• How Can I Make the First Character in a Cell Uppercase and All the Other Characters Lowercase?
• How Can I Copy a Worksheet to a New Spreadsheet?
• How Can I Use Windows PowerShell to Automate Microsoft Excel?
• How Can I Tell If a Specified Worksheet Exists in an Excel Workbook?
• How Can I Add a COUNTIF Formula to an Excel Spreadsheet?
• How Can I Copy All the Comments From an Excel Worksheet to a Word Document?
• How Can I Change All the Lowercase Letters in an Excel Worksheet to Uppercase Letters?
• How Can I Create a New Excel Spreadsheet at Midnight Each Night?
• How Can I Select a Column of Data in Excel and Then Paste that Data into a Text File?
• How Can I Convert a Number to a Date in Excel?
• How Can I Locate and Replace Information for a Specific Item in a Spreadsheet?
• How Can I Save an Excel Spreadsheet, and Then Save a Copy as HTML?
• How Can I Format an Excel Spreadsheet So It Retains Leading Zeroes?
• How Can I Determine the Last Row in an Excel Spreadsheet?
• How Can I Remove All the Formatting from an Excel Spreadsheet?
• How Can I Change the Footer in an Excel Spreadsheet?
• How Can I Add Additional Worksheets to an Excel Workbook?
• How Can I Sort Worksheets in a Workbook?
• How Can I Import a Fixed-Width File into Microsoft Excel?
• How Can I Add a Hyperlink to an Excel Spreadsheet?
• How Can I Build an Array from a Column of Data in Excel?
• How Can I Copy Column C of One Worksheet to Column A of a Second Worksheet?
• How Can I Center Text in an Excel Cell?
• How Can I Change the Background Color of a Cell Depending on the Date?
• How Can I Delete Data from a Spreadsheet Yet Keep All the Formatting?
• How Can I Open an Excel Workbook and Retrieve the Names of All the Worksheets?
• How Can I Tell if an Excel Workbook is Open and, If It Isn’t, Open It?
• How Can I Insert a Column into a Spreadsheet?
• How Can I Save a Single Excel Worksheet to a CSV File?
• How Can I Password-Protect an Excel Spreadsheet?
• How Can I Make Changes to and Then Re-Save an Existing Excel Spreadsheet?

Microsoft Word
• How Can I Randomly Assign a Font to Characters in a Word Document?
• How Can I Find a Word in a Document and Change the Background Color of the Paragraph Where That Word Appears?
• How Can I Count the Number of Sentences and Paragraphs in a Word Document?
• How Can I Extract Specific Information From a Word Document and Then Use That Information to Rename the Document?
• How Can I Tally All the Items in a Microsoft Word Document?
• How Can I Reset the Revision Number of a Word Document to 1?
• How Can I Retrieve Field Values in a Microsoft Word Document?
• How Can I Change the Paragraph Case in Microsoft Word?
• How Can I Insert a Manual Line Break into a Microsoft Word Document?
• How Can I Search For and Modify Formatted Text in a Word Document?
• How Can I Search For Red Text in a Microsoft Word Document?
• How Can I Change the Default File Save Format in Microsoft Word?
• How Can I Put the File Name in the Footer of a Microsoft Word Document?
• How Can I Add a Page X of Y Footer to a Microsoft Word Document?
• How Can I Save Each Paragraph in a Word Document as a Database Record?
• How Can I Replace a Specified Font in a Microsoft Word Document?
• How Can I Apply a Theme to a Microsoft Word Document?
• How Can I Add the Last-Saved Date to the Footer of a Microsoft Word Document?
• How Can I Insert a Date Field in Word?
• How Can I Add a New Item to the Microsoft Word AutoCorrect List?
• How Can I Clear All the Formatting From a Microsoft Word Document?
• How Can I Change the Default Highlight Color for a Microsoft Word Document?
• How Can I Run a Macro After Opening Up Word?
• How Can I Center-Align a Picture in a Word Document?
• How Can I Change an Existing Hyperlink in a Microsoft Word Document?
• How Can I Add a Blank Line Between the Existing Paragraphs in a Word Document?
• How Can I Insert Text Into an Existing Microsoft Word Bookmark?
• How Can I Get a Total Page Count for All the Word Documents in a Folder?
• How Can I Set the Document Orientation in Microsoft Word to Landscape?
• How Can I Replace Text in a Microsoft Word Document?
• How Can I Search For (and Reformat) Highlighted Text in a Word Document?
• How Can I Get a List of the Unique Words Used in a Microsoft Word Document?
• How Can I Get a List of Available Metadata for Microsoft Office Documents?
• How Can I Add Multiple Tables to a Word Document?
• How Can I Search a Word Document for All the Words in Double Brackets?
• How Can I Add Centered Page Numbers to the Footer of a Word Document?
• How Can I Create a Table and Fill the First Column With a Range of Dates?
• How Can I Rename a Word Document Using the First Three Characters in That Document?
• How Can I Search for and Highlight Words in a Microsoft Word Document?
• How Can I Right-Align a Single Column in a Word Table?
• How Can I Boldface a Specific Word Throughout a Microsoft Word Document?
• How Can I Change the Font Name and Size for an Entire Word Document?
• How Can I Set Word’s Revision View Mode to Final?
• How Can I Insert Multiple Files Into a Word Document, Putting a Page Break Between Each File?
• How Can I Set Word’s Default File Location to be the User’s Home Directory?
• How Can I Open Word with the Cursor Positioned at the Start of a Specified Line?
• How Can I Change File Locations for Microsoft Word?
• How Can I Insert a Symbol into a Word Document?
• How Can I Hide a Specific Toolbar in Microsoft Word?
• How Can I Extract Word Paragraphs That Use a Specific Style?
• How Can I Insert Files into a Word Document?
• How Can I Convert 1,000 .RTF Files to Word Documents?
• How Can I Add a Hyperlink to a Word Document?
• How Can I Put the User Name into the Footer of a Microsoft Word Document?
• How Can I Determine Which Version of Word is Installed on a Computer?
• How Can I Save Word Documents as Text Files By Using a Script?

Microsoft Access
• How Can I Import Multiple Worksheets into an Access Database?
• How Can I Add a Record to a Database Using Windows PowerShell?
• How Can I Delete a Set of Records From an Access Database?
• How Can I Be Notified if an Access Database File Exceeds a Specific Size?
• How Can I Retrieve the Field Size and a Sample Record For All the Tables and Fields in an Access Database?
• How Can I List All the Fields and Data Types in an Access Database?
• How Can I Append a Value to a Specified Field For Each Record in a Database?
• How Can I Add the Contents of a Group of Text Files to an Access Database?
• How Can I Import a .CSV File into an Access Database?
• How Can I List All the Access Database Files on a Computer?
• How Can I Print a Microsoft Access Report?
• How Can I Compact an Access Database?
• How Can I Use Windows PowerShell to Pull Records From a Microsoft Access Database?
• How Can I List All the Tables in an Access Database?
• How Can I Determine Which Version of Access was Used to Create a Database?
• How Can I Delete a Table from a Jet Database?

Microsoft Outlook
• How Can I Schedule a Meeting Each Monday Through Friday For Two Weeks?
• How Can I Sort Items Retrieved From a Microsoft Outlook Folder?
• How Can I Set a Reminder on All My Outlook Appointments?
• How Can I Schedule an All-Day Event in Microsoft Outlook?
• How Can I Run a Script Any Time Outlook is Started?
• How Can I Determine the Age, in Years, of an Outlook Contact?
• How Can I Save the Attachments for All My New Outlook Messages?
• How Can I Convert an Extension Number to an Actual Phone Number?
• How Can I Create a New Folder in Microsoft Outlook?
• How Can I List the Addresses in My Sent Items Folder in an Excel Spreadsheet?
• How Can I Get a List of Appointments for a Specific Month?
• How Can I Save Emails That Are More Than One Month Old?
• How Can I Create a New Outlook Distribution List Based On the Membership of an Active Directory Group?
• How Can I Save All My Contacts as VCards?
• How Can I Delete Unread Emails That Are More Than 6 Months Old?
• How Can I Randomly Select an Email From an Outlook Mail Folder?
• How Can I List All the Meetings Scheduled By a Specified Person?
• How Can I Filter Outlook Messages By Email Address?
• How Can I List All the Members of a Microsoft Outlook Distribution List?
• How Can I Determine the Follow-Up Status of Outlook Emails?
• How Can I Tell If Any of My Contacts Have a Birthday This Month?
• How Can I Delete All the Messages in My Sent Items Folder?
• How Can I Start Outlook If It Isn’t Already Running?
• How Can I Connect to the Junk Mail Folder in Outlook?
• How Can I Get Access to a Mail Folder That Isn’t a Subfolder of My Outlook Inbox?
• How Can I Get a List of All the Senders’ Email Addresses in an Outlook Folder?
• How Can I Delete All the Email Sent From a Specific Person?
• How Can I Write a Script That Accesses All the Subfolders in My Outlook Inbox?
• How Can I Get Total Size and Number of Items in an Outlook Folder?
• How Can I Convert an Outlook Email Message into a Text File?
• How Can I Get a List of All the .PST Files on a Computer?

Microsoft PowerPoint
• How Can I Retrieve the User Name and User Initials From Microsoft PowerPoint?
• How Can I Copy Data from Excel to PowerPoint?
• How Can I Apply a New Template to a PowerPoint Presentation?
• How Can I Configure PowerPoint to Print Handouts Instead of Slides?
• How Can I Run a PowerPoint Slide Show From a Script?

Other Office Tasks
• How Can I Use Windows PowerShell to Look at All the Microsoft Office Documents in a Folder?
• How Can I Change the User Information in Microsoft Office?

Hi,

Thank you for looking into this problem.

I have a workbook in which I have a summary sheet of all the data/calculations I need to refer-to/make-use-of in order to analyze some key pointers. I am using VBA to dynamically allocate a certain number of rows (ModuleStability) in that summary sheet and then populate those rows with data from other worksheets in my workbook with the help of another macro. I have created a stacked column chart to compare two things and the problem I was facing was that I needed to update that chart based on this new data that I populated the rows with. I wrote the below-mentioned macro to select/update the chart's data range. Now, the problem I'm facing with this is that while I can concretely define numbers for range extremes, I can't seem to figure out how to abstractly tell excel to set the source data for the chart to row:4 through (allocated variable) and column:C F G are used.

The actual code for the chart itself starts on line 13 (excluding blank lines), and everything before that (Line 1-12) is for summarizing/aggregating the rows that I populated with the previous macro I used. Clearly, I have had success with the summarizing even though there is a dynamically allocated variable (ModuleStability) involved with it's calculations but fail to do so with the chart. After reading through forums for a week and trying out different things, I have given up and finally posted on here. So any help would be really appreciated and am ready to get flamed for mistakes since this would be my first time posting.

Sub CalculatingStabilityRisk()
'
' CalculatingStabilityRisk Macro
'
ModuleStability = InputBox("Enter the number of modules including Module 1", "Number of modules", 1) 'Typically user would enter 5 or 6 and then it will drop down 5 rows, the implementation of which takes place in another macro
ModuleStability = ModuleStability + 4 'Since calculations will start from row 4
Range("D" & ModuleStability).Select
R1 = ActiveCell.Row
ActiveCell.Value = Application.Sum(Range(Cells(4, 4), Cells(R1 - 1, 4)))
ActiveCell.Value = Application.Sum(Range(Cells(4, 4), Cells(R1 - 1, 4)))
Range("E" & ModuleStability).Select
R2 = ActiveCell.Row
ActiveCell.Value = Application.Sum(Range(Cells(4, 5), Cells(R2 - 1, 5)))
ActiveCell.Value = Application.Sum(Range(Cells(4, 5), Cells(R2 - 1, 5)))

'(RowOffset has a -1 is since there exists one row by default)
RowOffset = ModuleStability - 1
Set ChartRange.Value = Sheets("Summary").Range(Cells(3, 3), Cells(RowOffset, 3))
ActiveSheet.ChartObjects("Chart 17").Activate
ActiveChart.SetSourceData Source:=ChartRange.Value
End Sub

'I also would typically want the chart range above to include Cells(3, 6) to (RowOffset, 6) for column F and Cells(3, 7) to (RowOffset, 7) for column G but whatever I have seems to be buggy.

Thank you once again!

Hello Everyone,

First I would like to thank anyone in advance who is willing to tackle this problem with me.

New guy here. I've been working on this Macro that splits up my data from a master sheet and splits it into many different tabs and names them according to the account number which is in the far most right coloumn. It groups all of the specific accounts activity in the one tab.

The problem I have is after I copy about 15 sheets or so it brings up this error:

Excel cannot complete this taks with available resources. Choose less data
or close other applications.

I push OK

then it says:

Run-Time error '1004':

PasteSpecial method of Range class failed

I push Debug

it highlights

mySht.Range("A1").PasteSpecial xlPasteValues

If i push End

it says:

The picture is too large and will be truncated.

I push OK

and it comes up two more times and the book closes.

vba code

Option Explicit

Private Declare Function OpenClipboard Lib "user32" _
(ByVal hwnd As Long) As Long
Private Declare Function CloseClipboard Lib "user32" () As Long
Private Declare Function EmptyClipboard Lib "user32" () As Long

Sub ExportDatabaseToSeparateFiles()
'Export is based on the value in the desired column

Dim myCell As Range
Dim mySht As Worksheet
Dim myName As String
Dim myArea As Range
Dim myShtName As String
Dim KeyCol As Integer

myShtName = ActiveSheet.Name
KeyCol = InputBox("What column # within database to use as key?")

Set myArea = ActiveCell.CurrentRegion.Columns(KeyCol).Offset(1, 0).Cells

Set myArea = myArea.Resize(myArea.Rows.Count - 1, 1)

For Each myCell In myArea
On Error GoTo NoSheet
myName = Worksheets(myCell.Value).Name
GoTo SheetExists:
NoSheet:
Set mySht = Worksheets.Add(Before:=Worksheets(1))
mySht.Name = myCell.Value
With myCell.CurrentRegion
.AutoFilter Field:=KeyCol, Criteria1:=myCell.Value
myCell.Parent.Cells.SpecialCells(xlCellTypeVisible).Copy
mySht.Range("A1").PasteSpecial xlPasteValues
mySht.Range("A1").PasteSpecial xlPasteFormats
mySht.Cells.EntireColumn.AutoFit
.AutoFilter
ClearCipboard
Application.CutCopyMode = False

End With
Resume
SheetExists:
Next myCell

End Sub

Sub ClearClipboard()
OpenClipboard Application.hwnd
EmptyClipboard
CloseClipboard
End Sub

end vba

Thanks so much for your help...

Dejan

I am sorry if this is a Duplicate post, i searched and could not find anything that was what would work for me.

This is my problem. I have two sheets, one has data in a table with column headings and the other is formula or calculation sheet. On the calculation sheet I have six sets of three columns each. The first five sets are basically the same, Ball #1, Number of Times Drawn, Date Last Drew. The only column that will change the column heading is the Ball #, it will go from 1 to 2 to 3 to 4 to 5. The sixth column has the same two columns, Number of Times Drawn and Date Last Drew. The first column in the sixth column set is labeled Mega Ball. (See Pics Attached Below)

Yes I am doing a stats analyses of lottery numbers and there drawings for a project.

This is my problem, I can't get the Date Last Drew to work. I have tried the following formula:
=INDEX(Drawing_Date[Date],SUMPRODUCT(MAX(A2)*ROW(A2))-ROW(Drawing_Date[Date])+1)

The original formula was slightly different, in the MAX() function and the *ROW() function, I had "Item=A2" in the () but I received a #Name error. I figured out that Excel didn't know what "Item" was so I changed it to just the cell reference, A2. The data sheet has a table named Table1, side note Excel doesn't see Table 1 it only sees Table8 through 14(??), with the column headings Date, Ball #1, Ball #2, Ball #3, Ball #4, Ball #5, Mega Ball, and Multiplier. So when the formula is referencing the range on the data sheet I am using "Drawing_Date[Date]" to specify the range of draw dates.

When I run the formula I get a date, that seems random and is clearly wrong, and I can not figure out where it is pulling it from. I have pasted in a couple of snap shots of the two sheets for you to see. I am not even sure if I am on the right track any more. I have read so much trying to figure this out that my brain seems to be turning to mush!!

Please note that I really want a VBA solution as I am in no way shape or form good with VBA. If I can figure out the formula/function for the first one I think I can adjust it for the rest. (I hope )

Thanks for all the help!!
Mike C

Calc Sheet Snap Shot
Calc_Sheet.JPG

Data Sheet Snap Shot
Data_Sheet.jpg

Hello everyone,

I have imported an MSSQL database table into my Excel spreadsheet. I
then placed four combo boxes on the first sheet. One of them allows you
to browse a column from the database sheet and select a cell. I want
the others to follow the first and show the matching cell two columns
over.

Example: My table has ten rows of four columns. The four columns are
part name, part number, price and expiration date. The first combo box
is a drop down list of all part numbers that start with the letter E.
This is only four of the ten rows. When a user pick the part number I
want the next box to show the part name which is one cell left on the
same row.

How do I do this or is there a better way than to use a second combo
box? Can it be done with some other item easier and if so how. Below is
the code running my combo boxes.

### CODE ###

Private Sub ComboBox1_Change()

Dim myRng As Range
Dim myCell As Range
Dim myPfx As String

With Worksheets("SQL")
Set myRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))
End With

Me.ComboBox2.Clear
Me.ComboBox3.Clear
Me.ComboBox4.Clear

If Me.ComboBox1.ListIndex < 0 Then
'do nothing
Else
Select Case Me.ComboBox1.ListIndex
Case Is = 0 'All Parts
myPfx = "*"
Case Is = 1 'Assembly
myPfx = "as*"
Case Is = 2 'Standard Part
myPfx = "aa*"
Case Is = 3 'Adhesives
myPfx = "ca*"
Case Is = 4 'Bagging
myPfx = "cb*"
Case Is = 5 'Core
myPfx = "cc*"
Case Is = 6 'Fabric
myPfx = "cf*"
Case Is = 7 'Mold Release
myPfx = "cm*"
Case Is = 8 'Prepreg
myPfx = "cp*"
Case Is = 9 'Resin
myPfx = "cr*"
Case Is = 10 'Thickner
myPfx = "ct*"
Case Is = 11 'Abrasives
myPfx = "fa*"
Case Is = 12 'Paint
myPfx = "fp*"
Case Is = 13 'Hardware
myPfx = "h0*"
Case Is = 14 'Tooling Material
myPfx = "tm*"
Case Is = 15 'Maint. Equipment
myPfx = "me*"
Case Is = 16 'Maint. Building
myPfx = "mb*"
Case Is = 17 'Supplies Manufacturing
myPfx = "sm*"
Case Is = 18 'Supplies Cleaning
myPfx = "sc*"
Case Is = 19 'Supplied Office
myPfx = "so*"
Case Is = 20 'Supplies Packaging
myPfx = "sp*"
Case Is = 21 'Supplies Safety
myPfx = "ss*"
Case Is = 22 'Mining
myPfx = "10*"
Case Is = 23 'Glass Fixturing
myPfx = "20*"
Case Is = 24 'Auto Racing
myPfx = "60*"
Case Is = 25 'Aircraft
myPfx = "90*"
Case Is = 26 'HANS
myPfx = "hans*"
Case Else
myPfx = "*" 'just in case
End Select
End If

For Each myCell In myRng.Cells

If LCase(myCell.Value) Like LCase(myPfx) Then
Me.ComboBox2.AddItem myCell.Offset(0, 3)
End If

If LCase(myCell.Value) Like LCase(myPfx) Then
Me.ComboBox3.AddItem myCell.Offset(0, 0)
End If

If LCase(myCell.Value) Like LCase(myPfx) Then
Me.ComboBox4.AddItem myCell.Offset(0, 5)
End If

Next myCell
'

End Sub

###

The combo box 1 is what sorts out the data by type. Combo box 2 allows
me to then select from a reduced list of parts by the part name. I want
boxes 3 and 4 to display based on the results found in 2 but don't know
how to code it.

Thanks
LWhite

Working with a macro I found at http://www.contextures.com/excelfiles.html
and I am stumped. To be honest, I am not strong with VBA, just walking
through it logically isn't getting it, and I am hoping the guru's on this
board can help me wake up and see the problem. :o)

Two issues:

1. (See code at the end of this message). My raw data sheet contains 1900
rows, and A to Y columns. The sheets create and the data is distributed
properly but something strange is happening... the values in columns A
through N are transfering just fine, but columns O through Y are not. I
tried another approach based on this macro
http://www.rondebruin.nl/copy5.htm#all and oddly enough the same columns of
data had the same problem.

Sample Data:
A B C
LastName, FirstName Title Department.....

The worksheets created sort the employees by department. Cells A, B, C, L,
O, R, U & X are text and all others are $.

2. How can I get the macro below to perform AutoFit on all the worksheets
it creates?

Thanks so much for your help as always.

Scott

Macro I am using now:

Sub FilterCities()

Dim myCell As Range
Dim wks As Worksheet
Dim DataBaseWks As Worksheet
Dim ListRange As Range
Dim dummyRng As Range
Dim myDatabase As Range
Dim TempWks As Worksheet
Dim rsp As Integer
Dim i As Long

'include bottom most header row
Const TopLeftCellOfDataBase As String = "A1"

'what column has your key values
Const KeyColumn As String = "C"

'where's your data
Set DataBaseWks = Worksheets("AAA Master")
i = DataBaseWks.Range(TopLeftCellOfDataBase).Row - 1

rsp = 6

Set TempWks = Worksheets.Add

With DataBaseWks
Set dummyRng = .UsedRange
Set myDatabase = .Range(TopLeftCellOfDataBase, _
.Cells.SpecialCells(xlCellTypeLastCell))
End With

'rebuild the List
With DataBaseWks
Intersect(myDatabase, .Columns(KeyColumn)).AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=TempWks.Range("A1"), _
Unique:=True

'Add the heading to the criteria area
TempWks.Range("D1").Value = _
.Cells(.Range(TopLeftCellOfDataBase).Row, KeyColumn).Value
End With

With TempWks
Set ListRange = .Range("a2", .Cells(.Rows.Count, "A").End(xlUp))
End With

With ListRange
.Sort Key1:=.Cells(1), Order1:=xlAscending, _
Header:=xlNo, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom
End With

'check for individual Team worksheets
For Each myCell In ListRange.Cells
If WksExists(myCell.Value) = False Then
Set wks = Sheets.Add
On Error Resume Next
wks.Name = myCell.Value
If Err.Number <> 0 Then
MsgBox "Please rename: " & wks.Name
Err.Clear
End If
On Error GoTo 0
wks.Move After:=Sheets(Sheets.Count)

Else
Set wks = Worksheets(myCell.Value)
wks.Cells.Clear
End If

'change the criteria in the Criteria range
TempWks.Range("D2").Value = "=" & Chr(34) & "=" & myCell.Value &
Chr(34)

'transfer data to individual Team worksheets
If rsp = 6 Then
myDatabase.AdvancedFilter _
Action:=xlFilterCopy, _
CriteriaRange:=TempWks.Range("D1:D2"), _
CopyToRange:=wks.Range("A1").Offset(i, 0), _
Unique:=False
Else
myDatabase.AdvancedFilter _
Action:=xlFilterCopy, _
CriteriaRange:=TempWks.Range("D1:D2"), _
CopyToRange:=wks.Range("A1"), _
Unique:=False
End If
Next myCell

Application.DisplayAlerts = False
TempWks.Delete
Application.DisplayAlerts = True

End Sub
Function WksExists(wksName As String) As Boolean
On Error Resume Next
WksExists = CBool(Len(Worksheets(wksName).Name) > 0)
End Function

I have a Worksheet that takes the user's input and then gives them an option to print other different Worksheets in the same workbook.

Before actually printing the other Worksheets, I have VBA code running that modifies the other Worksheets before printing them. At least that's what should happen.

The issue I'm having is that I have two If statements that execute to change the value of two cells. These cells are linked to ActiveX Checkboxes. (Basically it checks to see which of the two checkboxes needs to be checked and changes the value of the linked cell for each Checkbox to TRUE or FALSE).

The problem I'm having is that it prints the Worksheet before the Checkboxes have a chance to update based on the changed linked Cell values.

Below is my code:
For reference,
Cell BA8 (in Worksheet "Encounter Form") is linked to the "New" Checkbox
Cell BF8 (in Worksheet "Encounter Form") is linked to the "Return" Checkbox
Private Sub StampPrintEncounterForm_Click()

Call Stamp_EncounterForm
Call Print_EncounterForm

End Sub
Public Sub Stamp_EncounterForm()

'Clear Values of Return/New
Worksheets("Encounter Form").Range("BA8", "BF8").Value = "False"

' Set New/Return Status
If Worksheets("Log").Range("e12").Value = "New" Then
    Worksheets("Encounter Form").Range("BA8").Value = "True"
Else
    'Worksheets("Encounter Form").Range("BA8").Value = "False"
End If

If Worksheets("Log").Range("e12").Value = "Return" Then
    Worksheets("Encounter Form").Range("BF8").Value = "True"
    Else
    'Worksheets("Encounter Form").Range("BF8").Value = "False"
End If

'Encounter Form
Worksheets("Encounter Form").Range("ah53").Value = ActiveSheet.Range("b11") 'Name
Worksheets("Encounter Form").Range("ah54").Value = ActiveSheet.Range("b12") 'MRUN
Worksheets("Encounter Form").Range("ah55").Value = ActiveSheet.Range("b13") 'DOB
Worksheets("Encounter Form").Range("aq55").Value = ActiveSheet.Range("c13") 'Gender
Worksheets("Encounter Form").Range("au55").Value = ActiveSheet.Range("d13") 'Race
Worksheets("Encounter Form").Range("BI1").Value = ActiveSheet.Range("e13") 'PT Arrival #
End Sub
Public Sub Print_EncounterForm()

'Print Encounter Form
Worksheets("Encounter Form").PrintOut Copies:=1, Preview:=False 'Duplex:=1

End Sub
Every time I execute these "Stamp" and "Print" modules together, it prints out the form with all of the blue code updated in the Worksheet but without the Red code fully executed to Check the "Return" or "New" checkboxes. It usually prints whatever the last "Stamp" had put there.

Is there a way to force my print code to wait until my checkboxes have been updated with the new values from the VBA code in my If statement?

Hi Folks,

I have a workbook that receives a daily extract from a database, from this data a bunch of calculations are performed. I need assistance in having these formula added or removed from the adjacent cells to allow for calcualations on other sheets to be correct.

In the attached workbook the two possibilities are shown where the calculations (columns AF-AH) do not go to the same number of rows as the data extracted from the database.

The easy option would be to continue correcting this on a daily basis via cut/fill, but I have had to hand this off to others, who have proven to not be as diligent in ensuring that the formula's are added or removed when neccessary.

Hence asking for assistance with a Macro better than the one that I have been able to create from browsing here.
The attached code whilst it works, takes an unworkabley long time and not all formula's work when created from the code insertion Column AO & AP in particular.

Sub InsertFormula()
  
  ' this is not currently used
  
  'Thread: http://www.excelforum.com/excel-programming/768583-help-inserting-formula-into-cells-using-vba.html
  
 
  Dim Cell As Range
  Dim LastRow As Long
  Dim i As Integer
  Dim InRowCount As Integer
  Dim R As Long
  Dim Wks As Worksheet
  
    Set Wks = ActiveWorkbook.Sheets("Today's Data Short")
    LastRow = Wks.UsedRange.Rows.Count - Wks.UsedRange.Row + 1
    
With Application
    .DisplayAlerts = False
    .EnableEvents = False
    .ScreenUpdating = False
End With
    
    InRowCount = LastRow
       For i = 2 To InRowCount
            Wks.Cells(i, "AH").ClearContents
            Wks.Cells(i, "AI").ClearContents
            Wks.Cells(i, "AJ").ClearContents
            Wks.Cells(i, "AK").ClearContents
            Wks.Cells(i, "AL").ClearContents
            Wks.Cells(i, "AM").ClearContents
            Wks.Cells(i, "AN").ClearContents
            Wks.Cells(i, "AO").ClearContents
            Wks.Cells(i, "AP").ClearContents
            Wks.Cells(i, "AQ").ClearContents
            Wks.Cells(i, "AR").ClearContents
            Wks.Cells(i, "AS").ClearContents
       Next i
      
      For R = 2 To LastRow
        If Wks.Cells(R, "AC") <> "" Then
            ' Delay Verbose
            Wks.Cells(R, "Ah").Formula = "place holder"
            ' Just Job Creted Date
           Wks.Cells(R, "Ai").Formula = "=TRUNC(AF" & R & ")"
            ' Jobs status
            Wks.Cells(R, "Aj").Formula = "=IF(A" & R & "=""Pending Ops
Update"",""Not Started"",IF(Q" & R & "="""",IF(J"
& R & "=PanelsAtRisk,Properties!$G$1,E" & R & "),""Booked with
Customer""))"
            ' PreBuilt Install Date
            Wks.Cells(R, "Ak").Formula = "=TRUNC(Q" & R & ")"
            ' Built Install from Deposit
            Wks.Cells(R, "Al").Formula = "=IF(AG" & R & "=0,IF(TRUNC(T" & R &
")=0,40715,TRUNC(T" & R & ")+84),AG" & R & ")"
            ' Built Install date
            Wks.Cells(R, "Am").Formula = "=IF(Q" & R &
"=""01/01/1900"",AG" & R & ",IF(E" & R & "=""On
Hold"",401404,AH" & R & "))"
            ' Useable Forecast date
            Wks.Cells(R, "An").Formula = "=IF(Q" & R & "="""",IF(Z"
& R & "<>"""",""Provisional Booking"",IF(AF" & R &
"=""On Hold"",""On Hold"",IF(Z" & R &
"="""",AH" & R & ",""""))),""Already
Booked"")"
            ' do not use
            Wks.Cells(R, "Ao").Formula = "{=Y" & R & "*1}"
            ' Install Region
            Wks.Cells(R, "Ap").Formula = "=IF(AO" & R &
"=0,"""",VLOOKUP(AO" & R & ",Regions!$A$2:$D$" & R &
",3,FALSE))"  
            ' Installation kW
            Wks.Cells(R, "Aq").Formula = "=IF(Q" & R &
"="""","""",H" & R & ")"
           ' Kw to install
            Wks.Cells(R, "Ar").Formula = "=IF(Q" & R & "="""",H"
& R & ","""")"
            ' Provisional booking Date
            Wks.Cells(R, "As").Formula = "=IF(Q" & R &
"="""",TRUNC(Z" & R & "),1)"
         End If
       Next R


With Application
    .DisplayAlerts = True
    .EnableEvents = True
    .ScreenUpdating = True
End With

End Sub
Any and All assistance is appreciated

Thanks in Advance
Iain


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