Microsoft Excel

130K

Solutions

195

Articles & Videos

37K

Contributors

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.

Share tech news, updates, or what's on your mind.

Sign up to Post

I have this solution
need to improve with vba the count

add an countig to the column A in count sheet

num3.png

also Need add
blank column in sheet PAIR  between CD, DE
num3a.pngnum3data.xlsm
0
PeopleSoft Has Never Been Easier
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

I have a folder which contains several thousand .pdf files.
I would like a program which can
1. find a file in a folder based on the file name
2. copy that file
3. paste that file to another folder

All these "source" files will be in .pdf format in a single folder.
I need it to do this in a "batch" format where you can specify several file names in a given folder and copy all those specified files over to a different folder.
I have used Excel to generate a list of the file names that I want to copy/paste.

Thanks in advance for any insight on how to accomplish this!
0
Hi all.

I have an Excel macro template that used to pull data from our old SQL Server 2000 and populate the Excel file. It would complete this task within 3 seconds.

Now, we have switched the data source to be our SQL Server 2008 server and now the same Excel file takes close to 35 seconds to populate the Excel file. The only line that has changed is the connection string line:

OLD SERVER:
strConn1 = "DRIVER=SQL Server;SERVER=myOLDServer;UID=me;APP=Microsoft Office 2007;WSID=myWork;DATABASE=myOLDDB;Trusted_Connection=Yes"

Open in new window


NEW SERVER:
strConn1 = "Provider=SQLOLEDB;Data Source=myServer;Initial Catalog=myDB;Integrated Security=SSPI;"

Open in new window


What do I have to do to make it run at the same speed as when it was pulling data from SQL Server 2000? The file only populates about 5-10 lines.

Below is the code for populating the Excel file, the connection string is for the SQL Server 2008 data source
Private Sub PopulateExcel()

Dim cnPubs1 As ADODB.Connection
Set cnPubs1 = New ADODB.Connection


Dim strConn1 As String

strConn1 = "Provider=SQLOLEDB;Data Source=myServer;Initial Catalog=myDB;Integrated Security=SSPI;"

cnPubs1.Open strConn1

Dim rsPubs1 As ADODB.Recordset
Set rsPubs1 = New ADODB.Recordset

With rsPubs1
.ActiveConnection = cnPubs1

    Dim I1
    Dim rng1 As Range
    Set rng1 = Range("B8")
    Dim strSQL1 As String

strSQL1 = "SELECT  PO2_PurchaseOrderEntryLine.ItemNumber, PO2_PurchaseOrderEntryLine.Vendor_AliasItemNumber,  

Open in new window

0
I have this Summary tab and different supplier tabs that are linked from an access table that I need information pulled from to populate the Summary sheet.  Wanted to see if this is possible to accomplish by entering a number instead of manually entering this information.

Basically if you enter the RFQ number in cell B3 then it would pull it's corresponding row information and populate the specific cells in the summary sheet tab.  See example in the attachment.
C--Users-Luis.Freund.EXT-Desktop-Lu.xlsm
0
I've been trying to reset the colors in all boxes (RangeName = "Boxcolors") to yellow by writing a Macro.  Unfortunately, my VBA skills are not that good.  If you're up for it, take a look at the Macro for resetting the boxes to yellow.  It's a small macro that requires a tweek.

Much thanks in advance,

B.
C--Data-X-Data-Trash-Clear-Map.xlsm
0
Hi

I have a formula in a cell ("I282") and I want to run a macro whenever the result changes. I have entered the following code in the worksheet change event:

Application.EnableEvents = False
If Target.Address = Range("I282").Address Then
MsgBox "Here"
    Range("J282").Value = Range("BD302").Value
End If
Application.EnableEvents = True

...but nothing is happening. It isn't even displaying the message box (which I inserted to make sure it was recognising the cell), when the value in the cell changes.

If I change the target cell to a cell without a formula in it, and change the value of this cell it works fine.

Any thoughts?

Regards

Terry
0
How would i upload and retrieve multiple images/files of an individual in a database. for example one can upload five (5)  images/files to MR. A and 3 Images/files to Mr. B and then how would i retrieve the Images/files individually using the Mr. A or Mr. B ID as Key Using Userform in Excel VBA
0
EE Pros,

I have some merged cells that I'd like to be able to double-click and have the color in the box change to Yellow, Red and Green.

Is there an easy way to have a macro change the box color when double-clicked?

Much thanks in advance,

B.
C--Data-Dev-Mapping-Strategy-AddCol.xlsm
0
Dear Experts,
I created a pivot table based on two sheets data. Both sheets data is connected with a unique column data by creating relationship. The data displayed based on the relationship created is great.

