Free Microsoft Excel 2013 Quick Reference

Number of rows in excel worksheet Results

Hi,
I'm looking for some Excel VBA code which will allow me to copy data from multiple workbooks (just the one worksheet is contained in each workbook) into a single spreadsheet (one worksheet). The number of columns will remain fixed. However, the number of rows with data in each workbook will be variable. I would want the data to be copied immediately underneath each other with no empty rows in the single worksheet.

Any help on this would be greatly appreciated.

Thanks.

Hi,

Many thanks in advance for your help
I've tried searching here and on the web for an answer, and I can't find one that fits.

I volunteered to support the local school by taking some administrative workload from the teachers. Twice a year they have to check the inventory of all the school items, from books to kitchenware. I have made them an Excel spreadsheet (summary example attached) that contains in column A the class or room where the items are, and the item information in the adjacent columns (B to H), from which I have to print individual reports based on the room the items are in, for the responsible teacher to check, add or delete all the items that are supposedly there.
Now comes my problem. I can't seem to figure out how to write the macro code so that each room's data would either print on a new page or (preferred, as this would enable selective room printing) get saved automatically as a pdf file, with the room as the file name, with no confirmation dialog box.
I am an average user of Excel and have tried to look for a macro that could:
1. Select all the rows of the spreadsheet
2. Sort the spreadsheet alphabetically in descending order by two columns: column A (room) and column B (object)
3. Either:
a. Save as pdf one file per room, taking the individual pdf file name from the column A (room)
b. Print one file per room as pdf (we use free Cutepdf software), taking the individual pdf file name from the column A (room).
If needed, this is some additional information about the worksheet: Each item is entered on a single row, but multiple rows will be in the same room region. The total number of rows changes unpredictably over time, so a room may take more than one page to print (and that's okay).

Hi Folks,

This request for help might seem strange at first, so bear with me.I have 6 specialists who each month create a billing workbook (same format each month). They each have their own directory under a parent drive on the network (parentspecialistname). This path doesn't ever change.Each month they create a workbook named YYYYMM_Specialist.xls. This naming convention never changes. The specialists are aware that if they deviate from this convention by overwriting the name change they have the potential to be fired.Each workbook each month has a worksheet named "ForExport" that they use a form to write their data to.The "ForExport" worksheet has a named range of data that is to be imported to the master spreadsheet ("MonthlyLoad"). This range is A2:D100000. Blank rows should be ignored in the range.The number of rows to import each month can vary.The workbooks are all Excel 2007.What I need to do is provide my primary analyst the ability to import the data from the "ForExport" worksheet in each of those workbook each month. The caveat is that it must also enable the analyst to import data from other months' workbooks for historical comparison purposes.

Example 1: Normal month - the analyst clicks an import button and it asks "What month to import?" (with a default to the current month in YYYYMM format). The analyst clicks the ok button to accept the current month. The code loops through the specialists folders, finds the files whose names start with the same YYYYMM entered at the prompt, and pulls the data into the master workbook and writes it row by row to the "MasterData" worksheet in the master workbook.

Example 2: Variant month - the same function as Example 1 happens. This month however they also want to compare some data (on another dashboard worksheet within the master workbook). The analyst clicks the import button again and this time when it prompts "What month to import?" the analyst enters the previous month's date (again in YYYYMM format). The code again loops through the specialists folders, but this time pulling data from workbooks whose titles start with the same "YYYYMM" as entered in the prompt. It copies the data from the workbooks matching the date and appends it to the "MasterData" worksheet.

Yes, I recommended a database and reports...but they want Excel. :|

Anyone have a clue how to do this? I'm beyond my skill at this point (I'm a very basic VBA noob), and I've been given about a week to figure out how to implement this.

Thanks,
Mike

Hi,

I would like to create a summary "view" of a basic table of detailed data. (As I am new to excel, I am not sure if I am using the term table, grid, list etc correctly, so please bear with me).

