Paste Special With Dates And Links

I Am Trying To Paste Special With A Date, When The Date Is Blank In The Other Sheet It Comes Up As Default 00/01/1900 Is There A Way Where If It Is Blank I Can Show Blank And Wheen There Is A Date I Can Show A Date.

Thanks In Advance


Hi All:

I'm new to macro programming, so please excuse me if this issue is an easy one to resolve. I have a spreadsheet with a couple hundred ranges describing web postings. The original file was a text file, which I saved into Excel. Each range looks like this:

AUTHOR: Michael Smith
TITLE: ďBlah Blah Blah"
STATUS: Draft
ALLOW COMMENTS: 0
CONVERT BREAKS: wysiwyg
ALLOW PINGS: 0
BASENAME: more-blah
CATEGORY: Partners & Affiliates

DATE: 11/11/2010 10:33:17 PM
-----
BODY:
<p>Christmas is upon us...<a href="http://www.failblog.org/</a>.</p>

I need to write a macro that will identify and paste the title, date, and link into columns on a different worksheet. Preferably, it would only be two columns, with the link hyperlinked in the title.

So far I've tried to record me copy and pasting stuff from the cells, but that proved to be a failure, as did any code I tried to write. Any help would be greatly appreciated.

Best,

Josh

Hi could someone please tell me how to paste special with correct widths as well as formats at the same time if possible.
I can copy cells from one sheet to another and get the format ok but how do I get the correct width.
Thanks

Dim rSource As Excel.Range
    Dim rDestination As Excel.Range
    
   
    
    Worksheets("Main").Activate
    Set rSource = ActiveSheet.Range("b:b")

    Selection.AutoFilter Field:=3, Criteria1:="Bakery"
    rSource.Copy
    Worksheets("Main2").Activate
     Set rDestination = ActiveSheet.Range("a:a")
    rDestination.Select

    Selection.PasteSpecial Paste:=xlPasteFormats, _
    operation:=xlNone, _
    skipblanks:=False, _
    Transpose:=False


I have a large database with date and timestamp. Trying to find the best formula that would count each line within a time zone. Example below.

8/1/11 8:48 AM 8/1/11 8:48 AM 8/1/11 8:03 PM 8/1/11 8:03 PM 8/2/11 2:22 PM 8/2/11 2:22 PM 8/2/11 7:48 PM 8/2/11 7:48 PM 8/3/11 8:14 AM 8/3/11 8:14 AM 8/3/11 8:57 PM 8/3/11 8:57 PM This may be a two step formula but need to know count of those between 8-9 AM and 8-9PM, 2-3AM and 2-3PM, etc.

Update asset list with date and room

Hiya

I am very new to Excel macros, and i have an asset list in excel (see attached example, sheet 1).

Asset numbers are incremental and unique.

Every year we get a list of asset numbers that have been found (see sheet 2) that are located in a particular room on a particular date.

I need to update the main asset list with the date (Date checked) and Room for each asset found on the list. I could also do with a prompt each time an asset is to be updated saying "Update or Skip".

Can anyone help me please...i really dont know where to start...I've tried recording macros but this doesn't seem to work, and i think i need to set a room and date constant..

Please help!! Kim

Is it possible that each time I save a file it saves the filename along with date and time as the version of the file in a specific path?

need to indicate last saved by with date and time in a cell.

Thanks n regards
Bhavesh.

Is there any way to do a paste special with values and format both? Meaning is there a way to combine the two statements below into one?

Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True

Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
'False, Transpose:=True

Thanks,
Jay

Hiya,

I'm pretty fluent in most of the regularly used function in excel - but most of my experience has come with working with numbers and text... I was just wondering if anyone could give me a quick sort of 'guide' to working with dates and times?

I know that each date and time have a numerical value, which has helped with a lot - like adding a day or week or something. But when it comes to trying to add a month, or a year or something like that I get stuck.

Also, I am familiar with the days360 function, but is there something similar with times?

For one of my workbooks, I am inserting a bussiness' expenses for each client (each client has a separate sheet). Each expense has many details, but in particular a date and an amount in pounds. On another separate sheet there is a table for each client, and that table adds up the total expenses in each MONTH. The sheet only runs for one financial year, so there are no duplicate months. Just wondered how i can do this? No doubt it simple. I tried having a hidden column for each expense - where the date is replicated (e.g. '=A3'), and then change the cell format to 'custom' and 'mmm'. this would display Jan for all January expenses, and so on. Then I tried to sue a sumif formula in to add up all the charges where 'Jan' is written. but this doesn't work. I'm assuming the sumif formula works on the cell's numerical value?

Anyway, any help would be much appreciated - once I've gotten my head around ates and times, it should make lots of things more simple.

Thanks

Hi,

I have a problem with date and time
my excel sheet goes as follows with date and time in two different columns time with milliseconds
I want to combine both the filelds(columns) and get unix time stamp in another column.....

