I have a letter and numbers in the A column. example: (A001_C108_1231Xb)
This kind of numbers go from Column A1 : A1800.
I need to print them out, therefore I would like to fill every page which has 50 rows and 3 columns : A, D and G to write in the data.
I tried to do it manually, which is quite some work.
Sheets.PrintOut. I have a button which uses:
View 4 Replies View RelatedI have a workbook on a web site. That workbook has the following
sFileName = sPath & ThisWorkbook.Worksheets("Retailer Lists"). Range("A" & c).Value & "/Fact+ Sheet.xls"
Workbooks.Open Filename:=sFileName
ActiveWorkbook.PrintOut
ActiveWorkbook.Close
When I run the code, the active workbook is the "sFileName" file. There are a couple of problems that I can't seem to get around. First of all, when I run the code, I get a Method 'Open' of object 'Workbooks' failed error. If I debug, it is on the Workbooks.Open statement shown above. If I do nothing but hit F8, the file opens. The second issue is nothing prints. The file is available to me and I can access things on the sheet, but it will not print. By the way, the CLOSE command works perfectly. Is there a different way that I should be accomplishing the printing?
I wanted to make a macro that saved my worksheet, then printed all the different tabs. The save is, of course, easy. Printing all tabs is being more difficult.
I tried to use the macro recorder, but all it did was select range, and then name each tab.
My tab names change though, so I need something that will just select all tabs.
Sub print_recap()
PrintOut Copies:=1
End Sub
this returns a "sub or function not defined" on the printout line
Sub print_All_recaps()
Set ws = Worksheets("Employees")
If UCase(InputBox("Enter y to print ALL the records")) <> "Y" Then Exit Sub
For n = 2 To ws. Range("A65536").End(xlUp).Row
Cells(2, 2) = ws.Cells(n, 1)
PrintOut Copies:=1
Next
End Sub
If I remove the PrintOut line I do not get an error.
I suffered a very annoying HDD failure and lost loads of music. The only record I have is the winamp media library which I could only export to .xml. From there, I've been unable to do anything remotely useful with it (access would have been best, I suppose) so have resorted to trusty Excel. Even then I've underestimated the task at hand: I've now got 9 excel files, each 65536 rows long, with the xml data streaming down them. Can anyone think how I could get a macro to
1. run through the list(s), line by line, deleting any line that had (e.g.)
Track ID OR Genre OR etc...
In order to remove all but the 5 categories I have in bold below, i.e. name, artist, album, date, folder....
I have a vba to do page set-up:
*Left, Top, Right, Bootom as 0.5
*Header and Footer as 0.2
Once vba is run page set-up is ok ie margins but when printed Left and right margins are not 0.5cm.
solve this mystery?
Code:
Sub Print_Format()
Dim myRange As String
myRange = Selection.Address
ActiveSheet.PageSetup.PrintArea = myRange
With ActiveSheet.PageSetup
[Code]...
I have a very long column (almost 3000 cells) of URLs. I would like to replace them in the second column with shortened versions, basically cut them at the fourth slash.
how can I used functions in Excel to do it?
I'm using this function to make a short description of a product from a long description.
=MID(K2,1,200)&"…"
So it's taking the first 200 characters of cell K2 and adding a ... to it.
Whats happening is it is splitting words and ending at character 200 instead of the end of the closest word to character 200.
Is there a way to alter this formula to break at the end of a word close to character 200?
Or is there a better way to do this?
How would I make the following code shorter/ more efficient? The columns don't vary much - either B, G or L. The rows vary a bit more....
View 9 Replies View RelatedNeed Macro to automatically printout my worksheets daily at a specifi time?
View 9 Replies View RelatedI have two sheets,one called pattern of about 25000 rows and one called rp1,
which is a fresh sheet downloaded each night from the web. What I want to do is select the names in column 3 of rp1 and printout in the same or a new sheet every row containing this name from the pattern spreadsheet.So for every horse named in col c,I want its complete record,could be 1 row or maybe up to 20 rows output,from the pattern worksheet(for each). They will all be underneath each other. I simply want excel to extract all the data belong to these names in rp 1 from the pattern large sheet. The names are in col r of the pattern sheet.
I have attached a screen shot below of a sheet I am using to collate fruit orders and calculate an order total for each individual person.
Untitled.png
The price of each fruit item runs along row 3 and then each persons order has its own row. The total price for each person is then calculated in column AM. However I am looking to shorten the formula I am using to calculate the total in this column.
I am currently using:
AM4=SUM(C3*C4+D3*D4+E3*E5+F3*F5+........+AJ3*AJ5+AK3*AK5+AL3*AL5)
AM5=SUM(C3*C5+D3*D5+E3*E5+F3*F5+........+AJ3*AJ5+AK3*AK5+AL3*AL5)
As you can see this formula is far too long so I am looking to write it in compressed formula.
shortening the below recorded macro?
Code:
Sub Macro1()
'
' Macro1 Macro
[Code].....
weekly i have a report that is generated that is over 5000 lines and 4 columns wide.
I currently copy and paste one page column to make it 16 columns across the page. Is there a function in excel to do this?
How would I shorten this code using a loop and arrays? It is working correctly but needs to be shortened down.
Sub cr()
Dim j
Cells(1, 1).Interior.Color = RGB(250, 155, 100)
j = 0.09
Cells(1, 1).Value = j
Application.Wait Now + TimeValue("00:00:02")
j = 0.094
Cells(1, 1).Value = j
Application.Wait Now + TimeValue("00:00:02")
j = 0.043
Cells(1, 1).Value = j
Application.Wait Now + TimeValue("00:00:02")
j = 0.05
Cells(1, 1).Value = j
Application.Wait Now + TimeValue("00:00:02")
j = 0.034
Cells(1, 1).Value = j
Application.Wait Now + TimeValue("00:00:02")
j = 0.032.........................................
I need a macro that evaluates the range H66:GI66.
In that range the following variables could exist:
A3, A4, A5, B3, B4, B5, C3, C4, C5, D3, D4...all the way to I5 for a total of 27 different combinations.........
I have put together some code which works fine but is very clumsy and, when it comes to expanding it in the very near future, will look really bad.
rn = Worksheets("Test"). Range("C1").End(xlDown).Offset(1, 0).Row
Worksheets("Test").Range("C" & rn) = Odate 'a variable already created
Worksheets("Test").Range("D" & rn) = Range("E8")
Worksheets("Test").Range("I" & rn) = Range("E4")
Worksheets("Test").Range("K" & rn) = Range("D6")
Is there a s way to simplify this code (and the several more lines of similar) using For..Next and/or With function(s)? I've tried a few combinations but can't quite strike the right one.
I have created 2 seperate VBA's that I would like to combine and improve upon. One removes Duffs, and the other eliminates blank rows. My goal is to do a copy and paste/special, then remove the duffs, and then delete all rows that do not have data in column H.
I have tried several VBA's listed on this sight, but cannot seem to get a VBA that does everything I need it to do, and do it quickly. I have attached an example of my spreadsheet.
I have a combo box that is used very simply to select a column from sheet and copy that whole column down to where text ends into a specific column in another sheet. I.e whatever column is selected ends up in column B of this other sheet. At the moment I've been using If functions for each case but there must be a more efficient way of doing this; here is just a sample of my
If textseries1.Text = "Fund01" Then
With Sheets("data")
Range("C1:C" & Cells(Rows.Count, 1).End(xlUp).Row).Copy
End With
Sheets("calculations").Range("B1").PasteSpecial
End If
If textseries1.Text = "Fund02" Then
With Sheets("data")
Range("D1:D" & Cells(Rows.Count, 1).End(xlUp).Row).Copy
End With
Sheets("calculations").Range("B1").PasteSpecial
End If
If textseries1.Text = "Fund03" Then
With Sheets("data")
Range("E1:E" & Cells(Rows.Count, 1).End(xlUp).Row).Copy
End With
Sheets("calculations").Range("B1").PasteSpecial
End If
There are about 75 columns in total but thought it would be a little repetitive to put it all up here!
I think I have a very straight forward problem, I'm copying about 400 values from one workbook to another (from vertical range to horizontal range) and I currently have about 400 lines of code in order to do this. Below I've pasted the code I'm using now but the macro takes an estimated 30 seconds to run. I figure if I can reduce the number of lines the macro will run a lot faster
RowCount = openWb.Sheets("Library Raw Shear Rates").Range("A3").CurrentRegion.Rows.Count
With openWb.Sheets("Library Raw Shear Rates").Range("A3")
[Code]......
I am using this formula to calculate a set of PH(Public Holidays) that have passed from a period of 1/4/2009 to 31/3/2010.
=IF(TODAY()>='Team Schedule'!D5,1.5,0)+IF(TODAY()>='Team Schedule'!D6,1.5,0)+IF(TODAY()>='Team Schedule'!D7,1.5,0)+IF(TODAY()>='Team Schedule'!D8,1.5,0)+IF(TODAY()>='Team Schedule'!D9,1.5,0)+IF(TODAY()>='Team Schedule'!D10,1.5,0)+IF(TODAY()>='Team Schedule'!D11,1.5,0)+IF(TODAY()>='Team Schedule'!D12,1.5,0)+IF(TODAY()>='Team Schedule'!H2,1.5,0)+IF(TODAY()>='Team Schedule'!H3,1.5,0)+IF(TODAY()>='Team Schedule'!H4,1.5,0)-4.5
From what u can see here, its a massive formula.... but I am pretty sure that there is a shorter way to key in the formula
*Note*
'Team Schedule'!D5-D12 and 'Team Schedule'!H2-H4 are PH. disregard the 4.5 that is in the formula as it is used for other purposes. Every PH that has passed will credit a 1.5 into the cell.
I have a file with over 20,000 rows that contain a date (Mon~Sun), What I need to do is remove every day that contains Mon~Sat and only keep Sun, this is the code that I have come up with and is working.
Code:
Dim c As Range
Dim SrchRng
Set SrchRng = ActiveSheet.Range("A1", ActiveSheet.Range("A" & Lastrow).End(xlUp))3
[Code]....
I have an issue with a Pivot table: I am using the 'show report filter pages' function and prior to this I have ensured my character length of that field is equal or less than 31 however when I hit the function the tab names are shortened to 26.
View 1 Replies View RelatedI am in the process of re-building a major project due to bugs. In the process, I plan to re-organize my sub-routine grouping within modules. In the source file, I have 18 standard modules, with 10-30 routines in each one.
What I would like is a printout of each routine name and which module it resides in, so I can check off each one as I copy it and paste it into the new file. I see that I can get a psuedo tree-view of my project in th eObject Browser, but can't figure out how to print that.
I have a column line chart to which I add data monthly and then have to manually update the "source data" to reflect the added data on chart. This is a rolling graph, which mean that I have to remove data for one month(from last year) and then include the new month's data. Is there any way on automating this process...like a macro or something, so once I add the data excel automatically removes one month of old data and make changes to include fresh data. Eg Currently chart is based on data from A2:F2 and I add new data to cell G2. I need something which automatically update the source data to cell B2:G2.
View 4 Replies View RelatedI have one source spreadsheet, where are columns NAME, DATE. I read these data by ADO to other spreadsheet, where I can change/delete data and then run macro for update data in source spreadsheet. The problem: In source spreadsheet is column "NAME" and column "DATE", with values e.g. "Joseph"; 1.1.1980. I read this data to other spreadsheet, then I delete in it value 1.1.1980. When I run macro Update, it messages error.
Sub UpdateItem
...
.Fields.Item(1).value = activecell 'activecell value = "Joseph"
If Not isempty(activecell.offset(0,1)) Then
.Fields.Item(2).value = activecell.offset(0,1).value
Else
.Fields.Item(2).value = "" 'I tried Empty and 0 too but when I read data again then, it displays 0.1.1900, nothing works
End If
...
End Sub
It seems that in source spreadsheet has data in column "Date" format Date and when I try to update data in format String ("") in source spreadsheet by Update macro, it messages error. When I used
.Fields.Item(2).Value = Empty
' or
.Fields.Item(2).Value = 0
after rereading data it displays 0.1.1900 What I want to get is that if the cell with date (in other spreadsheet) is empty, the cell in column Date in source spreadsheet after updating will be blank (contains no values).
I am trying to create a user form that will allow the user to type int values in boxes. Then when the user clicks the submit button the code needs to add the values from each user input box to the existing values in specific cells accross multiple worksheets. Then the form needs to be cleared after the cell values are updated. I can create the form it is the code on the submit button i am lost on. Also it is important that some boxes may be left blank.
If you click the Grey "Qty Form" button on sheet1 the form will open. User data numbers can be entered in the blank boxes. When the submit button is pressed the form needs to add the user entered numbers to the numbers in the corresponding cells in sheet 1 and sheet 2. How to code the submit button to do this properly. Also after the data on the spreadsheet is updated the form needs to be cleared and start the cursor back in the Item 1 box on the form.
- I have total of 13 sheets in a workbook - 12 sheets represent 12 months with data; 13th sheet is single sheet in which i would like to get complete overview of 12 months
- each of 12 sheets has actually sales results for multiple products with following data: internal code, manufacturer part number, name, and qty sold in that month
- situation is that some products have been phased out during the year and some were introduced so each sheet is slightly different in terms of in which row certain product is located
What i would like to achive is to make 13th sheet (whole year overview) do the following:
- there is a list of all the products in it, each product has unique internal code - this code (from each line) should be used to find that code in each monthly sheet, then find its monthly sales value (copy it) and paste it in sheet 13 in cell that represents this product and particular month.
In other words i would like to see for each product what was monthly sales throughout this year, but avoid manually filling in qty for each product per month.
I have attached a sheet that has part of a data list, sheet called (Full Data) what i am trying to do is seperate the data into event locations into individual sheets.
The data ref will be column F which is the different event locations.
I thought the best way to try and do this was to create an if/ match formula using the sheet tab names as the if match, with the event locations in column F.
I have added two sheet tabs so you can see that i require the data for (Ain) to be put into the Ain sheet.
The data list in Full Data sheet will be continuously updated so i will need the range to be around 10,000 entries.