As an example, on a Detail worksheet I will have two columns, TYPE and QTY. The number of rows in this list will increase over time, and it is not known ahead of time what the distinct list of TYPEs will be.

On a Summery worksheet, I would like to see the sum total QTY for each TYPE. However, since new values of TYPE might come into the Detail worksheet at any time, the Summary list will also increase over time as a new distinct TYPE is added to the Detail worksheet.

I would like the Summary sheet to update dynamically as new data is added to the Detail worksheet.

Thanks for the help

~Hello. I'm adding a dashboard page to a complex tracking worksheet (macro-enabled, shared and protected Excel 2007). Where I'm stuck is trying to autopopulate a row showing a count of the number of dates in a ReviewDate column that fall between certain dates only if multiple criteria are met. I've attached a sample spreadsheet and mocked up someting below.

Sample excel file
Dash.xlsm

The results will be on a dashboard page needs to have nicer (brand standard) formatting for senior leaders, which makes Pivot Tables difficult to use. The spreadsheet is not that large so calc times are not a concern (yet). I've tried COUNTIF, COUNTIFS, SUMPRODUCT and everything else I could think of and have struck out. I think my main problem is the stringing the complex syntax together properly.

Your expertise and suggestions are appreciated! If I can solve for #1 below, I can figure out the rest I think. Thank you!

Example of source data

ReviewDate Status Approach LOB 7/23/2011 Not Started ILT A Finance 7/25/2011 In Progress ILT B Finance 7/26/2011 Develop WBT A Tech 7/23/2011 Not Started WBT B Finance 7/25/2011 Complete WBT B Finance 7/26/2011 Complete ILT B Finance 7/29/2011 Develop ILT B HR 7/31/2011 In Progress ILT A Finance 8/2/2011 Not Started WBT A Finance

What I'm trying to figure out is:ILTs Due = COUNT of Approach "ILT A" OR "ILT B" where LOB = "Finance" AND Status = "In Progress" OR "Not Started" AND ReviewDate is between July 11-18, 18-24, 25-31, etc.ILTs Complete = ILT A or ILT B = "Complete" and LOB = "Finance" (just a simple running total)

Example of dashboard table
[Finance]
Week of July 11 July 18 July 25 August 1 August 8 ILTs Due 2 2 1 ILTs Complete 1 WBTs Due 1 WBTs Complete 1

Hi

I have a very strange error.. I am comparing two workbooks with one worksheet per workbook. My code works perfectly fine when i give limited number of rows and columns like

ROWs Columns Result
50 10 Fine
100 100 Fine
200 200 Fine
300 300 Error - Cannot find workbook2

if i give rows n columns beyond 300 it gives me the error mentioned.. is there a restricition in Excel for comparison of rows n columns...
Kindly help !!!

Thanks
jamap

Hi Guys,
I am working with line charts and i am successively drawing line charts based on the number of rows. Each row contains one line chart and every time chart is drawn on new worksheet (e.g., if there are 5 rows then on single click 5 line charts will be drawn).
The problem that i am facing is, How to put column headings on the x-axis of each chart.
NOTE: Row heading is already appearing on the y-axis of each chart.
I have also attached my excel sheet...Here is my code:

	VB:
	
 LineCharts() 
    Dim Ws As Worksheet 
    Dim NewWs As Worksheet 
    Dim cht As Chart 
    Dim LastRow As Long 
    Dim CurrRow As Long 
     
    Set Ws = ThisWorkbook.Worksheets("Sheet1") 
     
    LastRow = Ws.Range("A65536").End(xlUp).Row 
    For CurrRow = 2 To LastRow 
        Set NewWs = ThisWorkbook.Worksheets.Add 
        NewWs.Name = Ws.Range("A" & CurrRow).Value 
        Set cht = ThisWorkbook.Charts.Add 
        With cht 
            .ChartType = xlLine 
            .SeriesCollection.NewSeries 
             
            .SeriesCollection(1).Values = "=" & Ws.Name & "!R" & CurrRow & "C3:R" & CurrRow & "C8" 
            .SeriesCollection(1).Name = "=" & Ws.Name & "!R" & CurrRow & "C2" 
            .Location Where:=xlLocationAsObject, Name:=NewWs.Name 
        End With 
    Next CurrRow 