When I tried to use "Show Report Filter Pages" to create multiple worksheets, I observed that the option is greyed out (even after placing one of the fields under report filters).

I observed that if the pivot is based on a single sheet data, "Show Report Filter Pages" option is available.

Am I doing any mistake? or Is there any other way to use this option when I create pivot table with two different data sheets?

Thanks in advance.
0
Hi All i have this excel sheet which have these post codes in column G.

You can see there are drop numbers in column B for them, but if you go down to the bottom there are some which are not slotted in there right position.

how can i slot them in to the correct position in above list.

all i want is all post code starting with there initial 4 character slot in to there corresponding slots and once there are in i can assign them the drop sequence.

Please help

Thanks
0
Industry Leaders: We Want Your Opinion!
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Hello:

Attached are my two spreadsheets.  I need for the first one to look like the second one, in that the listed companies need to be in columns and not in rows.

How do I accomplish this?

Thank you!

John

UserCompanyAccess.xlsm
UserCompanyAccess-v7.xlsx
0
Hello,

This question is in follow-up to another recent thread located here:

Modify formatting of selected intracellular text

In that thread, a VBA solution was provided to alter the formatting of certain portions of an Excel text entry while leaving the remaining portions unchanged. That solution (as far as I can determine with my limited knowledge of VBA) operates by including, within the code, the literal strings of text (ie the specific words) which are to be reformatted.

The follow-up question now is:

Can the VBA code described above be modified so that in place of the strings of text (which need to be reformatted), it contains variables which obtain those strings from say, an adjacent column in the spreadsheet?

I'm attaching a file that illustrates two possible options for the functionality I've got in mind. The first option (in the sheet tab labeled Text_Strings) is shown in the first screenshot below. The second option (in the sheet tab labeled MID_Function) is shown in the second screenshot below.

Note: I believe the second option (MID_Function), if it is doable, is a much better way to go than the first since it allows for a more precise designation of the text strings to be reformatted and also eliminates potential problems due to duplicate or repeated strings.

Option #1 Text_Strings
0
Hello!

I have created a button that I want to allow the user to be able to print just certain sheets of the workbook. I am wanting to create a macro for the button that prints columns A-J, but NOT column C, from the worksheet "summary". Then I would also like the worksheet called "service rates" to print right after the "summary" sheet. It would also be great if both sheets fit to the page so that they aren't being cut off!

Thanks so much :)
0
i have this solution but need to improve with vba
to count the triples from 4 numbers
num4triple.xlsm
0
I had this question after viewing Set font smaller and allow box to expand.

Assistance is greatly appreciated.
0
Hi all

 I'm hoping you can offer some advice please!! I have googled as much as I can to get to this point. I have a workbook, and I would like some coding that identifies if a contract is a "Contract - Framework" AND has a status of "Contract Awarded" on the "Procurement Tracker" tab. I would then like the relevant rows that meet this criteria to be copied over to another tab called "Live Contracts" and hidden on the original Procurement Tracker tab.

 This is the formula I have so far that will move the rows that meet the "Contract - Framework" criteria, I would just like to know how to amend it so that the row meets both conditions;

 
Sub ReqToLive()
 Dim xRg As Range
 Dim xCell As Range
 Dim I As Long
 Dim J As Long
 I = Worksheets("Procurement Tracker").UsedRange.Rows.Count
 J = Worksheets("Live Contracts").UsedRange.Rows.Count
 If J = 1 Then
 If Application.WorksheetFunction.CountA(Worksheets("Live Contracts").UsedRange) = 0 Then J = 0
 End If
 Set xRg = Worksheets("Procurement Tracker").Range("F1:F" & I)
 On Error Resume Next
 Application.ScreenUpdating = False
 For Each xCell In xRg
 If CStr(xCell.Value) = "Contract - Framework" Then
 xCell.EntireRow.Copy Destination:=Worksheets("Live Contracts").Range("A" & J + 1)
 xCell.EntireRow.Hidden = True
 J = J + 1
 End If
 Next
 Application.ScreenUpdating = True
 End Sub

Open in new window

Hope that makes sense! Thanks in advance
 Amy
0
I have been attempting to use this Macro that I found through VBA Express.  The Macro successfully disables the ability to right-click cut/copy/paste but the keyboard shortcuts are still active.  Any help is greatly appreciated.  I have very little knowledge about writing Macros and usually go to Google to find already existing codes to use.  Please see below:

'*** In a standard module ***
Option Explicit
 
