Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Paste column but change order Results

I have two worksheets... "TRANSACTIONS" and "MANUAL ADJUSTMENTS"
The two sheets have similar types of data but the columns are in different orders.

Here is what I've got so far:

	VB:
	
 MoveColumnsFromMANUALtoTRANSACTIONS() 
     '
     ' MoveColumnsFromMANUALtoTRANSACTIONS Macro
     ' get data on sheet "Manual Adjustments" and past to proper columns on "Transactions"
     '
     
     '
    Sheets("MANUAL ADJUSTMENTS").Select 
    Range("A1").Select 
    Dim myColumn As Range 
    Set myColumn = Range(Range("A2"), Range("a2").End(xlDown).Offset(0, 0)) 
    myColumn.Copy 
     
    Sheets("TRANSACTIONS").Select 
    Range("A1").Select 
    Selection.End(xlDown).Offset(1, 0).Range("A1").Select 
    ActiveSheet.Paste 
     ' that seemed to work
     ' but now I see why some go to the bottom and xlUp
     '  now for column B
    Sheets("MANUAL ADJUSTMENTS").Activate 
    Set myColumn = Range(Range("b2"), Range("b2").End(xlDown).Offset(0, 0)) 
    myColumn.Copy 
     
    Sheets("TRANSACTIONS").Select 
    Range("b2").Select 
    Selection.End(xlDown).Offset(1, 0).Range("A1").Select 
    ActiveSheet.Paste 
     ' Ok that worked for column B
     ' now I need to copy and paste columns: C-C, D-D, E-E, F-F, G-k, (don't use H&I)
     '  J-G, K-Q, L-R, N-S, O-T
     ' It would be nice to have a more elegant way than just rewrite same code 10 more times
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I am happy to search for solutions (I've learned tons just reading other posts) but I don't seem to know what to search for...

any help is very much appreciated!

I have a list of data that involves columns A, B, and C. I have sorted the values by column C so that the 8 or so repeating values are in order and have their corresponding column A and B values in the same row. I need a macro that will copy and paste columns ABC into new columns (Doesn't matter if on the same or new worksheet) for each unique value in column C. I have found a macro that inserts a blank after each unique value in column C, but rather than inserting a blank row, I need to create new columns for each unique value in column C. The macro that inserts a blank is a good start, but I'm a beginner and cannot get it to copy and paste the values into new columns for each unique C value. The macro for inserting blanks is as follows:


	VB:
	
 InsertBlanks() 
    lr = Cells(Rows.Count, "c").End(xlUp).Row 
    For I = lr To 2 Step -1 
        If Cells(I - 1, "c")  Cells(I, "c") Then Rows(I).Insert 
    Next I 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Can anyone help me get it so that this macro doesn't "Insert" as seen in the 4rth line of code, but instead would copy and paste into a new column each time it detects a change in column "C"? Thank you very much.

I need to re-arrange columns in a workbook

example columns: A,B,C,D

buts users want the columns in this order: B,C,D,A

I know I could insert columns and then cut and paste to rearrange the columns, but the users have a tendency of changing their minds

so I wanted a nice way where I could move columns without having to go through the trouble of modifying the insert/cut/paste code everytime they want a different order

from looking around on this forum their doesn't seem to be an easy way to do this?

Somehow, one column of numbers in a sheet refuses to be sorted correctly. It is a column of total sales by customer, and when I sort it in descending order, I get all the data with "9's" listed first, then all the 8's, etc. It is not sorted by amount. When I click on the cells, and try to reformat to number format, they will not. It appears that the number has a space in front of it, and when I click on a cell, and then click up in the formula bar, just to the left of the number, and press backspace, and then enter, the number changes to the format I specified. I can't do this to 1700 cells, so I tried the trim function by adding another column "P", (the original data is in O1:O1747) and tried sorting by that column, but I get the same result. Any suggestions?

Note: I did convert the original file by using the PROPER function because original was in all caps. I did not apply the proper command to the financial data, but pasted it in next to the corresponding rows after the conversion was complete.

I received a large spreadsheet of dates and salaries. I sort the columns into the order I need them then copy/paste special values into an Excel workbook that runs a program on the dates and salaries. (I paste special so that any formulas that made up the salaries turn into hard numbers). I then reformat the dates from a 5 digit number back into a mm/dd/yy in the spreadsheet.

To my immense surprise, ALL of the dates changed by exactly 4 years and 1 day. So a date that originally showed as 08/27/53 (even when pasted into a clean worksheet) turned into 18136, which turned into 08/26/49!

I have never run into this before. When I looked at the original spreadsheet, the date 08/27/53 shows as 08/27/1953, but when I look at the format it has a Date format, but instead of mm/dd/yy it shows as *mm/dd/yy. The person who sent me the data simply downloaded it from her software into Excel. Apparently a date format starting with a * changes with the operating system?

What do I have to do to get these dates into the new worksheet without changing?

I'm stumped. I have two worksheets where I count the occurrences of the work
order type by month from the first worksheet and get a running tally in a
table on the second worksheet. My results have been accurate until I arrived
at January. Column C is WS2 is accurate but columns D & E no longer tally
and count the blank cells which never happened in the past. Nothing has
changed except for the month value. Here is the set up:

Worksheet 1 is "Work Orders" Worksheet 2 is Totals FY 2009
Worksheet 1:
Column b is the Work Order Type
Phone Request
Telephone Trouble
Trouble Calls
Work Request
Column c is the date received
column d is the date started
column e is the date completed

Here is my formula:
=SUMPRODUCT(--('WORK ORDER'!B2:B701="Telephone Trouble"),--(--MONTH('WORK
ORDER'!D2701)=1))

Hi All,
I'm working on setting up a chart. The formula I'm using for cell B21 is this: =ROUND(F3,0) & " " & IF(E3<F3,"▲", IF(E3=F3,"●","▼")).

I'd like to able to copy this to cells C21 through H21 (along row 21), but when I copy it the formula becomes: =ROUND(G3,0) & " " & IF(F3<G3,"▲", IF(F3=G3,"●","▼"))

I'd actually like for the cells referenced in the above formula to be F4 and E4 and continuing in that order through H (i.e. the cells referenced in D21 would be F5 and E5, F6 and E6 in E21, etc.. I can't quite seem to figure this out though. Any ideas?

I would attach the worksheet, but I've been blocked from uploading files here at work. However, what I'm trying to accomplish is similar to what Chandoo has here.

Thanks in advance for your help.

Cheers
Tim

Ok, I have this strange problem. I am currently writing an excel workbook for someone calculating food items and donations during a weekly breakfast service at a church. And I have everything done except for a worksheet called "donors list"

the user would click on the update existing button, and it prompts the user to input the name of the person they want to update information on. User imputs the name, the system searches through the names in column A, and returns with the information of the person on the most recent date. The system then prompts the user to enter in the new amount donated, in order of updating only the elements in that cell for amount donated. Afterwards, it recalculates the total amount donated and the new information with all the cells are pasted on the bottom of the list with new date. All the information stays except for amount donated and total amount donated, aswell as the date.

Right now I have it so that it updates existing and copies the whole row of the person, but I need it to also use the new amount donated and do the total amount donation. So maybe a macro that copies up until the column for amount donated, and another input box to prompt user to continue adding new donation, and the system updates new donation to total amount donated.

--------------> update existing donor button <------------------

Name Address State Zip|||<--((stop copying and enter new data)) Amount Donated Total Amount Donated Date

I have attached the worksheet below, the problem is in the sheet called "donors list"

I have a download every month that is normally the same, but from time to time, they add new information, which means I would have to rewrite my code anytime a change is made, I will show you what I have done, it's the long way around because of my limited Excel knowledge lol

    If ActiveCell.Value = "TOP BP" And ActiveCell.Offset(0, 1).Value =
"NAME" And ActiveCell.Offset(0, 2).Value = "CONTRACT" And ActiveCell.Offset(0, 3).Value =
"CONFIGURATION" And ActiveCell.Offset(0, 4).Value = "INV-TO BP" And ActiveCell.Offset(0, 5).Value =
"NAME" And ActiveCell.Offset(0, 6).Value = "SHIP-TO BP" And ActiveCell.Offset(0, 7).Value =
"NAME" _
    And ActiveCell.Offset(0, 8).Value = "Name 2" And ActiveCell.Offset(0, 9).Value = "ADDRESS 1" And
ActiveCell.Offset(0, 10).Value = "ADDRESS 2" And ActiveCell.Offset(0, 11).Value = "CITY" And
ActiveCell.Offset(0, 12).Value = "CITY 2" And ActiveCell.Offset(0, 13).Value = "POSTAL CODE" And
ActiveCell.Offset(0, 14).Value = "PROV" And ActiveCell.Offset(0, 15).Value = "SERIAL NO" _
    And ActiveCell.Offset(0, 16).Value = "CONTRACT STATUS" And ActiveCell.Offset(0, 17).Value = "OBJECT
GROUP" And ActiveCell.Offset(0, 18).Value = "OBJECT DESC" And ActiveCell.Offset(0, 19).Value =
"INVOICE" And ActiveCell.Offset(0, 20).Value = "INVOICE DATE" And ActiveCell.Offset(0, 21).Value =
"COMMIT FROM" And ActiveCell.Offset(0, 22).Value = "COMMIT TO" And ActiveCell.Offset(0, 23).Value =
"PREV METER READ DATE" _
    And ActiveCell.Offset(0, 24).Value = "SOURCE" And ActiveCell.Offset(0, 25).Value = "PREV METER READ"
And ActiveCell.Offset(0, 26).Value = "CURR METER READ DATE" And ActiveCell.Offset(0, 27).Value = "SOURCE"
And ActiveCell.Offset(0, 28).Value = "CURR METER READ" And ActiveCell.Offset(0, 29).Value = "VOLUME" And
ActiveCell.Offset(0, 30).Value = "CPC" And ActiveCell.Offset(0, 31).Value = "BASE CHARGE" And
ActiveCell.Offset(0, 32).Value = "AMOUNT" _
    And ActiveCell.Offset(0, 33).Value = "SUMMARY SUPPLY$" And ActiveCell.Offset(0, 34).Value = "TOTAL
CHARGES" And ActiveCell.Offset(0, 35).Value = "GST" And ActiveCell.Offset(0, 36).Value = "PST" And
ActiveCell.Offset(0, 37).Value = "INVOICE TOTAL" And ActiveCell.Offset(0, 38).Value = "HST ADJ Y/N" And
ActiveCell.Offset(0, 39).Value = "HST PRORATED AMT" And ActiveCell.Offset(0, 40).Value = "CALC TAX CODE"
And ActiveCell.Offset(0, 41).Value = "HST ADJ" _
    And ActiveCell.Offset(0, 42).Value = "PST REV" And ActiveCell.Offset(0, 43).Value = "HST REV" And
ActiveCell.Offset(0, 44).Value = "GST REV" And ActiveCell.Offset(0, 45).Value = "TAX ADJ BALANCE" And
ActiveCell.Offset(0, 46).Value = "CUSTOMER G/L ACCOUNT" And ActiveCell.Offset(0, 47).Value = "CTD TOTAL
CHARGES" And ActiveCell.Offset(0, 48).Value = "YTD TOTAL CHARGES" And ActiveCell.Offset(0, 49).Value =
"CUSTOMER PO" And ActiveCell.Offset(0, 50).Value = "SOLD-TO BP" _
    And ActiveCell.Offset(0, 51).Value = "SOLD-TO BP NAME" And ActiveCell.Offset(0, 52).Value = "VENDOR
NUMBER" And ActiveCell.Offset(0, 53).Value = "PARENT" And ActiveCell.Offset(0, 54).Value = "PARENT
NAME" And ActiveCell.Offset(0, 55).Value = "PARENT NAME 2" And ActiveCell.Offset(0, 56).Value = "PARENT
ADDRESS" And ActiveCell.Offset(0, 57).Value = "PARENT ADDRESS 2" And ActiveCell.Offset(0, 58).Value =
"PARENT CITY" And ActiveCell.Offset(0, 59).Value = "PARENT CITY 2" _
    And ActiveCell.Offset(0, 60).Value = "INSTALL DATE" And ActiveCell.Offset(0, 61).Value = "CONTRACT
TYPE" And ActiveCell.Offset(0, 62).Value = "ORG. TERM" And ActiveCell.Offset(0, 63).Value = "TERM"
And ActiveCell.Offset(0, 64).Value = "START DATE" And ActiveCell.Offset(0, 65).Value = "END DATE" And
ActiveCell.Offset(0, 66).Value = "ORG. EXPIRY" And ActiveCell.Offset(0, 67).Value = "MACHINE STATUS" And
ActiveCell.Offset(0, 68).Value = "SUPPLY ITEM" And ActiveCell.Offset(0, 69).Value = "SUPPLY ITEM DESC" _
    And ActiveCell.Offset(0, 70).Value = "SUPPLY DETAIL QTY" And ActiveCell.Offset(0, 71).Value = "SUPPLY
DETAIL AMT" And ActiveCell.Offset(0, 72).Value = "CO DEPT CODE" And ActiveCell.Offset(0, 73).Value =
"CUST DEPT CODE" And ActiveCell.Offset(0, 74).Value = "COST CENTRE" And ActiveCell.Offset(0, 75).Value =
"CONFIG OPTION" And ActiveCell.Offset(0, 76).Value = "CUSTOMER AREA" And ActiveCell.Offset(0, 77).Value =
"ENTITY NAME" And ActiveCell.Offset(0, 78).Value = "BANNER NAME" _
    And ActiveCell.Offset(0, 79).Value = "CLASSIFIED CODE" And ActiveCell.Offset(0, 80).Value = "Asset
Tag" And ActiveCell.Offset(0, 81).Value = "Mac ID" And ActiveCell.Offset(0, 82).Value = "ADD-ON" And
ActiveCell.Offset(0, 83).Value = "ADD-ON DESC" And ActiveCell.Offset(0, 84).Value = "ADD-ON TEXT" Then
    MsgBox "Headers Ok"
    Else
    MsgBox "Headers have changed"
    Exit Sub
    End If
This checks all the Row 1 cells to make sure all the columns are in the same order. If not, it gives a message and ends the sub. Further along, all the columns are cut and pasted in a certain order.

   
Columns("T:T").Select
    Selection.Cut
    Columns("A:A").Select
    Selection.Insert Shift:=xlToRight
    Columns("G:G").Select
    Selection.Cut
    Columns("B:B").Select
    Selection.Insert Shift:=xlToRight
    Columns("K:K").Select
    Selection.Cut
    Columns("C:C").Select
    Selection.Insert Shift:=xlToRight
    Columns("M:M").Select
    Selection.Cut
    Columns("D:D").Select
    Selection.Insert Shift:=xlToRight
    Columns("O:O").Select
    Selection.Cut
    Columns("R:E").Select
    Selection.Insert Shift:=xlToRight
As you can see, I am cutting and pasting blindly and assuming that the Columns are in the right spot. This also moves around all the Columns within the same sheet as it goes, so if I go to cut a Column part way through, say S, that may have started in V when the data first came down. Also, if new Columns are added to the download, suddenly, what was in C (ex. HST) is now in F, so my cut/paste code is useless.

What I'm trying to figure out is if it's possible to store the sheet as an array, and then cut and paste the columns in the right order based on the cell data in each header in Row 1. So if Column AX has "GST" in Row 1, I want to find that name, store the Column range maybe, then insert that Column somewhere in the spreadsheet, or even into a new Sheet.

I hope that made sense, thanks!

First off, thanks so much. I've learned a lot by searching this forum. I have also read two different books on VB but I am still very new to it.

I have been making changes to various code from this forum in order to create a macro.

I have gotten step 1 to work great, but step 2 and 3 have been slowing me down:When it runs, it will open each file in the folder and paste the activesheet into the macro file as its filename. (ex: Opens HI.xls, copies all of it, and pastes as new sheet tab “HI.xls” in macro fileIt will then add 32 new columns to each sheet with the same headers. (A:AF). For some reason I can get A:E to work, but when I tried 32 it only added up to A:W, is there a reason for this?I have a spreadsheet with each sheet name in column A. Each sheet has data in a different column than another. I went through each sheet and wrote down the column the respective header should be pulling for that sheet. (So for Apples* sheetname, I want to add 32 columns to the left, and then “Apples” into ColumnA, “Apples” into ColumnB, “D-C-E” in Column D, Column F in Column E, and so on.Ultimately I would like to pull out 32 columns from each sheet with the same headers so they can be consolidated into one file where you can see sugar amount, skin type, food type, all in one.

I have attached my code below and a similar file is attached. Any help would be much appreciated.

Thanks.


	VB:
	
 
Sub CombineFiles() 
     
    Dim Path As String 
    Dim Filename As String 
    Dim Wkb As Workbook 
    Dim myname As String 
    Dim rcolumn As Range 
    Dim ws As Worksheet 
     
    Application.EnableEvents = False 
    Application.ScreenUpdating = False 
    Path = ActiveWorkbook.Path 'Change as needed
    Filename = Dir(Path & "*.xls", vbNormal) 
    Do Until Filename = "" 
        If Filename  ThisWorkbook.Name Then 
            Set Wkb = Workbooks.Open(Filename:=Path & "" & Filename) 
            myname = Left(ActiveWorkbook.Name, 28) 
            ActiveWorkbook.ActiveSheet.Name = myname 
            ActiveSheet.Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count) 
            Wkb.Close False 
            Filename = Dir() 
        Else: Filename = "" 
        End If 
    Loop 
     
    Application.EnableEvents = True 
    Application.ScreenUpdating = True 
     
End Sub 
Sub test() 
    Dim sht As Object 
    For i = 2 To Sheets.Count 
        Columns("A:E").Select 
        Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove 
    Next i 
End Sub 

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


hello everyone,
this is my first post and i thank you in advance for any help you can provide.
i am as new of a newbie as there is at excel. i have recently discovered mail merge and have been manually entering information on
a spreadsheet in the following format
first name last name street city state zip date AGE GENDER john doe 123 main street new york ny 1234 10/1/2011 25 m

it has worked very well but the amount of manual data entry has caused me to seek an alternative.
the report i can generate is in the format :

Name A/R Acct Sex Age Relationship Address DOE , JOHN F 73 1234 Main Street
New York,NY 12345

so i was able to successfully scan the report and use OCR to convert it to text. but i have since been struggling to do 2 things:

#1. getting the cells that have multiple info (ie Doe,John ; F 73; and New york,NY 1234) to be in individual cells (ie John; Doe; F; 73 ,etc) but i think by using data; text to columns i've been pretty successful.

#2 this is where i really neeed the help. getting the New York,NY 12345 to be in the same line as the other info. It currently is in the row beneath all the other information.
i tried recording a macro that cut from say f1 and pastes to e4 , but i do not know how to make it run on the entire spreadsheet (ie f3 to e6, f5 and e8....)
i am not sure if other issues will become apparent later (like deleting the now vacant cell, and i just noticed my sheet is John Doe and the report is Doe,John (i assume i could just reverse the order on the spreadsheet, but if that is unnecessary please let me know the trick to that fix.
i hope this makes sense and i greatly appreciate the help.
thanks,
brian

Hi,

I have a spreadsheet downloaded from our e-commerce system that lists product titles in one column and their prices in another. There are various other columns listing other attributes as well. The idea is that you can make mass changes to the spreadsheet, such as a price change, and upload it to the e-commerce system, which translates it to the actual products on the site.

We have a range of maps, some of which have titles like '1234O - Calais', and some with titles like '5678ET - Boulogne'. The maps with just one letter in the 'code number' are 9.99. The maps with 2 letters in the code number should be 10.99, but are currently 9.99. What I want to do is change the price for all the maps with ET (and OT) in the title. The problem is there are loads of them and they are in no particular order.

I could do a Find All for all the OTs, manually select the corresponding prices, paste in the new price, then repeat for the ETs, but this would take ages. Is there a quicker way to go about it? I assume something clever could be done with a bit of VB, but I'm a complete newbie when it comes to using it in Excel.

Thanks in advance.

Hi all!

Another dim question that I'd love some help with:

I'm selecting a range of cells from one sheet, and then attempting to paste it into another sheet below the data that is in that sheet.

I've tried various ways of doing this, but I cannot get my code to like the destination cell.

I've changed the order of the code so that it selects the destination cell before it cuts the data, in case that was causing a problem, but it doesn't seem to have made any difference

I assume it's because I'm trying to tell it to use the ActiveCell in the second sheet. I've also tried defining the selected cell as a range, and using the range name, and still I cannot get it to work. (It works fine if I just put in ("A12") instead )

I assume I'm being very thick, but as you can see from my indecisiveness around using .Activate or .Select, everything in VBA is new to me, so any help is gratefully received!! : D


	VB:
	
 
Worksheets("Ongoing Sickness").Activate 
Range("A3").Activate 
ActiveCell.End(xlDown).Activate 
ActiveCell.Offset(1, 0).Select 
 
 'cut and paste 'new sickness' into 'Ongoing sickness' sheet...
 
Worksheets("new Sickness").Activate 
Range("A3").Select 
Range(ActiveCell, ActiveCell.End(xlDown).EntireRow).Select 
Selection.Cut Sheets("Ongoing Sickness").Range(ActiveCell) 

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


I am trying to do something that sounds
simple but clearly is not (for me atleast). The simplest way to describe what I
am looking for is this:

I have a spreadsheet with columns all the way
from A to O. I have column O as a last update column I am currently manually
inputing the value constantly and would like some help to solve this.

Each ROW is its own order and so I want to auto update that last cell to
show the last date and/or time anything in that single row has changed value.
All the columns do not show just numbers, they are half numbers half letters so
I am not sure if that makes a difference or not and I rather throw it out there.

I have seen ALOT of theses VBA scripts (sorry if I labeled it wrong, I
clearly am not the best in this deep of detail) and I have seen many people say
work but I honestly do not have a clue what to do. I read where people say
"paste this code in the VBA window" which I can pull up and paste into however
whats my next step? How the heck do I confirm this is working? If you can help
me please give me the exact steps to make it work, such as delete or dont delete
the auto text that pops up in the vba screen, what to press after I paste the
code, what part of the code to switch and so on... you can pretend that you are
trying to teach this to an 11 year old if it makes it easier to understand just
how lost I am here.

hellllllppppP!!!! =)

Hello all,

I am using Excel 2007 but am running both workbooks in Excel 97-2003 mode for compatibility with other components that are a part of the overall process. I have the two workbooks linked (Book1 and Book2), where Column A in Book 1 feeds Column B in Book 2. When I copy Column A in Book 1 and then use paste link in Book 2 I have zeroes filling in the trailing blank cells (cells after my data ends) all the way down to row 65,XXX (for reference, there are currently only 9 rows with actual data, so the zeroes start at row 10).

Is there a way to prevent blank cells from being copied from Column A in Book 1? Or any other way to prevent all the trailing zeroes from appearing? I need only to remove/filter the trailing zeroes, as I may have cells inside my data where 0 is a valid value. The process may be even more complicated because the data from Book1 is dynamic and the number of rows within the book will change frequently.

I have attached an example of each book, although I removed most of the zeroes in Book 2 in order to reduce the size of the xls file for the purpose of uploading, but the gist of my problem is still evident.

I appreciate any and all help/comments. Hopefully I have provided all the details and a clear explanation of my problem! Book1.xlsBook2.xls

I have a piece of code that is failing only on one sheet of one workbook, but works fine on all the other sheets of all other workbooks I've tested it on. (I have 6 workbooks for testing, each with 3 sheets. All the data is in the same format on each sheet.) The code is designed to copy the Vendor ID to a blank column then split it with text-to-columns. All columns after the column the data is pasted to (J) are empty.

On this one sheet I get an error of "Run-time error '1004': TextToColumns method of Range class failed". Other sheets with this vendor in other workbooks work fine. I've tried copying the formating and columns after J from worksheets where the code ran successfully in case there was something I wasn't seeing, but those changed didn't help. I just checked, and the sheet in question is not protected.

Does anyone have any ideas on what is going wrong?


	VB:
	
Range("G:G").Select ' Selects Vendor ID column
Selection.Copy 
Range("J:J").Select ' Copies Vendor ID to J in order to split out region code
ActiveSheet.Paste 
Columns("J:J").Select 
Application.CutCopyMode = False 
Application.DisplayAlerts = False 
Selection.TextToColumns Destination:=Range("J1"), DataType:=xlDelimited, _ 
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _ 
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _ 
:="-", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True 
Application.DisplayAlerts = True 

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


Hi,

Looking for days and I have tried all kinds of things but nothing works. The lates was the Kickbutt find function.
Let me explain what I am tring to do. I have an estimate work book with 11 diffrent sheets of material. When I create an estimate I go thru the sheets and select the quanitity of each material needed. What I would like to do is search thru all the sheets and find all the quanititys in column A greater than 0, the copy the complete row A to F to a sheet named FoxOrd.
Trying AronBloods "Kickbutt find function" seemed to be the code to use but I could not get x = " >0 " to work. So I got rid of all the default 0 in the qty column and just left it blank and changed the code to x = "*" which searched and found all the numbered rows but this included the header row when pasted to the order sheet.
Any suggestions or anything else to use?

Heres the sample WB.

I am looking for a way to replicate (copy to adjacent columns) a predefined range of numbers (named "DATA") BUT so that each replica will be pasted with the numbers of the original "DATA"
in a different(!) (randomize !) order (not sorted).

Assume "DATA" is in column "A" and 5 cells deep with the numbers 1, 10, -6, 0, 7.

I will appreciate a code that will do the task an each run will add the numbers in an adjacent column without deleting the previous replica.

I tired to insert those numbers into an ARRAY however, I didn't find a VBA command to change their order in a Randomize way.

Thanks, Michael

Hi,

I have a file where in i want to paste certain columns data to sheet 2 ex. thr are 4 columns ID, Name, Address, Language etc i need only ID and Address or any other column in next sheet. Its order may change every time but column name will remain same.

Thx,
P

Hello all,

I am writing a spreadsheet to do the following.

I have clients that submit lists of words (3 columns of data) that need to be normalised (ie - certain punctuation removing, casing to lower etc) and then all duplicates to be removed .

It then pastes everything into a new sheet.

Now, it all works perfectly but, runs a little too slow.

Any ideas of how to speed this up? (i am probably missing something obvious!!)

Thanks

	VB:
	
 NormaliseDedupe() 
     
     '
     ' NormaliseDedupe Macro - July 2006
     ' This Macro will normalise the given list of keywords, removing punctuation and noise words,
     ' then remove any duplicated keywords.
     '
     ' Keyboard Shortcut: Ctrl+Shift+N
     '
     
     ' Turn off graphics and Automatic Calculation
     
     
    Application.Calculation = xlCalculationManual 
    Application.ScreenUpdating = False 
     
     ' Check Advertiser & Client names have been entered
     
    Dim my_advertiser 
    Set my_advertiser = Range("G1") 
    Dim my_client 
    Set my_client = Range("J1") 
     
    Sheets("Normalised Keywords").Select 
    If my_advertiser = "" Or my_client = "" Then 
        MsgBox "Enter the Advertiser & Client at the top of the sheet." 
        Exit Sub 
    End If 
     
     ' Check file can be saved without overwriting
     
    Dim my_date As String 
    my_date = Trim(VBA.Format(Now(), "DD.MM.YY")) 
     
    If Len(Dir("C:Normalised Keywords" & my_client & "(" & my_advertiser & ")" & _ 
    my_advertiser & "." & my_client & ".Normalised." & my_date & ".xls", vbDirectory)) > 0 Then 
        MsgBox "A workbook for this Advertiser & Client has already been saved today." & vbCrLf & _ 
        vbCrLf & "Either amend the Advertiser or Client name at the top of the sheet OR" & _ 
        vbCrLf & "delete the previously created file. Then click NormaliseDedupe again." 
        Exit Sub 
    End If 
     
     ' Check Original Keyword list not empty
     
    If Range("C4").Value = 0 Then 
        MsgBox "Enter the keywords to be cleaned in Column C - 'Original Keywords'" 
        Exit Sub 
    End If 
     
     ' Remove any formatting on Original Keywords
     
    Range("A4:C65536").Select 
    Selection.Copy 
    Range("D4:F65536").Select 
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ 
    :=False, Transpose:=False 
    Range("D4:F65536").Select 
    Selection.Cut 
    Range("A4").Select 
    ActiveSheet.Paste 
     
     ' Sort Original Keyword List, first A-Z (to remove blanks), then by length
     
    Range("A4:C65536").Sort Key1:=Range("C4"), Order1:=xlAscending, Header:= _ 
    xlNo, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ 
    DataOption1:=xlSortNormal 
     
    Dim my_range As Range 
     
    With Worksheets("Normalised Keywords") 
        Set my_range = .Range("D4:D" & .Range("C65536").End(xlUp).Row) 
        my_range.Formula = "=LEN(RC[-1])" 
    End With 
    Set my_range = Nothing 
     
    Range("A4:D65536").Sort Key1:=Range("D4"), Order1:=xlAscending, Header:= _ 
    xlNo, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ 
    DataOption1:=xlSortNormal 
    Range("D4:D65536").Select 
    Selection.Clear 
     
     ' Copy Sorted Keyword List to Sheet2
     
    Range("A4:C65536").Select 
    Selection.Copy 
    Sheets("Sheet2").Select 
    Range("A1").Select 
    ActiveSheet.Paste 
     
     ' Copy Original Keywords to another column
     
    Columns("C:C").Select 
    Selection.Copy 
    Columns("D:D").Select 
    ActiveSheet.Paste 
     
     ' Change all keywords to lowercase
     
    With Worksheets("Sheet2") 
        Set my_range = .Range("E1:E" & .Range("C65536").End(xlUp).Row) 
        my_range.Formula = "=LOWER(RC[-2])" 
    End With 
    Set my_range = Nothing 
     
    Range("E1:E65533").Select 
    Selection.Copy 
    Range("C1:C65533").Select 
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ 
    :=False, Transpose:=False 
     
     ' Add a space at the beginning and end of each keyword
     
    With Worksheets("Sheet2") 
        Set my_range = .Range("E1:E" & .Range("C65536").End(xlUp).Row) 
        my_range.Formula = "="" ""&RC[-2]&"" """ 
    End With 
    Set my_range = Nothing 
     
    Range("E1:E65533").Select 
    Selection.Copy 
    Range("C1:C65533").Select 
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ 
    :=False, Transpose:=False 
     
     ' Remove punctuation characters . , - ! ? @ # $   % ^ & * ( ) +  { } [ ]  / < > : ; = |  ` _ ~
     
    Columns("C:C").Select 
    Selection.Replace What:=".", Replacement:=" ", LookAt:=xlPart, SearchOrder _ 
    :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False 
    Selection.Replace What:=",", Replacement:=" ", LookAt:=xlPart, SearchOrder _ 
    :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False 
    Selection.Replace What:="-", Replacement:=" ", LookAt:=xlPart, SearchOrder _ 
    :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False 
    Selection.Replace What:="!", Replacement:=" ", LookAt:=xlPart, SearchOrder _ 
    :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False 
    Selection.Replace What:="~?", Replacement:=" ", LookAt:=xlPart, SearchOrder _ 
    :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False 
    Selection.Replace What:="@", Replacement:=" ", LookAt:=xlPart, SearchOrder _ 
    :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False 
    Selection.Replace What:="#", Replacement:=" ", LookAt:=xlPart, SearchOrder _ 
    :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False 
    Selection.Replace What:="$", Replacement:=" ", LookAt:=xlPart, SearchOrder _ 
    :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False 
    Selection.Replace What:="", Replacement:=" ", LookAt:=xlPart, SearchOrder _ 
    :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False 
    Selection.Replace What:="", Replacement:=" ", LookAt:=xlPart, SearchOrder _ 
    :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False 
    Selection.Replace What:="%", Replacement:=" ", LookAt:=xlPart, SearchOrder _ 
    :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False 
    Selection.Replace What:="^", Replacement:=" ", LookAt:=xlPart, SearchOrder _ 
    :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False 
    Selection.Replace What:="&", Replacement:=" ", LookAt:=xlPart, SearchOrder _ 
    :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False 
    Selection.Replace What:="~*", Replacement:=" ", LookAt:=xlPart, SearchOrder _ 
    :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False 
    Selection.Replace What:="(", Replacement:=" ", LookAt:=xlPart, SearchOrder _ 
    :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False 
    Selection.Replace What:=")", Replacement:=" ", LookAt:=xlPart, SearchOrder _ 
    :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False 
    Selection.Replace What:="+", Replacement:=" ", LookAt:=xlPart, SearchOrder _ 
    :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False 
    Selection.Replace What:="", Replacement:=" ", LookAt:=xlPart, SearchOrder _ 
    :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False 
    Selection.Replace What:="{", Replacement:=" ", LookAt:=xlPart, SearchOrder _ 
    :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False 
    Selection.Replace What:="}", Replacement:=" ", LookAt:=xlPart, SearchOrder _ 
    :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False 
    Selection.Replace What:="[", Replacement:=" ", LookAt:=xlPart, SearchOrder _ 
    :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False 
    Selection.Replace What:="]", Replacement:=" ", LookAt:=xlPart, SearchOrder _ 
    :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False 
    Selection.Replace What:="", Replacement:=" ", LookAt:=xlPart, SearchOrder _ 
    :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False 
    Selection.Replace What:="/", Replacement:=" ", LookAt:=xlPart, SearchOrder _ 
    :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False 
    Selection.Replace What:="", Replacement:=" ", LookAt:=xlPart, SearchOrder _ 
    :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False 
    Selection.Replace What:=":", Replacement:=" ", LookAt:=xlPart, SearchOrder _ 
    :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False 
    Selection.Replace What:=";", Replacement:=" ", LookAt:=xlPart, SearchOrder _ 
    :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False 
    Selection.Replace What:="=", Replacement:=" ", LookAt:=xlPart, SearchOrder _ 
    :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False 
    Selection.Replace What:="|", Replacement:=" ", LookAt:=xlPart, SearchOrder _ 
    :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False 
    Selection.Replace What:="", Replacement:=" ", LookAt:=xlPart, SearchOrder _ 
    :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False 
    Selection.Replace What:="`", Replacement:=" ", LookAt:=xlPart, SearchOrder _ 
    :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False 
    Selection.Replace What:="_", Replacement:=" ", LookAt:=xlPart, SearchOrder _ 
    :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False 
    Selection.Replace What:="~~", Replacement:=" ", LookAt:=xlPart, SearchOrder _ 
    :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False 
     
     ' Remove apostrophes (not those that are within word)
     
    Selection.Replace What:="''", Replacement:="'", LookAt:=xlPart, SearchOrder _ 
    :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False 
    Selection.Replace What:="'s ", Replacement:=" ", LookAt:=xlPart, SearchOrder _ 
    :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False 
    Selection.Replace What:="' ", Replacement:=" ", LookAt:=xlPart, SearchOrder _ 
    :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False 
    Selection.Replace What:=" '", Replacement:=" ", LookAt:=xlPart, SearchOrder _ 
    :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False 
    Selection.Replace What:=" ' ", Replacement:=" ", LookAt:=xlPart, SearchOrder _ 
    :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False 
    Selection.Replace What:=" t'", Replacement:=" ", LookAt:=xlPart, SearchOrder _ 
    :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False 
    Selection.Replace What:=" d'", Replacement:=" ", LookAt:=xlPart, SearchOrder _ 
    :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False 
     
     ' Remove noise words
     
    Selection.Replace What:=" a ", Replacement:=" ", LookAt:=xlPart, SearchOrder _ 
    :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False 
    Selection.Replace What:=" about ", Replacement:=" ", LookAt:=xlPart, SearchOrder _ 
    :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False 
    Selection.Replace What:=" an ", Replacement:=" ", LookAt:=xlPart, SearchOrder _ 
    :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False 
    Selection.Replace What:=" and ", Replacement:=" ", LookAt:=xlPart, SearchOrder _ 
    :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False 
    Selection.Replace What:=" at ", Replacement:=" ", LookAt:=xlPart, SearchOrder _ 
    :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False 
    Selection.Replace What:=" by ", Replacement:=" ", LookAt:=xlPart, SearchOrder _ 
    :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False 
    Selection.Replace What:=" for ", Replacement:=" ", LookAt:=xlPart, SearchOrder _ 
    :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False 
    Selection.Replace What:=" from ", Replacement:=" ", LookAt:=xlPart, SearchOrder _ 
    :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False 
    Selection.Replace What:=" how ", Replacement:=" ", LookAt:=xlPart, SearchOrder _ 
    :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False 
    Selection.Replace What:=" in ", Replacement:=" ", LookAt:=xlPart, SearchOrder _ 
    :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False 
    Selection.Replace What:=" is ", Replacement:=" ", LookAt:=xlPart, SearchOrder _ 
    :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False 
    Selection.Replace What:=" on ", Replacement:=" ", LookAt:=xlPart, SearchOrder _ 
    :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False 
    Selection.Replace What:=" the ", Replacement:=" ", LookAt:=xlPart, SearchOrder _ 
    :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False 
    Selection.Replace What:=" what ", Replacement:=" ", LookAt:=xlPart, SearchOrder _ 
    :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False 
    Selection.Replace What:=" of ", Replacement:=" ", LookAt:=xlPart, SearchOrder _ 
    :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False 
    Selection.Replace What:=" or ", Replacement:=" ", LookAt:=xlPart, SearchOrder _ 
    :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False 
    Selection.Replace What:=" to ", Replacement:=" ", LookAt:=xlPart, SearchOrder _ 
    :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False 
    Selection.Replace What:=" with ", Replacement:=" ", LookAt:=xlPart, SearchOrder _ 
    :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False 
     
     ' Trim spaces from cleaned keywords
     
    With Worksheets("Sheet2") 
        Set my_range = .Range("E1:E" & .Range("C65536").End(xlUp).Row) 
        my_range.Formula = "=TRIM(RC[-2])" 
    End With 
    Set my_range = Nothing 
     
    Range("E1:E65533").Select 
    Selection.Copy 
    Range("C1:C65533").Select 
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ 
    :=False, Transpose:=False 
     
     ' Dedupe & return cleaned keywords to Normalised Keywords sheet
     
    Rows("1:1").Select 
    Selection.Insert Shift:=xlDown 
    Range("A1").Select 
    ActiveCell.FormulaR1C1 = "Camp" 
    Range("B1").Select 
    ActiveCell.FormulaR1C1 = "Ord" 
    Range("C1").Select 
    ActiveCell.FormulaR1C1 = "Kwd" 
    Columns("A:C").AdvancedFilter Action:=xlFilterInPlace, Unique:=True 
    Range("A2:C65534").Select 
    Selection.Copy 
    Sheets("Normalised Keywords").Select 
    Range("E4").Select 
    ActiveSheet.Paste 
     
     ' Delete cleaned keywords from Sheet2, swap 2 columns & copy rejections to Normalised Keywords sheet
     
    Sheets("Sheet2").Select 
    Cells.Select 
    Selection.ClearContents 
    ActiveSheet.ShowAllData 
    Columns("C:C").Select 
    Selection.Copy 
    Columns("E:E").Select 
    ActiveSheet.Paste 
    Columns("C:C").Select 
    Selection.Delete Shift:=xlToLeft 
    Range("A2:D65534").Select 
    Selection.Copy 
    Sheets("Normalised Keywords").Select 
    Range("I4").Select 
    ActiveSheet.Paste 
     
     ' Sort 3 lists alphabetically by keyword, order and campaign
     
    Range("A4:C65536").Sort Key1:=Range("C4"), Order1:=xlAscending, Header:= _ 
    xlNo, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ 
    DataOption1:=xlSortNormal 
    Range("A4:C65536").Sort Key1:=Range("B4"), Order1:=xlAscending, Header:= _ 
    xlNo, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ 
    DataOption1:=xlSortNormal 
    Range("A4:C65536").Sort Key1:=Range("A4"), Order1:=xlAscending, Header:= _ 
    xlNo, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ 
    DataOption1:=xlSortNormal 
     
    Range("E4:G65536").Sort Key1:=Range("G4"), Order1:=xlAscending, Header:= _ 
    xlNo, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ 
    DataOption1:=xlSortNormal 
    Range("E4:G65536").Sort Key1:=Range("F4"), Order1:=xlAscending, Header:= _ 
    xlNo, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ 
    DataOption1:=xlSortNormal 
    Range("E4:G65536").Sort Key1:=Range("E4"), Order1:=xlAscending, Header:= _ 
    xlNo, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ 
    DataOption1:=xlSortNormal 
     
    Range("I4:L65536").Sort Key1:=Range("K4"), Order1:=xlAscending, Header:= _ 
    xlNo, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ 
    DataOption1:=xlSortNormal 
    Range("I4:L65536").Sort Key1:=Range("J4"), Order1:=xlAscending, Header:= _ 
    xlNo, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ 
    DataOption1:=xlSortNormal 
    Range("I4:L65536").Sort Key1:=Range("I4"), Order1:=xlAscending, Header:= _ 
    xlNo, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ 
    DataOption1:=xlSortNormal 
     
     ' Clean up Sheet2 worksheet
     
    Sheets("Sheet2").Select 
    Cells.Select 
    Selection.Clear 
    Range("A1").Select 
     
     ' Repair Original Keywords counter
     
    Sheets("Normalised Keywords").Select 
    Range("C2") = "=COUNTA($C$4:$C$65536)" 
    Range("A1").Select 
     
     ' Create directory named Client(Advertiser) & Save Normalised Keywords worksheet as new workbook
     ' New workbook named Advertiser.Client.Normalised.Date.xls
     
    If Len(Dir("C:Normalised Keywords", vbDirectory)) = 0 Then 
        MkDir ("C:Normalised Keywords") 
    End If 
     
    If Len(Dir("C:Normalised Keywords" & my_client & "(" & my_advertiser & ")", vbDirectory)) = 0 Then 
        MkDir ("C:Normalised Keywords" & my_client & "(" & my_advertiser & ")") 
    End If 
     
    Dim my_worksheet As Worksheet 
    Set my_worksheet = ActiveSheet 
    my_worksheet.Copy 
    my_worksheet.Cells.Copy ActiveSheet.Cells(1, 1) 
     
    ActiveWorkbook.SaveAs ("C:Normalised Keywords" & my_client & "(" & my_advertiser & ")" & _ 
    my_advertiser & "." & my_client & ".Normalised." & my_date & ".xls") 
    Set my_worksheet = Nothing 
     
     ' Cleanup Normalised Keywords template sheet
     
    Application.Goto Workbooks("Normalise&Dedupe.xls").Sheets("Normalised Keywords").Range("A1") 
    Range("A4:L65536").Select 
    Selection.Clear 
    Range("G1").Select 
    Selection.ClearContents 
    Range("J1").Select 
    Selection.ClearContents 
    Range("A1").Select 
     
     ' Change view to newly created file
     
     '    Application.Goto Workbooks(my_advertiser & "." & my_client & ".Normalised." & my_date & ".xls").Sheets("Normalised
Keywords").Range("A1")
     
     ' Clear variables
     
    Set my_advertiser = Nothing 
    Set my_client = Nothing 
     
     ' Turn graphics and automatic calculation back on
     
    Application.ScreenUpdating = True 
    Application.Calculation = xlCalculationAutomatic 
     
End Sub 

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



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