End Sub 

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


I am trying to match a series of numbers in a certain column and either highlight the entire row or copy that entire row to another worksheet. For example: I have imported an output file that was created using SQL into excel. In column "Q" contain error codes. I want excel to look at column "Q" and copy all the same error codes to another worksheet or highlight the entire row. I've tried using a macro, but was only able to get it to highlight the first for occurrences and I completely failed at trying to get it to copy the row to another worksheet.
Can someone help...Please!!!
Thanks

GBP 10. How can I append the total number of instances of a string in each row to the end of each string instance using VBA code?
I use the newspaper selection box from The Racing Post to make horse racing selections. I use an excel worksheet of which a sample is attached.

Each line represents a race and columns G to W shows each newspapers tipster’s selection. Some of the cells in each row have a number after the selection which represents the total number of tips for that horse.
I would like some VBA code that can loop through the cells in each line and append the total number to the cells that contain the same selection but not the total number. To make things a bit more difficult some of the cells have ‘(b)’ or ‘(nb) ‘, indicating best or next best ,after the selection’s name and I would like the total number to be inserted after the selections name but before the (b) or (nb).

Hi all of you,

I wish to know how to write a small program how to find a number in a range of cells? e.g

If I ask a respondent in a survey about the brands of cigarettes that he smokes, he will list me a number of different brands which are all pre-coded.

In excel worksheet I type my data as follows:
Column A COLUMN B
Row 1...Brand 1: 1
Row 2...Brand 2: 5
Row 3...Brand 3: 14
Row 4...Brand 4: 6
Row 5...Brand 5: 10

Now, I want to tell the computer that if he found code 1 or anu other code I want in the range from ROW 1 to ROW 5, a one will apper in another row.

Note: I use VBA connected to a worksheet i.e use target.row, target.column etc..

Thks
Sajid

Ok I've got the following code that folks in this forum helped me with - it's searching down an enormous column of data in excel that looks something like this:

V38=4
V72=2
V39=2
V55=
V56=
V38=3
V57=
V74=4

Now I need to change this code so I can manually specify a few values so in pseudo code it does this:

If cell starts with V38 export to sheet('output') into column D - what I can't figure out is how to hold the value so that if another V38 occurs, it knows to put it into the second row down and a third one into the third row etc...

If someone can help me with this I can then manually modify the script for all the V numbers to the correct column!


	VB:
	
 CommandButton1_Click() 
     
    Dim xlInput As Worksheet 
    Dim xlOutput As Worksheet 
    Dim xlLongRowInput As Long 
    Dim xlLongRowOutput As Long 
    Dim xlLongRow As Long 
    Dim xlIntColumn As Integer 
     
     
    Set xlInput = ActiveWorkbook.Worksheets("Input") 
    Set xlOutput = ActiveWorkbook.Worksheets("Output") 
     
     
    xlLongRowInput = xlInput.Cells(Rows.Count, 1).End(xlUp).Row 
    xlLongRowOutput = xlOutput.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row 
     
    xlIntColumn = 2 
     
    For xlLongRow = 1 To xlLongRowInput Step 1 
         
        Select Case Left(xlInput.Cells(xlLongRow, 1), 8) 
        Case "***START" 
            xlIntColumn = 2 
            xlLongRowOutput = xlOutput.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row 
            xlOutput.Cells(xlLongRowOutput, 1).Value = xlInput.Cells(xlLongRow, 1).Value 
             'xlLongRowOutput = xlOutput.Cells(Rows.Count, 1).End(xlUp).Row
        Case Else 
            xlOutput.Cells(xlLongRowOutput, xlIntColumn).Value = xlInput.Cells(xlLongRow, 1).Value 
            xlLongRowOutput = xlOutput.Cells(Rows.Count, 1).End(xlUp).Row 
            xlIntColumn = xlIntColumn + 1 
             
        End Select 
         
    Next xlLongRow 
     
    Set xlInput = Nothing: Set xlOutput = Nothing 
     