Sub ToggleCutCopyAndPaste(Allow As Boolean)
     'Activate/deactivate cut, copy, paste and pastespecial menu items
    Call EnableMenuItem(21, Allow) ' cut
    Call EnableMenuItem(19, Allow) ' copy
    Call EnableMenuItem(22, Allow) ' paste
    Call EnableMenuItem(755, Allow) ' pastespecial
     
     'Activate/deactivate drag and drop ability
    Application.CellDragAndDrop = Allow
     
     'Activate/deactivate cut, copy, paste and pastespecial shortcut keys
    With Application
        Select Case Allow
        Case Is = False
            .OnKey "^c", "CutCopyPasteDisabled"
            .OnKey "^v", "CutCopyPasteDisabled"
            .OnKey "^x", "CutCopyPasteDisabled"
            .OnKey "+{DEL}", "CutCopyPasteDisabled"
            .OnKey "^{INSERT}", "CutCopyPasteDisabled"
        Case Is = True
            .OnKey "^c"
            .OnKey "^v"
            .OnKey "^x"
            .OnKey "+{DEL}"
            .OnKey "^{INSERT}"
        End Select
    End With
End Sub
 
Sub EnableMenuItem(ctlId As Integer, Enabled As Boolean)
     …
0
Hi

Is it possible to call a function (in eg Module1) in an Excel cell?
0
I had this question after viewing Produce the count value next to each filterable item in a spreadsheet.

I am using this macro with data from another sheet and the results are cut-off due to all the data not fitting in dialog.

If possible, the results dialog should automatically expand as necessary to fit all the text to the right, and be in a smaller font size.
CountItemsv2.xlsm
0
Creating Instructional Tutorials  
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

I have an excel sheet. One column has company names and another column has company 1 line description, there is multiple description entries (of different types) for same company. So, company ABC is repeated 10 times in column A with 10 corresponding different descriptions in column B, then company PQR is repeated 16 times in column A with 16 corresponding different descriptions in column B (after company ABC's entry).

Out come needed : Find all the unique words for company ABC from all the description lines corresponding to Company ABC and display count of every unique word for Company ABC ( e.g. "good" : 5 times , "the" :10 times , "big" : 7 times etc). And similarly for company PQR and other companies in the sheet.
0
Dear macro experts, please help.
I would like the macro to adjust (shrink or increase) row height anywhere in sheet depends of the text in cells. The rows and columns have Merged Cells and Wrapped Text. The text is always in other two rows and in same 11 column from B to L. Column length is static. That two rows need to be same size and the text need to fit.
Adjust.xlsx
0
Experts:

I have come across a wonderful (ready-to-use) Excel sample file that allows multiple values to be selected (in single cell) via a drop-down box.   I found the example at:  https://trumpexcel.com/select-multiple-items-drop-down-list-excel/

I would like to modify the code provided by Sumit Bansal.   Specifically, I need multiple drop-down (i.e., for every record in an XLS, I need the drop-down for assigning POCs).

Sumit's code below allows to use the drop-down for cell C2 only though (line: "If Target.Address = "$C$2" Then").   For testing purposes, I tried modifying the code with only two cells.   The proposed VBA "If Target.Address = "$C$2" Or "$C$3" Then", however, does not work.

My question:   If I wanted the drop-down to work for, e.g., 50 rows, how can I change the code?   That is, "If Target.Address = "$C$2:$C$51" Then" does **not** work.

Thank you in advance.
EEH





Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
'Code by Sumit Bansal from https://trumpexcel.com
' To Select Multiple Items from a Drop Down List in Excel
Dim Oldvalue As String
Dim Newvalue As String
Application.EnableEvents = True
On Error GoTo Exitsub
If Target.Address = "$C$2" Then
  If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
    GoTo Exitsub
  Else: If Target.Value = "" Then GoTo Exitsub Else
    Application.EnableEvents = False
    Newvalue = Target.Value
    Application.Undo
    Oldvalue = Target.Value
      If Oldvalue 

Open in new window

0
I am attaching a file with dummy data. How can I format the scatter series on this chart so that the data points are clearer e.g in the CD111 control chart the data points are bunched together. in the PB 208 control chart the data points are a bit clearer but the labels are still overlapping. In SB121 control chart, they are also a bit melded together :-)
How can I make them easier to read? I'm using Excel 2010 so I wouldn't have all the capability of 2013...thanks.
Control_chart_multiple_data_points_.xlsx
0
Please help! I'm receiving a "Problems During Load" prompt when I try and open up my XML file. I am able to open it in NotePad, but I can't seem to get it open no matter what I do. I would really appreciate if someone could take a look!

I attached the file.
BCNMitosisCopy.xml
0
How do deselect a cell or range of cells in Excel Office 365 2016?

CTRL +click does nothing  -  clicking in an empty cell and hitting delete does nothing

Microsoft, where is the instruction?

Thanks to anyone who can help me get pass this.
0

Microsoft Excel

130K

Solutions

195

Articles & Videos

37K

Contributors

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.