I am struggling with it since 2 days..
it would be helpfull if someone can find a way out.......

Date Time
28.11.2011 17:39:51.75
28.11.2011 17:39:51.95
28.11.2011 17:39:52.15
28.11.2011 17:39:52.35
28.11.2011 17:39:52.56
28.11.2011 17:39:52.74
28.11.2011 17:39:52.94
28.11.2011 17:39:53.17
28.11.2011 17:39:53.37
28.11.2011 17:39:53.57
28.11.2011 17:39:53.78
28.11.2011 17:39:53.98

Thanks in advance..

Is there any way when copying and pasting special with add to ignore adding the dates but just add the numbers underneath the date?

Hi!

I have a problem or rather Iím trying to find a solution to a problem in Excel. Iíll try to be as clear as I can so I apologize in advance for the length of my post. I have a spreadsheet in Excel that consists of a number of tabs, one for each month of the year. After sorting the data for each month looks like this

Account number Birthdate Name Data 1 Data 2
X0000 670822 Jane 1 3
X0000 740908 Ben 5 5
X0001 370908 Jerry 0 7
X0001 400908 John 3 5
X0001 450908 Paul 4 4
X0002 550908 Peter 2 0
X0002 650908 Linda 1 7
X0002 709008 Sally 0 6
X0002 750908 Maria 3 0

I used the function SUBTOTAL in Excel 2003 (Delsumma in Swedish) with column Account Number acting as the filter so what I got looks like this:

Account number Birthdate Name Data 1 Data 2
X0000 670822 Jane 1 3
X0000 740908 Ben 5 5
X0000 Totalt 6 8
X0001 370908 Jerry 0 7
X0001 400908 John 3 5
X0001 450908 Paul 4 4
X0001 Totalt 7 16
X0002 550908 Peter 2 0
X0002 650908 Linda 1 7
X0002 709008 Sally 0 6
X0002 750908 Maria 3 0
X0002 Totalt 6 13
Totalt 19 37

Of course if I just want to look at the partial sums I can do that and I think it works very well. I was very pleased with the function.

The problem I have manifest itself when I wanted to copy the totals for each month in a separate tab, i.e.

Month X0000 X0001 X0002
Jan
Feb
Mar
Apr