End Sub 

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

Hmm I've tried the following, but this only imports the value once.


	VB:
	
 CommandButton2_Click() 
    Dim InputSheet As Worksheet 
    Set InputSheet = ActiveWorkbook.Worksheets("Input") 
    Dim OutputSheet As Worksheet 
    Set OutputSheet = ActiveWorkbook.Worksheets("Output") 
     
    Dim InputRowCount As Integer 
    InputRowCount = InputSheet.UsedRange.Rows.Count 
    MsgBox InputRowCount 
     
    Dim CurrentInputRow As Integer 
    Dim CurrentOutputRow As Integer 
    CurrentOutputRow = 1 
    Dim CurrentOutputColumn As Integer 
     
    For CurrentInputRow = 1 To InputRowCount Step 1 
         
        Select Case Left(InputSheet.Cells(CurrentInputRow, 1), 8) 
        Case "***START" 
            CurrentOutputColumn = 1 
            OutputSheet.Cells(CurrentOutputRow, 1).Value = InputSheet.Cells(CurrentInputRow, 1).Value 
            CurrentInputRow = CurrentInputRow + 1 
        Case Else 
            CurrentInputRow = CurrentInputRow + 1 
        End Select 
    Next CurrentInputRow 
     
    Dim CurrentColumn As Integer 
     
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
can anyone point out the stupidity?

HI,

I've a workbook in which I run an excel macro to filter out data for five regions W1, W2, W3, W4, W5, the macro creates five sheets (w1, w2, w3, w4, w5) and places them in the same workbook along with the original master sheet called "west".

The data in all the sheets is layed out in an identical fashion except that the the number of rows in each sheet will differ depending on the number of records for each region in the master sheet "west".

I was looking for some vba coding to automatically add sum totals in two columns (I & J) for all the five region worksheets.

The first record always begins from row 4, in all the sheets.

so as an example W1 sheet could have a sum formula in I20 = Sum (I4:I8)
and in COl J as Sum(J4:J18). Row 19 is a blank row, and the intention is to leave a blank row just before placing the sum total in all the sheets.

W2 will have the same starting range but might differ in how many rows to sum .

And so on for the 4 remaining region worksheets in the workbook.

And help will be appreciated. I've been working on putting this extract region macro together for 3 days now and this is the final remaining part.

Thanks,

K

I have set of data rows in excel, these have been coloured coded to group them. What I would like to do is create a legend at the top of the worksheet (e.g. 1 =red, 2=green .....n) and by clicking that cell display all rows containg that serial number i.e. 1, 2, ....n This hopefully (if possible) filtering out any row not serial numbered 1, thus displaying all rows clour coded red.

I'm working with several ranges of data on the one worksheet. Once I'm finished with range A, I move down (using ActiveCell.end(xlDown)) and start working with range B, when I'm finished with B I move to the next and so on. The next block of data will have the same number of columns (as the original) but a varying number of rows such that the dimensions of the range need to change to accommodate each new block of data. My problem is when I get to the last block of data in the activesheet the ActiveCell.end(xlDown) sends the cursor careering down the column until I hit the outer limit of the sheet and Excel throws up an error. How do I better manage moving from one block to the next and most importantly, when I'm on the last I need to find a reliable way of detecting when I have reached the outer limits of my data - especially the last block of data.
Hoping you can help.
Dave

i have a special condition as follow
Since public function allow user to use this function in the worksheet.
for example, i have a public function ABC which does 1+1 =2
then in the worksheet cells if you type "=ABC" then it will return 2.

so what i need is that function return the cell position where this public function sits.

for example,
if i type in cell "A1" that "=ABC" it returns me the "A1" Column number in A2 with result of 1 and Row number in B2 with result of 1.
also if i put this same function in Cell "C3" it will returns me the Column number of "C3" in "C4" with value of 3 and in "D3" with value of 3.

and if i put application.volitale in the public function ABC, it will allow the public function update itself while you change the reference value.

i have tried many things, firstly activecell.address will not work, because it will only make the function calculating the active cell's value which will change all the others to the same value.

so is there any other way that could obtain the cell postion of where i put the public function "=ABC"?

Thank you.

i have put an examlpe of what i want in the excel file, i have made 2 codes, 1st totaltimex1 and 2nd is totaltimex2
if you read through and have a go with the formulas in the worksheet you should be able to understand what i mean.
Thank you again if you could help me out of this.

when you open this file you will see there are 6 place have #value, if you double click it and press enter it will start working. For TotaltimeX1 you have to manually do all 3 #value to make it work. TotaltimeX2 will just simple update itself, but it based on the activecell on totaltimeX1, i want them based on the cells where this formula TotaltimeX sits.

I have made notes from several posts that are close and I am going to try come up with a solution by combining them.
In the meantime I thought I would post as well just in case I have no luck.
I have a spreadsheet that is about 40 columns wide. Each row contains a members data, address, numbers, etc, then children's names and birthdates.
Currently the relevant data on this main sheets is entered: Column M=Child 1 Name, Column N=Child 1 Bday, Column O=Child 2 Name, Column P=Child 2 Bday, etc up to Child 7 bday. Obviously some members have only one child and some have more so some columns of each row are blank.
I would like to copy all the child data to a seperate worksheet into one column.
That would contain, Last Name (Column A on Main Data tab), then Child # Name, Child # Bday.
It would be nice if this could be updated (overwrite, not append) everytime the spreadsheet was opened but that is not necessary.
I have done some vb code in excel before but mostly editing not creating, I have not done anything with macros.
I'm not really sure which this should/would be.
Thanks for any assistance.

example.xlsx

Hello,

I have a workbook with 10 worksheets representing chapters in a manual. Each article in each chapter is highlighted a certain color based on a number of criteria (grey - complete, aqua - sent to translation, yellow - needs to be re-written, and orange needs to be produced). Now I've already figured out how to use the VBA and formula "countcolor", so I've been able to get a total in each worksheet. Now, in the 11th worksheet, I would like to have a list that lists only those articles that are aqua, yellow, and orange, so that it's easier for me to see what's being worked on without having to go through all the worksheets, as there is over 400 articles. So I want to create some sort of "IF" formula, that pretty much says "IF a cell from worksheets 1-10 = a certain color, then paste into worksheet 11". Please help. BTW, I'm still a newbie when it comes to excel, so if you list a VBA to use, can you please explain what the formula should look like.

Thanks so much.

Hello everyone,

I need your help on a project which involves, among others, importing a .csv file which has empty rows and columns.
Here's a code I found on the net and worked on:


	VB:
	
 import_from_csv() 
     'Imports text file into Excel workbook using ADO.
     'If the number of records exceeds 65536 then it splits it over more than one sheet.
    Dim strFilePath As String, strFilename As String, vFullPath As Variant 
    Dim lngCounter As Long 
    Dim oConn As Object, oRS As Object, oFSObj As Object 
    Dim msg As String 
    Dim style As Integer 
     
    msg = "Doriti sa importati datele din fisierul .csv?" 
    style = vbQuestion + vbYesNo 
    If MsgBox(msg, style, "Import date") = vbNo Then Exit Sub 
     'Get a text file name
    vFullPath = Application.GetOpenFilename(filefilter:="CSV Files (*.csv),*.csv," & _ 
    "Text files (*.txt),*.txt," & _ 
    "All files (*.*), *.*") 
     
    If vFullPath = False Then Exit Sub 'User pressed Cancel on the open file dialog
     'Application.ScreenUpdating = False
     
     'This gives us a full path name
     'We need to split this into path and file name
    Set oFSObj = CreateObject("SCRIPTING.FILESYSTEMOBJECT") 
    strFilePath = oFSObj.GetFile(vFullPath).ParentFolder.Path 
    strFilename = oFSObj.GetFile(vFullPath).Name 
     
     'Open an ADO connection to the folder specified
    Set oConn = CreateObject("ADODB.CONNECTION") 
    oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _ 
    "Data Source=" & strFilePath & ";" & _ 
    "Extended Properties=""text;HDR=Yes;FMT=Delimited;IMEX=1""" 
    Set oRS = CreateObject("ADODB.RECORDSET") 
     
     'Now actually open the text file and import into Excel
    On Error Goto Eroare 
    oRS.Open "SELECT * FROM [" & strFilename & "]", oConn, 3, 1, 1 
     
    While Not oRS.EOF 
        Sheets("chargereportadmin").Select 
        ActiveSheet.Range("$A$1").CopyFromRecordset oRS, 65536 
    Wend 
     
    oRS.Close 
    oConn.Close 
    MsgBox "Datele au fost importate din fisierul .csv", vbInformation, "Import finalizat" 
    Exit Sub 