I thought that if I copied the values for Data 1 and Data 2 when Iím only looking at the partial sums then I could use PASTE SPECIAL (values and transpose) in much the same way one can with a filtered table, but I discovered I get all the values in between (in this example instead of 6 for X0000 I get 1 and 5 as well. Iíve ended up copying the values from the filtered sheet and writing them down in my year-end table, which feels kind of a waste of a good function. I feel Iím almost better of doing the partial sums myself because then I believe at least I could use PASTE SPECIAL in conjunction with a filter to take only subtotals.

The second part of my problem is that I would like to count how many people belong to each account number. I know I can use the same function (SUBTOTAL) for example in column birthdate to count how many people there are. But apparently you canít use SUM and COUNT at the same time. What I did was to add a column after Data 2 and use COUNT.IF with column birthdate (for example COUNT.IF (B2:B3; ď>0Ē) for account number X0000). I thought I could now use PASTE SPECIAL, with values, transpose and skip blanks checked in, since I also have to take all the totals in a year-long table, but it didnít work with this column either, and I get all the empty cells in between.

So I guess my general question is: how can I do this compiling in a more efficient way?

Any tips will be greatly appreciated!

PS THe columns got a bit messed up when I pasted from word but I hope you get the picture still.
[/img]

It would be handy in Microsoft Excel if there was a selection under Paste
Special that allowed you to paste the value of a link without pasting the
value for other formulas.

For example: I have a spreadsheet that pulls data from another database
file and performs numerous calculations on it. I have to e-mail this file to
others (who do not have the database so I have to remove the links).
Currently, I have to paste the values for everything, destroying the "base"
formulas. If I change any of the other values those formulas run on, it will
not change in this section.

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...et.f unctions

I have data exported from a defect management tool (that reports all the defects that have been recorded in the defect management system). This data has unique columns for name of originator, defect ID, date originated, priority, etc.

The date originated column has the date and time. I want to create a report that tells me how many High, Medium and Low priority defects were created on each date - and I am using a pivot table to do this.

However, because the date column is actually the date and time the defect was reported/recorded, the time element of this is making all the dates unique eg, I have three defects recorded on 29/01/2009, a High priority defect (reported at 29/01/2009 12:23) and two Medium priority defects (reported at 29/01/2009 13:02 and 29/01/2009 13:32). My pivot table is showing each date as a unique date - with a count of 1 against each, whereas I want to see 1 date entry (for 29/01/2009) with a count of 1 against High and 2 against Medium.

I have tried custom formatting the date column to dd/mm/yyyy - which appears to work in the data (but doesn't in the pivot table, because the time stamp is still there, just not visible).

I have tried copy and paste special [Values] (on the custom formatted date column) - but the time stamp is still there.

I have tried formatting the date column to "General", but this changes the dates to the number format - eg 29/01/2009 12:23 becomes 39842.5166203704.

I have tried inserting a new column and using the =Left function for the first 10 characters of the cell (ie the 29/01/2009), but that gives the first 10 characters of the number form of the date, ie 39842.5166

Basically, without going into every date cell (and I have thousands) and manually deleting all the times, how do I quickly change the date column into just having the date in it (ie how do I strip off the time element)?

Many thanks.

Help! I don't know what the best way to do this, I've been surfing
these postings on various sites for the past few days as well as bought
a book. And I still not sure what I should do.

I have 6 individual workbooks for each rep that I want to consolidate
into one master workbook, either with one worksheet or 6 worksheet.
This is for sales progress, so not necessarily looking for a sum total.
I want to have the ease of looking at one master workbook instead of
opening each of the 6 workbooks. Each workbook has same column header
but may have different number of rows depending on their sales
progress. Some may have 10 rows with data, some may have 50 rows.

I figured out how to do the Paste Special. With this, I think the best
would be with one master workbook and 6 worksheet, since I don't know
how many rows each individual workbook will contain. The only thing
is, there is alot of 0's because it already has the links. Anyway
around this? Cool thing is that info is updated even when the master
is closed.

Is this the best way or is there another way to do this? I just want
to compile a master with all information for each rep, and each
workbook has the simple summation formula. Is data consolidation the
best for this? Also, I am not that advanced so I won't be able to do
any of the programming/VB yet!

Thank you in advance!

sumi

Thanks!

It would be handy in Microsoft Excel if there was a selection under Paste
Special that allowed you to paste the value of a link without pasting the
value for other formulas.

For example: I have a spreadsheet that pulls data from another database
file and performs numerous calculations on it. I have to e-mail this file to
others (who do not have the database so I have to remove the links).
Currently, I have to paste the values for everything, destroying the "base"
formulas. If I change any of the other values those formulas run on, it will
not change in this section.

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...heet.functions

Alistar helped me with the following code, and I want to thank him/her with that, but I need to the code to start the copy 6 rows later and do a paste special values only (because my formulas are going over instead and I don't want that), seeing how I am new w/VBA I was hoping either Alistar or someone else could help me w/this edit. Thanks.


	VB:
	
 Replacelist() 
     
    Dim EndData As Integer 
    Dim endResults As Integer 
    Dim ws As Worksheet 
    Dim i As Integer 
    Set ws = Sheets("detailed replacement") 
    EndData = ws.Cells(65536, 1).End(xlUp).Row 
     
     
    For i = 3 To EndData 
        If Cells(i, 5).Value  0 Then 
            endResults = Sheets("replaced").Cells(65536, 7).End(xlUp).Row + 1 
            With ws 
                .Range(.Cells(i, 1), .Cells(i, 5)).Copy Destination:=Sheets("replaced").Cells(endResults, 1) 
            End With 
        End If 
    Next 
     
    Application.CutCopyMode = False 
     
End Sub 

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


Apologies if this question is too long!

Following on from a previous post (OzGrid Excel Forum Ľ Excel/VBA Ľ Copying files with ODBC query links), I have a file with 10 sheets, all of which have links to an Access database via ODBC. I want to be able to save the file without the links, so I can save a historical 'snapshot'. If I just Save As, the links remain and the data is updated. I have created a macro that highlights all sheets, then selects all sheets, copies the data, creates a new file, adds the additional sheets and renames them and then pastes the data into the new file which can then be saved. This works, only the database links remain.

I want to be able to Paste Special with the column widths, then format and then paste values. When I change the Paste line to include all this, the macro fails. Can anyone help please?

Sub savefile()
'
' savefile Macro

Sheets("Constants").Select
Cells.Select
Sheets("Summary").Select
Cells.Select
Sheets("Gary Bixley").Select
Cells.Select
Sheets("David Cartwright").Select
Cells.Select
Sheets("Malcolm Voss").Select
Cells.Select
Sheets("David Lenton").Select
Cells.Select
Sheets("Linda Dick").Select
Cells.Select
Sheets("Robert Greenhalgh").Select
Cells.Select
Sheets("Others").Select
Cells.Select
Sheets("Behind Schedule").Select
Cells.Select
Sheets("Zone List").Select
Cells.Select
Sheets("Zone Summary").Select
Cells.Select
Sheets(Array("Constants", "Summary", "Gary Bixley", "David Cartwright", _
"Malcolm Voss", "David Lenton", "Linda Dick", "Robert Greenhalgh", "Others", _
"Behind Schedule", "Zone List", "Zone Summary")).Select
Selection.Copy
Workbooks.Add
Sheets.Add
Sheets.Add
Sheets.Add
Sheets.Add
Sheets.Add
Sheets.Add
Sheets.Add
Sheets.Add
Sheets.Add

Sheets("Sheet12").Select
Sheets("Sheet12").Name = "Constants"
ActiveWindow.DisplayGridlines = False
Sheets("Sheet11").Select
Sheets("Sheet11").Name = "Summary"
ActiveWindow.DisplayGridlines = False
Sheets("Sheet10").Select
Sheets("Sheet10").Name = "Gary Bixley"
ActiveWindow.DisplayGridlines = False
Sheets("Sheet9").Select
Sheets("Sheet9").Name = "David Cartwright"
ActiveWindow.DisplayGridlines = False
Sheets("Sheet8").Select
Sheets("Sheet8").Name = "Malcolm Voss"
ActiveWindow.DisplayGridlines = False
Sheets("Sheet7").Select
Sheets("Sheet7").Name = "David Lenton"
ActiveWindow.DisplayGridlines = False
Sheets("Sheet6").Select
Sheets("Sheet6").Name = "Linda Dick"
ActiveWindow.DisplayGridlines = False
Sheets("Sheet5").Select
Sheets("Sheet5").Name = "Robert Greenhalgh"
ActiveWindow.DisplayGridlines = False
Sheets("Sheet4").Select
Sheets("Sheet4").Name = "Others"
ActiveWindow.DisplayGridlines = False
Sheets("Sheet1").Select
Sheets("Sheet1").Name = "Behind Schedule"
ActiveWindow.DisplayGridlines = False
Sheets("Sheet2").Select
Sheets("Sheet2").Name = "Zone List"
ActiveWindow.DisplayGridlines = False
Sheets("Sheet3").Select
Sheets("Sheet3").Name = "Zone Summary"
ActiveWindow.DisplayGridlines = False

Sheets("Constants").Select
Selection.PasteSpecial Paste:=xlColumnWidths, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

End Sub

i'm vb dumb (resides recording and butchering), but i want a range (say a1:f10) saved as a new file with just values/format/columnwidths pasted in it

and save the file name as "SETUP + "a1" + date"

and prompt where to save?

possible???????

I am linking cells in an Excel Sheet to a Microsoft Word Document.

When I do a Paste Special to link an Excel cell that contains MULTIPLE paragraphs, I get " " around the text.

How do I get rid of the quotation marks around the linked text in my Word Document?

Hi,
I'm having an excel sheet named 'Box'. In Cell "B7" I have an info with
'Box1'
In the area "A10:H100" I have a lot of values calculated by formulas.

What I want is a macro that create a new sheet using the name from "B7"
then copy A10:H100 from sheet 'box' and then paste special with only
the values in the new sheet 'box1".

After changing all the values in A10:H100 and "B7" to 'box2' ( in 'box'
)I will be able to have the macro do the same procedure again but this
time paste special to 'Box2' and so on.

And yes: the original data that I want to copy and the "B7" are always
from the sheet named 'Box'

Is this possible makin in VB?

Thanks

Hi,

I am completely new to excel programming. I have a SQL DTS which
creates an excel file and exports data into it. The created excel file
could have variable number of work sheets.

Text data in the excel file had a leading single quote character and
spaces before the data. I can get rid of these by copying entire sheet
and paste special--values into a new sheet.

Would some one tell me how to automate to create a new excel file and
copy and paste special (with values option) all the work sheets into
the new file? I appreciate any help.

Hi,
I'm having an excel sheet named 'Box'. In Cell "B7" I have an info with
'Box1'
In the area "A10:H100" I have a lot of values calculated by formulas.

What I want is a macro that create a new sheet using the name from "B7"
then copy A10:H100 from sheet 'box' and then paste special with only
the values in the new sheet 'box1".

After changing all the values in A10:H100 and "B7" to 'box2' ( in 'box'
)I will be able to have the macro do the same procedure again but this
time paste special to 'Box2' and so on.

And yes: the original data that I want to copy and the "B7" are always
from the sheet named 'Box'

Is this possible makin in VB?

Thanks

I am trying to paste a group of cells from one excel book that gets information from two other books. For the past...forever...when I paste into the last book, I right click the cell and paste special and got several options to choose from, the paste with values is the one I am after.

Now, for a reason I am sure is my fault, when I paste special, I get two options;

Paste:
Paste link:

I don't want those options. I want the option list that gives me the option of pasting special with values. Anyone have an idea of what I could have done wrong to make the values option screen go away.

Thank you

Hi,

I wanted to know how can i convert 23MAR2012:23:35:34.000 using a formula, need to convert this into proper date and time format (hh:mm:ss) and also i have two colums with same date formats but different dates, i need to know the formula to differenciate them.

Thanks,
Sona