Eroare: 
    MsgBox "Operatiunea de importare a datelor a esuat." & vbCr & _ 
    "Este posibil ca fisierul sa fie deschis de un alt utilizator." & vbCr & _ 
    "Va rugam sa inchideti fisierul si sa reincercati.", vbCritical, "Import esuat" 
    Exit Sub 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
What I need to do is import the data from the .csv file but I would like to skip the blank rows and columns.
Any help would be very much appreciated.

Thank you.

First time using VBA to chart data, using an XYScatter plot. Data set could have different numbers of rows and columns (series). Selecting all the data and plotting XYScatter by hand, Excel interprets the series, the x values (Column A) and the series titles, no problem. It is when I try to do that using VBA that things fall apart. This is what I have:


	VB:
	
 
Sub graph() 
    Dim rngData As Range 
    Dim numrows As Integer 
    Dim numcols As Integer 
     
    Worksheets("sheet").Activate 
    Range("A1").Select 
    Selection.CurrentRegion.Select 
    numrows = Selection.Rows.Count 
    numcols = Selection.Columns.Count 
    With Sheets("sheet") 
        Set rngData = .Range(.Cells(1, 2), .Cells(numrows, numcols)) 
    End With 
     
    ActiveSheet.Shapes.AddChart.Select 
    ActiveChart.SetSourceData Source:=rngData, PlotBy:=xlColumns 
     
    For k = 1 To numcols 
        ActiveChart.SeriesCollection(k).XValues = Worksheets("sheet").Range("A2:A" & numrows) 
    Next k 
     
    ActiveChart.ChartType = xlXYScatterLinesNoMarkers 
    ActiveChart.Location Where:=xlLocationAsNewSheet, Name:="sheetchart" 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
It is the "ActiveChart.SeriesCollection(k).XValues..." line which is where it falls apart. If I were to put in "ActiveChart.SeriesCollection(1)....
ActiveChart.SeriesCollection(2)....
ActiveChart.SeriesCollection(3)....etc, the x values are correctly entered. But when I try to do it dynamically, based upon the number of columns, it doesn't work. What could the issue be? Is there an easier way to do the whole thing?

Hi, I've been browsing through this forum and it has already helped me a great deal, but I'm kind of stuck on my most recent assignment. I'm working on an excel file containing 2 worksheets. Worksheet 1 a whole bunch of data which is neglectable for now. On worksheet 2 is the reference data.
On both sheets, there's a serial number in column A. Now, worksheet 2 contains the complete list of serial numbers and some numbers can be in that list x times. I've already made a count in column C.

Now, what I need to do, seems straightforward enough. When a serial number on worksheet 1 is matched on worksheet 2, a number of rows needs to be inserted below the active cell on worksheet 1. The number are the values in column c.

What makes it tricky is that it's not possible to match worksheet1!A1 to worksheet 2!A1 for instance. It should be completely dynamic.

Hope you guys can help me, or at least point me in the right direction many thanks in advance!


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