Over 1 million tech questions and answers.

Excel macro - copy specific columns from different files to one file

Q: Excel macro - copy specific columns from different files to one file

Hi Gurus,

I have many excel files containing similar data.

1. I want to copy specific columns (columns A to U) from all files and paste the consolidated data into another excel file.

2. I also want to copy specific columns (columns C,D,E,G,H,K,L,T,V,W,X,Y) and paste the consolidated data in a different excel.

Please help.

RELEVANCY SCORE 200
Preferred Solution: Excel macro - copy specific columns from different files to one file

I recommend downloading and running Reimage. It's a computer repair tool that has been proven to identify and fix many Windows problems with a high level of success.

I've used it in the past to identify and fix everything from blue screens (BSOD's), ActiveX errors, corrupt files and processes, dll/exe/sys errors, recover lost memory, Windows update problems, defragging, malware removal etc.

You can download it direct from this link http://downloadreimage.com/download.php. (This link will automatically start a download of Reimage that you can save to your computer.)

A: Excel macro - copy specific columns from different files to one file

Are all of these source Excel files stored in the same directory? Do they need to be opened and consolidated in any particular order? More details please. If possible please try to provide a sample file with any sensitive data removed.

Regards,
Rollin

Read other 1 answers
RELEVANCY SCORE 114.4

I have searched for a similar issue but was unsuccessful. I need to create a macro to do what will surely be a miserably repetitive job of transposing data for a whle bunch of files. I need to get into each excel file and copy certain data fields from column B (B5-B8 turquoise, B13-B15 bright greeen, B23-B27 bklue, B29-B30 pink, and B35 tan) into sequential cells in another file into a single row under a heading A2 - O2. I will need to do this several hundred times, open a file, copy the fields and all the files selected data from a column will wind up in a single excel data sheet in columns.

I thought I would just do a careful record macro, but cannot get 2007 to record beyond my first cell copy. I have named both files xlsm and changed settings to no avail.

The source file is attached and called Source and Detsination is called destination. Any help or a macro would be greatly appreciated!!attached the source file (Source ) I collor codes the field to be copied in the source as indicated aboue with the header fields in the destination if that helps. Thank you VERY much!!
 

Read other answers
RELEVANCY SCORE 103.2

I have done a search, and could not find it.

I know there was a post on this earlier but I could not find the post I was looking for.

What I am looking for a macro that will copy specific columns and put them on another worksheet.

i.e.

With Columns between A and AA
Column F,K,M,Q,S,U,Y would all be copied to Sheet 2

Thanx in advance.
 

A:Excel - Copy Specific Columns

Can you not just use the macro recorder to accomplish what you want?

Rollin
 

Read other 3 answers
RELEVANCY SCORE 101.2

Hello,

I need help to create an Excel macro that would

1. ask users to select x number of rows to be copied from one worksheet of one Excel file
2. once users have selected the rows to be copied, then the rows are copied from the one original Excel file into X number of target Excel files

Notes:
the target Excel files are all based on the same template
there is one worksheet in each of the target Excel files

in the target files, the rows should be copied from the first available empty row, going down

Looking forward to your help!

Thanks a lot.
Mzz
 

A:Macro needed to copy x number of rows from one Excel file into a batch of Excel files

Hi, welcome to the board.
Not much info there to get the correct picture.
Sample? Of source and template please
And not to forget, what version of Excel are you and the users using?
 

Read other 1 answers
RELEVANCY SCORE 100.4

What would be the best way to find a specific Column Header, and copy the data found in the column until the column header is found again?
 

A:Solved: Excel Macro Copy Data From Columns

Read other 8 answers
RELEVANCY SCORE 99.2

Hi excel-macro experts, I am writing a data-compiling macro which does, 1)select folder, 2)open xls files in the folder, 3)select all data for each file (only sheet 1 has data), 4)create a new file in the folder (let's say summary file), 5)paste data selected in the process (3) to the summary file created. With a lot of help from many websites, the following macro has been created. However, there is a PROBLEM that is when the data are pasted, all data were pasted into one column (sorce data of each original file has many columns). I need to avoid this. All I want is pasting the source data to Sheet1 of the summary file with the same number of columns (all source data files has the same number of columns) as the sorcce data has. Your help would be greately appreciated!!!

Function RDB_Last(choice As Integer, rng As Range)

' A choice of 1 = last row.
' A choice of 2 = last column.
' A choice of 3 = last cell.
Dim lrw As Long
Dim lcol As Integer
Select Case choice
Case 1:
On Error Resume Next
RDB_Last = rng.Find(What:="*", _
After:=rng.Cells(1), _
LookAt:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
Case 2:
On Error Resume Next
RDB_Last = rng.Find(What:="*", _
After:=rng.Cells(1), _
LookAt:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column
On Error GoTo 0
Case 3:
On Error Resume Next
lrw = rng.... Read more

A:Help!: Excel Macro; copy multiple files into one file

Can you put your code in the CODE blocks (there is a button in "Go Advanced" mode)? It'll make it much easier to read - that is, if you've indented your code.
 

Read other 1 answers
RELEVANCY SCORE 94

Please Help - ASAP - Thank you in Advance.
(I did also post this under Business Applications, before realizing it should probalby go here - sorry about that)

I'm just STUCK on this one last bit of my code to finish off this macro for a meeting today

I am Copying a Range of data, from a specific sheet on about 36 Workbooks
I need to paste only certain columns from that copied info, into a new worksheet.

Maybe I'm making this sound too difficult... at this point all I need is:

Columns A-E, K, P, S-U, W, AB-AC, AE-AH, AJ-AK, from the "TargetData" (Defined Range) on Customer Targets worksheet of Test1, to Paste into Columns A-S of the Build Worksheet

This should be easy right? I'm having a mind block...
 

A:Excel Macro - Move Specific Columns from Range to New Range - ASAP If Possible

There have been a bunch of views, but not replys or suggestions. Am I not including the right information? Is this not posted in the right place? Can someone please help me?
 

Read other 2 answers
RELEVANCY SCORE 93.6

Please Help - ASAP - Thank you in Advance.

I'm just STUCK on this one last bit of my code to finish off this macro.

I am Copying a Range of data, from a specific sheet on about 36 Workbooks
I need to paste only certain columns from that copied info, into a new worksheet.

I think I've attached a sample of the code, and some test workbooks I've been using.

Any advise would be helpful. I know this should be easier than I'm probably making it...
 

A:Excel Macro - Move Specific Columns from Range to New Range

Read other 16 answers
RELEVANCY SCORE 92

Hi all,

I really need help with this, as I have tried means to get it right, but I have no experience in VBA and it seem difficult. Any help will be greatly appreciated.

I need to copy specific cells in a form (in Excel format) that is input by users and paste them into the summary workbook.

The form is fixed, but every time someone sends in a new form, I'll need to update the data in a summary workbook. So it'll be constant updating and I need to ensure that a new row in the Summary folder is used for each form that is sent in.

I'm trying to write a macro that is able to automate the data transfer.

Eg. I need to:

copy the data from D6 in the file Form to the celll A2 Summary file,
D7 in Form to B2 etc.
Attached is the form and my summary sheet.

Thanks in advance!
 

A:Help Needed for Excel Macro - Copy specific cells and paste into a Summary workbook

Hi all,

Below is the vba code that I have written.
But the problem is I am not sure how to define the destination workbook as a file, which is also where this macro will be stored.

It does not seem to work when I tried to put ThisWorkbook or Activeworkbook. I did not want to add a new workbook either.

Sub copyWorkbooks()
Dim MyPath As String
Dim SourceRcount As Long, FNum As Long
Dim mybook As Workbook, DestWks As Workbook
Dim sourceRange As Range, destrange As Range
Dim rnum As Long, CalcMode As Long
Dim SaveDriveDir As String
Dim FName As Variant

' Set application properties.
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
.EnableEvents = False
End With
SaveDriveDir = CurDir
' Change this to the path\folder location of the files.
ChDirNet "C:\Documents and Settings\chinba\Desktop\ASL Exception\"
FName = Application.GetOpenFilename(filefilter:="Excel Files (*.xl*), *.xl*", _
MultiSelect:=True)
On Error Resume Next

'find the last row
RDB_Last = rng.Find(What:="*", _
after:=rng.Cells(1), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
If IsArray(FName) Then
'Loop through all files in the myFiles array.
For FNum = LBound(FName) To UBound(FName)
Set mybook = Nothing
On Error Resume Next
Set mybook = Workbooks.Open(FName(FNum))
On Error GoTo 0
If Not mybook Is Nothing Then
On Error Resu... Read more

Read other 1 answers
RELEVANCY SCORE 90.4

I am trying to write some vba code to Copy the the contents from each row in excel and make it into a new text file. I need the 1st Row to be in each text file though. So the 1st loop would be A1 & A2 row copy next loop would be A1 & A3 copy. When i paste the values in the new sheet to be saved as the text file I need to transpose the paste so row 1 now will be in column A. The filename would be created from the value in Column A. I have uploaded some example files. Any help would be greatly appreciated. The bonus would be if i could get any empty spaces removed from the cells.

I tried modifying some code that Jimmy the Hand wrote linked here
http://forums.techguy.org/business-applications/951580-save-excel-each-row-html.html
with no luck.
 

A:Solved: Copy Columns from excel into new file

Read other 7 answers
RELEVANCY SCORE 88.4

Hi

Ive been searching the net for help with this and i just cant find what im looking for.

Im still new at excel macro's so still learning everyday.

I need help to wright a macro that will search in a folder for a file with a specific keyword that can be in the file name.

example: lets say i want to open a file that is in a folder that has "leadership" in the files name.
Because i send the file to people called "leadership.xls" and i recieve files back called "copyofleadership.xls" and different other names but there will always be leadership in the file name.

Can anyone please help me with this.

Thanks
bill6432
 

A:Excel macro to open a file in a folder with a specific keyword in the file names

Read other 6 answers
RELEVANCY SCORE 88.4

I have been working on a project for a few weeks and I could really use some help. I'm attaching a dummy file (example1) that is an example of how my XLS comes to me each month.

I need a macro that leaves me with the second file (example2). If the macro could just grab the Student name, then put the following info for each student all on 1 row, that would be perfect:

Child's Name | Date In | Time In | Rounded | Time Out | Rounded

Sometimes for some reason there is a student with a (Cont) after their name. I need that row deleted and just pull the times up onto the same row as other times. The example2 file might make sense.

Here are the steps I am doing manually that I hope a macro could do for me!
Un-merge cells: B10
Un-merge any cells with a child's name such as Doe, Jane
Delete any row with a childs name that has (Cont) after it such as Doe, Jane (Cont)
Delete rows: 1-9
Delete columns: A, C, F, H, I, J, M, N, O, and P
Manually move all info up to one row per student
Delete all empty rows below
See example2 as the completed file but I only moved a few of the rows per student for the sake of time.

Is there any way to do all of this with a macro?
 

A:Solved: XLS Macro to delete specific rows/columns and move columns to rows.

Read other 16 answers
RELEVANCY SCORE 85.6

Hello all. Not a regular user of Excel; but do need help in creating something that would be useful to me and a few others at work. I suppose the best way of explaining what I'm after is by giving an example.

I have a directory C:\Users\Tekko\Desktop\Maintenance Project\Cape Nelson
In Cape Nelson are a number of folders named alpha beta charlie delta echo and foxtrot and so on.

I would like to have an excel template in "Cape Nelson" with a macro that when activated names the file as whatever folder name might be in say cell A1 and whatever ever date might be in cell B1. Eg charlie_15-mar-2013.xls
This then is saved in the relevant folder. So in the end I would end up with
C:\Users\Tekko\Desktop\Maintenance Project\Cape Nelson\charlie\charlie_15-mar-2013.xls

Also the macro script would ensure that the macro was disabled in the saved file.

Hoping this is achievable and look forward to replies.
 

A:Solved: Excel 2010 - Macro to name and save file to a specific folder

Read other 16 answers
RELEVANCY SCORE 84.8

Hello,

I am trying to learn rules in altering a macro that has been recorded. I need the following code to execute across columns A-AX, where each column is to be sorted independently of the others. Thank you in advance for your time.
Code:

Range("A2:AX21").Select
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("A2"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").Sort
.SetRange Range("A2:A21")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub
 

A:Solved: How to execute a macro across a specific number of columns

Read other 7 answers
RELEVANCY SCORE 83.6

I have been reading up on creating log files with Excel Macros.

I am trying to figure out if it is possible to have a Macro output the contents of the currently selected Cell to a text file?
 

A:Solved: Excel Macro Copy Cell to log file.

Read other 16 answers
RELEVANCY SCORE 82

I have a spreadsheet that is used by a number of users and has a macro built into it to collect updates from several other sheets. I want to add to the macro so that once it has been updated and saved the macro then saves another version of the sheet in a seperate folder with the date and time it was saved as part of the file name.

I have tried

ChDir"H:\Resourcing Teams\Placements\Weekly activity\Archive"
ActiveWorkbook.SaveAs Filename:= _
"H:\ResourcingTeams\Placements\Weekly activity\Archive\" & Now.Day & Now.Month &Now.Year &" .xlsx", _
FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

But get errors

Any suggestions greatly appreciated
 

A:Solved: Excel Macro to save a copy of the file in new location with the date

Read other 9 answers
RELEVANCY SCORE 80

Hallo,
seit kurzer Zeit bewege auch ich mich durch das "Matlab-Universum". Demgem? freue ich mich ?ber jeden kleinen Schritt, der mir gelingt. Nun habe ich jedoch eine Aufgabe erhalten, welche - so denke ich - mein bisheriges K?nnen bei weitem ?bersteigt.
Folgender Sachverhalt:
Mir liegt in K?rze eine sehr umfangreiche Excel-Tabelle (mehrere 1.000 Spalten/Zeilen) vor, in welcher nur gewisse Spalten f?r weitergehende Berechnungen erforderlich sind. S?mtliche Spalten sind bspw. in Zeile f?nf mit einer ?berschrift versehen. Dabei ist nur ein Teil der besagten ?berschrift jeweils relevant.
Meine erste Frage ist nun, wie ich die ?berschriften in der Zeile (hier bspw. f?nf) pr?fen kann, ob etwa neben anderen alpha-numerischen Angaben ein bestimmter Begriff (oder mehrere) auftauchen? Ist dies der Fall soll die entsprechende Spalte in Matlab eingelesen werden.
M?glicherweise gibt es f?r diesen Prozess sogar eine "elegantere" Darstellung, als die aus Excel herausgefilterten (und in Matlab integrierten) Spalten alle einzeln im "Workspace" abzulegen. Als absoluter Anf?nger bin ich dahingehend nat?rlich ?berfragt und freue mich selbstverst?ndlich, wenn mein Vorhaben ?berhaupt (auf relativ humane Art und Weise) m?glich ist.
Meine zweite Frage betrifft die Vorbereitung (und Durchf?hrung) einer Regression.
Angenommen ich habe das vorherige Verfahren zwei Mal (f?r zwei verschiedene Begrifflichkeiten) angewendet..., wie kann ich anschlie?end die umfangrei... Read more

Read other answers
RELEVANCY SCORE 79.6

I was hoping someone could help me write or find a macro that would take two columns and move it to column A under the previous data and repeat for all columns. Basically user information is located in pairs of columns (typeofvalue, value). So column A and B are for user1, column C and D are user2, column E and F are user3, etc. Bonus if I could have a space in between each once moved.

Current:


HTML Code:
A B C D
User1First User1Last User2First User2Last
User1ValueTypeA1 User1ValueB1 User2ValueTypeA1 User2ValueB1
User1ValueTypeA2 User1ValueB2 User2ValueTypeA1 User2ValueB1
Desired:


HTML Code:
A B
User1First User1Last
User1ValueTypeA1 User1ValueB1
User1ValueTypeA2 User1ValueB2

SPACE/EMPTY ROW

User2First User2Last
User2ValueTypeA1 User2ValueB1
User2ValueTypeA1 User2ValueB1
Each user has 47 rows of "values", for the example I just did two rows worth.

A:Excel Macro to move columns to 1 row after each other.

I did a quick google and found this. It worked for you?

Excel macro to move columns to 1 row after each other

Read other 2 answers
RELEVANCY SCORE 79.6

I am trying to do a macro to multiply the several columns values, observing determined terms, but do not be getting. Attachment, worksheet with example and for macro that I typed but is not solving. I thank any help.
 

A:Excel VBA macro to multiply columns

Read other 15 answers
RELEVANCY SCORE 79.6

I am trying to create a macro in Excel 2007 that will sum two different columns of numbers. The columns will always be the same length. I am new at "trying" to create macros and I can't figure out how to keep the same range to sum just move it 3 columns to the right. A sample of the table data is below. Thank You!!

Sub Test()
On Error GoTo CancelExit:
With Selection
.Offset(.Rows.Count + 1, .Columns.Count).Cells(0, 0).FormulaR1C1 = "=SUM(" & .Address(, , xlR1C1) & ")"
.Offset(.Rows.Count + 1, .Columns.Count).Cells(0, 3).FormulaR1C1 = "=SUM(" & .Address(, , xlR1C1) & ")"
End With
CancelExit:
On Error GoTo 0
End Sub

I am trying to create a macro in Excel 2007 that will sum two different columns of numbers. The columns will always be the same length. I am new at "trying" to create macros and I can't figure out how to keep the same range to sum just move it 3 columns to the right. A sample of the table data is below. Thank You!!

Sub Test()
On Error GoTo CancelExit:
With Selection
.Offset(.Rows.Count + 1, .Columns.Count).Cells(0, 0).FormulaR1C1 = "=SUM(" & .Address(, , xlR1C1) & ")"
.Offset(.Rows.Count + 1, .Columns.Count).Cells(0, 3).FormulaR1C1 = "=SUM(" & .Address(, , xlR1C1) & ")"
End With
CancelExit:
On Error GoTo 0
End Sub

Sale Date Sale Price Adj Sale Price 2010 Land 2010 Imp 2010 Total
7/7/2009 10,000 10,000 13,300 0 13,300
7/7... Read more

A:Excel 2007 Macro Sum 2 Columns

Read other 13 answers
RELEVANCY SCORE 78.8

Hi all,

I have a requirement. Consider there is an excel sheet with values in several columns. Some of the columns inbetween are empty. So i have to somehow highlight the empty columns.
Note: All the columns have headings in the first row.
It is ok even to get the headings of the empty columns in seperate sheet.
Help me out guys.

Thanks in Advance,
Sughesh.
 

A:How to find Empty columns in Excel using Macro

will conditional format workto highlight blank cells if so

goto conditional format- click on cell A1 and then hightlight the range - if you want all the spreadsheet - click on the box between the columns and rows
then in conditional format
type

=A1=""
then set a format fill colour

heres a macro to remvoe blank columns
http://www.mrexcel.com/archive/VBA/11225.html
but may not work if you have a heading - as thats not a blank column

if you could load a dummy spreadsheet as an example- others here with more macro experience should be able to help
 

Read other 3 answers
RELEVANCY SCORE 78.8

I work on parts lists at work that have ben created by colleagues. They have approximately 80 columns. I need to refer to these parts lists when completing a separate excel file and I want to be able to hide certain columns. The columns that I want to hide are not beside each other so I can't use the *group* function. Well I suppose I could use it but I?d prefer to use a Macro as I'm sure it would be moe efficient.

I?m not up to speed with VBA and I?ve been attempting to record a Macro but have only been partially successful thus far. So on the attached is my macro recording effort. I wanted to hide columns G, T, AC, AF, AX and BA. In order to do this I am doing the following

View Tab > Record Macro
Enter macro name and description
Shift select columns G, T, AC, AF, AX and BA
Right click and hide them
Click on Stop Recording
I thought this would be sufficient. However, when I go back and run the macro it hides columns A:L, T, AC, AF, AX and BA. Can anyone show me what I?m doing wrong or is my understanding of how a macro works different to reality.

Moving on from this I would like to be able to do the opposite, i.e. unhide above columns (actually this macro works in the attached). Would also be very handy to have buttons that I could just click on that would do this.
 

Read other answers
RELEVANCY SCORE 78.8

I have a dump from a text file in two single columns. The first column is a block of 55 labels that repeat for every group of data, the second is the data for 110 transducers. Every transducer produce 55 single values (numerical or status(text)). I need to order this two columns dumps into a table. Where the first column are the labels and the rest 110 column the corresponding data one column per transducer.
How can we do this? Can any body help me?
 

A:Excel macro to move two Columns into a table

Hi and welcome to TSG!

Can you upload a sample of the textfile? I just need a few lines. It doesn't have to be real data. Just want to see the data structure.If you can provide this, I'm sure we will solve the problem.

Jimmy
 

Read other 1 answers
RELEVANCY SCORE 78.8

Hi,

I have data in the following row format:

0 4591 5 4491 10 4461 15 4441 20 4321

The data are X and Z points so the above example shows 5 pairs of data. I would like to place these pairs of data sequentially in two columns where one column has all the X's and the other has the related Z's. I have many, many rows of similar data that I need transferred into two columns.

0 4591
5 4491
10 4461
15 4441
20 4321

Could anyone help me out with a macro that can do this for me?

Thanks very much in advance.
 

A:Solved: Excel Macro - Rows to Columns

Welcome to the board.

Let's say the example pairs are in A1:J1.

With A1:J1 selected,

Sub test()
For Each Cell In Selection
If Cell.Column Mod 2 = 1 Then
Range("A" & Rows.Count).End(xlUp).Offset(1) = Cell.Value
Else
Range("B" & Rows.Count).End(xlUp).Offset(1) = Cell.Value
End If
Next Cell
End Sub

gives you actual pairs in A2:B6.

So, what do we need to adjust to make it compatible with your "many, many rows of similar data" scenario?
 

Read other 3 answers
RELEVANCY SCORE 78

I am trying to write a macro that compares cells in column A from sheet1 to cells from column A in sheet2 and if the values from sheet2 are not in sheet1 then I want to delete the entire row from Column A in sheet2.

for example

In sheet1, column A has a list of id no's (no duplicates) and in sheet2 Column A has list of id no's (with duplicates). I want to delete every row in Sheet2 Column A that is not in Sheet1 Column A.

Keep in mind that both sheets have several columns.
Also there is about 5000 rows in sheet 2

Any help would be greatly appreciated.

Thanks
 

A:Excel HELP macro to Delete Row Based 2 columns from 2 sheets

Welcome to the board.

While I understand each part of your post (I think), some parts seem to contradict others. So here's what I did.

In Sheet1!A1:A5 I entered 1 -- 2 -- 3 -- 4 -- 5.

In Sheet1!A1:A6 I entered 1 -- 2 -- 6 -- 3 -- 4 -- 5.

Then I wrote some code that removes the third row from Sheet2, since it contains 6 in column A which is not in col A of Sheet1.

How the code works is:

(i) inserts a new column A on Sheet2

(ii) enters a formula in new column A Sheet2, e.g.:

=MATCH(B1,Sheet1!A:A,0)

which returns a number if there's a match in column A Sheet1, #N/A if not

(iii) deletes any rows on Sheet2 with #N/A in column A

(iv) deletes column A on Sheet2 (redundant).

Obviously if this isn't what you need don't use it, post more info instead.

HTH

Sub test()
Sheets("Sheet2").Range("A1").EntireColumn.Insert
x = Rows.Count
y = Sheets("Sheet2").Range("B" & x).End(xlUp).Row
Sheets("Sheet2").Range("A1").Resize.FormulaR1C1 = "=MATCH(RC[1],Sheet1!C,0)"
Sheets("Sheet2").Range("A1").Resize.SpecialCells(xlCellTypeFormulas, 16).EntireRow.Delete
Sheets("Sheet2").Columns(1).Delete
End Sub
 

Read other 1 answers
RELEVANCY SCORE 78

I'm using some software to export payment information into a CSV file. This file needs to be in the bank's specified SIF format which requires all the data to be in one column.

The limitation with my software is that the detail of each payment gets exported into it's own separate column. For instance, cells A1:A10 contain payment details to one individual, B1:B10 to another, and so on. I require the whole lot combining into Column A, running from A1:A65536 (it will never go this far down the spreadsheet, but you get the idea).

I've tinkered with some basic copy and delete macros but I'm unable to make the headway that I need to. I don't have the touch when it comes to programming .

Thanks in advance
 

A:Solved: Excel: Macro to combine all Columns into Column A

Read other 12 answers
RELEVANCY SCORE 78

Hi, I'm new to macro. Hope you can help me with my problem. I've been stuck with this for a week with no result.

I have this table:

001 002 003 004
111 115 119 123
112 116 120 124
113 117 121 125

Let's say, the first row are the StoreID and all other digits below them are the ProductID

I want to convert this table into something like this:
001 111
001 112
001 113
002 115
002 116
002 117
003 119
003 120
003 121
004 123
004 124
004 125

I haven't made any macro before in MS Excel and this one is really getting the breath out of me.

Hope you can help me please.. I'd really appreciate it.

Thank you in advance!
 

A:Help with Macro in excel - Convert Rows to Repeating Columns

is this just a "one off" thing or do you have many sheets to convert? Because for one or a few sheets, it is not a lot of work to do manually...probably take a few minutes ( I 'll explain if you need)

to automate I can do it, but I need to build some loops and references to refer to the columns and ranges to move to a new list...it's a bit of work.

Please confirm also if it is only 4 columns, and or if the column count varies
 

Read other 1 answers
RELEVANCY SCORE 78

I have a flat file that when I read it into excel it populates column 1 of each row with data.
This data is actually a series of 5 fields that I need to have in columns and rows, that is,
column a1 thru a5 I need placed in a1, b1,c1,d1 and e1 followed by
column a6 thru a10 placed in a2,b2,c2,d2 and e2.
The data is balanced, that ism there is data in each of the 5 fields for a "record".
Any help would be appreciated.
THanks
 

A:Solved: excel macro to move columns to rows

Read other 8 answers
RELEVANCY SCORE 78

Hello - I found a previous post on this site that solves (I think) 90% of what I need to accomplish. I have a large spreadsheet that I need to:

· Delete column C
· Delete all rows without account numbers (Acct numbers will always be in column A)
· Delete all rows that have dates in column F other than “00/00/0000”.

The code I found on this site is :

Simple vba code:
lastrow = Range( "A65536").end(xlup).row
For y = LastRow To 2 Step -1
If Cells(y, x).Value = "" Then Cells(y, x).Delete shift:=xlUp
Next y
Set the range to the the column desired, in this case "A"
Lastrow checks the last row and starts from the end up.
Removes all empty rows entirely up to row 2 because row 1 generally is the header
Happy coding
Can anyone help me?
 

A:Excel VBA Macro to delete rows/columns with criteria

To delete column c, use:
Code:
Columns("E").EntireColumn.Delete
Delete all rows without account numbers (Acct numbers will always be in column A)
and
If Cells(y, "A").Value = "" Then Cells(y, "A").Delete shift:=xlUp

Use:
Code:
lastrow = cells(rows.count,1).end(xlup).row
For y = LastRow To 2 Step -1
If Cells(y, "A").Value = "" Then Rows(y).EntireRow.Delete
If Cells(y, "F").Value = “00/00/0000” Then Rows(y).EntireRow.Delete ' Delete all rows that have dates in column F other than “00/00/0000”.
Next y


 

Read other 1 answers
RELEVANCY SCORE 78

Hello - I found a previous post on this site that solves (I think) 90% of what I need to accomplish. I have a large spreadsheet that I need to:

· Delete column C
· Delete all rows without account numbers (Acct numbers will always be in column A)
· Delete all rows that have dates in column F other than “00/00/0000”.

The code I found on this site is :

Simple vba code:
lastrow = Range( "A65536").end(xlup).row
For y = LastRow To 2 Step -1
If Cells(y, x).Value = "" Then Cells(y, x).Delete shift:=xlUp
Next y
Set the range to the the column desired, in this case "A"
Lastrow checks the last row and starts from the end up.
Removes all empty rows entirely up to row 2 because row 1 generally is the header
Happy coding
Can anyone help me?
 

A:Excel 07 MACRO to delete rows/columns w/ criteria

Delete all rows without account numbers (Acct numbers will always be in column A)Click to expand...

If there are no acct numbers, will there be

blanks ""
Space " "
dash -
other ??
Can you provide a sample with dummy data so we can see the data structure?
 

Read other 1 answers
RELEVANCY SCORE 76.8

I'm a novice excel VBA macro user. I've manipulated already created macros, but I'm not sure how to create one from scratch. Can someone please help me create a macro.

I'd like to move the values highlighted in yellow to the cells highlighted in green...and then delete any rows that are empty.

Thanks in advance for the help.
 

A:Solved: Excel Macro to Move Data in rows to columns

Read other 9 answers
RELEVANCY SCORE 76

I have found a few posts here and there about how to move columns around but nothing quite what I m looking for.

So I get these data dumps from a vendor weekly , column headers are always the same but they are always in different order (strange I know)

Example

Email | Phone | Last Name | First Name

I would like to have a macro cut/paste entire rows and put in a specific order

First name |Last Name| Phone| Email

Any help would be great! Thank you!
 

A:Excel 2007 Β– Macro (newbie) - Move Columns bases on name question

How many total columns are we talking about? Is it possible to post a sample workbook with any sensitive data removed so I can see the structure.

Regards,
Rollin
 

Read other 3 answers
RELEVANCY SCORE 76

I need to delete multiple columns within an Excel worksheet based on the headers in the first row. What would be the best way to perform this operation?

I have been playing around with the code below, but I feel as though there could be a better way.

Sub test()

Dim myArr As Variant

myArr = Array("Test1", "Test2", "Test3")
If Range("C1").Value = myArr Then
Columns("C").Delete shift:=xlToLeft
ElseIf Range("D1").Value = myArr Then
Columns("D").Delete shift:=xlToLeft
End If

End Sub
 

A:Solved: Excel Macro -> Delete Multiple Columns Based on Criteria

Read other 16 answers
RELEVANCY SCORE 75.2

Hi,

I need help in Excel copying columns and paste it in rows.

Ex: I have Columns A and B.
Column A Column B
5.78% 4.51%
23.60% 32.50%
14.17% 16.41%
32.12% 54.16%

Now output should be in one row.

Row1 5.78% 4.51% 23.60% 32.50% 14.17% 16.41% 32.12% 54.16%
 

A:Copy columns and paste in rows in Excel

sub test()
dim rng as range
dim i as long
dim col as long
dim nextcol as long
dim lastrow as long
lastrow = range("A"&rows.count).end(xlup)
set rng = range("A2:B"&lastrow)
col=rng.columns.count
nextcol =col+1
for i=2 to lastrow
range(cells(i,1),cells(i,col)).copy cells(1,nextcol)
nextcol=nextcol+col
next
rng.delete
end sub
 

Read other 1 answers
RELEVANCY SCORE 74.4

I need a visual basic macro for excel 2002 that will do the following:

From sheet1:ColumnA
Select rows until sheet1:columnA value does not start with REC (as the no of rows is unbalanced, sometimes 7 rows, sometimes 10 rows, sometimes 8 rows, etc)
copy selected rows and transpose to sheet2:columnA to however many rows were selected

Repeat until end last row
My real table has 30000 rows and the rows are unbalanced

I am attaching a test file.

Please help me and thank you for your support and time.

Tech Support Guy System Info Utility version 1.0.0.2
OS Version: Microsoft Windows XP Professional, Service Pack 3, 32 bit
Processor: Pentium(R) Dual-Core CPU E5300 @ 2.60GHz, x86 Family 6 Model 23 Stepping 10
Processor Count: 2
RAM: 2047 Mb
Graphics Card: ATI Radeon HD 4300/4500 Series, 1024 Mb
Hard Drives: F: Total - 99998 MB, Free - 30856 MB; G: Total - 205236 MB, Free - 153405 MB; H: Total - 305168 MB, Free - 180367 MB;
Motherboard: MICRO-STAR INTERNATIONAL CO.,LTD, G31M3-L V2(MS-7529)
Antivirus: None
 

A:Visual Basic Macro for Excel 2002 columns to rows in new sheet using unbalanced data

Can you also attach a second workbook showing how the data should appear after the macro is run?

Regards,
Rollin
 

Read other 3 answers
RELEVANCY SCORE 74.4

Hello,
I've long used a simple macro on various spreadsheets to reset the last used cell:

Sub Reset_Range()
Application.ActiveSheet.UsedRange
End Sub

This works fine, but prior to using it, I would have to press CTRL + End to see where the current last cell is, then manually delete any blank columns and rows appearing at the end of the data, by selecting them by their column letters / row numbers, right clicking and selecting Delete. Failure to do this would mean the macro didn't successfully correct the last used cell.

I would like to add this stage into the macro.
I found some code on the web that allegedly removes all blank rows and columns, so I pasted it at the top of my existing macro:

Option Explicit

Sub DeleteBlankRows()
Dim Rw As Long, RwCnt As Long, Rng As Range

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

On Error GoTo Exits:
If Selection.Rows.count > 1 Then
Set Rng = Selection
Else
Set Rng = Range(Rows(1), Rows(ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row()))
End If
RwCnt = 0
For Rw = Rng.Rows.count To 1 Step -1
If Application.WorksheetFunction.CountA(Rng.Rows(Rw).EntireRow) = 0 Then
Rng.Rows(Rw).EntireRow.Delete
RwCnt = RwCnt + 1
End If
Next Rw

Exits:
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub

Sub DeleteBlankColumns()
Dim Col As Long, ColCnt As Long, Rng As Range

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

On ... Read more

A:Solved: Excel 2003 macro to delete blank columns & rows, then reset range

Read other 6 answers
RELEVANCY SCORE 74.4

Hello,
I need some help creating a macro with some complex (I think) functions. This one might be tough!

I have a document that has fill in fields. The document is protected in order to enable the fill in fields.
Users will enter information in the fields depending on the answers to the questions in the document. The document is attached to show what it looks like.

What I need is a button (called "copy") at the end of the document that the user can just click and the macro will only copy text formatted as header and any fill-in fields that were completed.

Then the user can use the copied data to paste it somewhere else either by using their mouse (right click + paste) or using the Ctrl + v.

I'm not even sure if that's possible but we'll see if someone can help. It would be extremely wonderful if someone could figure this one out.

THANK YOU!
 

A:Creating a Macro to copy specific data within a protected document

Hi Nena, welcome to the forum
I'm sure it will be possible to read all entries using a macro like the one below

Code:

Sub test2()
Dim fld As FormField
For Each fld In ActiveDocument.FormFields
Debug.Print fld.Name & " - " & ActiveDocument.FormFields(fld.Name).Result
Next
End Sub

I placed this macro in the documents VBA project,
I filled in "hello" in the Person: field and see here the result when I run the macro:
Code:

Dropdown4 - Select One
Text1 - hello
Text1 - hello
Text1 - hello
Text1 - hello
Text1 - hello
Text1 - hello
Text1 - hello
Text1 - hello
Text1 - hello
Text1 - hello
Text1 - hello
Text1 - hello
Dropdown4 - Select One
Text1 - hello
Text1 - hello
Text1 - hello
Text1 - hello
Text1 - hello
Text1 - hello
Dropdown4 - Select One
Text1 - hello
Text1 - hello
Text1 - hello
Text1 - hello
Text1 - hello
Text1 - hello
Text1 - hello
Text1 - hello
Dropdown4 - Select One
Text1 - hello
Text1 - hello
Text1 - hello
Text1 - hello
Text1 - hello
Text1 - hello
Text1 - hello
Text1 - hello
- Yes....complete a-c
- Yes....complete a-c
Text1 - hello
- Yes....complete a-c
Text1 - hello
- Yes....complete a-c
Text1 - hello
Text1 - hello
Text1 - hello
Dropdown5 - No
Text1 - hello
Text1 - hello
Dropdown5 - No
Text1 - hello
Dropdown6 - Select One
Text1 - hello
Text1 - hello
Text1 - hello
Text1 - hello
Dropdown5 - No
Text1 - hello
Text1 - hello
Dropdown5 - No
Text1 - hello
Text1 - h... Read more

Read other 1 answers
RELEVANCY SCORE 74

Hi,

I’m building a tool using Excel that resolves discrepancies between purchase orders and invoices. Things are moving along fine, but I would like to record/write a macro to perform a task. My macro knowledge is at a beginner level. The following is the task I would like to perform. If Sheet 2 (VNDR ADJ WKS), Cell GP78= “PO ADNL CST = INV ADNL CST” I would like a macro to automatically move me to Sheet 3 (PO REC WKS) and land me at Cell C450/Range("C450:BQ452"). Is this task possible via a macro and if so could you help me record/write the macro?

Thanks,

Warren
 

A:Writing a specific macro within Excel

Code:
Public Sub Test()

If Sheets("VNDR ADJ WKS").Range("GP78").Value = "PO ADNL CST = INV ADNL CST" Then
Sheets("PO REC WKS").Select
Range("C450:BQ452").Select
End If

End Sub
How will the macro be fired? Will you run the macro manually or do you want the code to kick off automatically under a certain condition? What exactly are you trying to accomplish? Many times there are much easier ways to do things that you may not know about. If you state exactly what you are doing maybe we can help you accomplish what you want more efficiently. If you need more help, include as many details as possible and try to post your workbook by using the "manage attachements" button at the bottom of the posting window.

Regards,
Rollin
 

Read other 1 answers
RELEVANCY SCORE 73.6

Hi guys,

I've attached the dummy file. You see the report groups all the relevant data pertaining to the person's name and puts it under the name of the person. I need a macro that will copy the name of the person in column A across all the rows of data pertaining to the person's name in column Z. I will not know which row the name of the person will be in or how many rows of data each person will have, so the macro needs to search through all the rows to find specific names.You'll see an example of what I need in column AA.

I then wrote a macro just based on reading in the net to remove the blank rows in column A. For example A6 is blank, so the macro I wrote deletes that column. The macro also deletes the "Total No. of Cases" row.

This is the macro:

Sub Deletejunk()
'
' Deletejunk Macro
' To delete rows containing *No. of Cases* & Blanks
'
' Keyboard Shortcut: Ctrl+Shift+O
'

With ActiveSheet
.AutoFilterMode = False
With Range("a1", Range("a" & Rows.Count).End(xlUp))
.AutoFilter 1, "*No. of Cases*", xlOr, ""
On Error Resume Next
.Offset(1).SpecialCells(12).EntireRow.Delete
End With
.AutoFilterMode = False
End With
End Sub
I would like that after the previous macro copies the names to column Z this macro could run straight after that. Even better if this macro could delete those names in column A. I'm thinking we would then have to make an array of things to... Read more

A:Solved: Macro to copy text from a cell to a column with specific criteria

Read other 8 answers
RELEVANCY SCORE 73.2

Hi everyone,
I am runing windows XP pro woth Office 2002 and Acrobat writer 5
I am trying to write a macro that will create a PDF on a specific Driver\folder\name.pdf

this is what I have :
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
"Acrobat distiller:", Collate:=True

newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 2
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime
Filename = "G:\Temp\Seth-quotes" '& ActiveSheet.Range("a13").Value
'SendKeys Filename & "{ENTER}", False

I print it on paper first and then I want to printer as a PDF and it kind of works but it gives me the screen where it ask me where to save the file.. so I know that the part on red doesn't work on the script.

I want to create two button that will create a pdf and save it on G:\folder1\filename.pdf
and the other button that same the file on G:\folder2\filename.pdf

Does anyone have any idea of how to do that.

thanks
AMD
 

A:Excel macro to create a PDF on a specific filename

Try This:

Button 1

Private Sub Button1_Click()

Dim ThisFile as Variant

ThisFile = "G:\Folder1\" & Replace(ActiveWorkbook.Name, ".xls", "") & ".pdf"

ActiveWorkbook.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:=ThisFile, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=False

End Sub

Button 2

Private Sub Button2_Click()

Dim ThisFile as Variant

ThisFile = "G:\Folder2\" & Replace(ActiveWorkbook.Name, ".xls", "") & ".pdf"

ActiveWorkbook.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:=ThisFile, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=False

End Sub
 

Read other 2 answers
RELEVANCY SCORE 73.2

Hello, I need to figure out a macro to select certain ranges of cells depending on what is in cel 'A1'.

I have a column of dates in column 'B' and two columns of numbers in 'H'. I want to be able to enter a date into cell 'A1' and have it find that date and then select all cells in column 'H' that correspond to all dates before and including the date entered in cell 'A1'.

eg.

A1 = 1/1/2010

B2 = 11/12/2009 H2 = 2.56
B3 = 15/12/2009 H3 = 30.99
B4 = 20/12/2009 H4 = 32.54
B5 = 25/12/2009 H5 = 5.65
B6 = 31/12/2009 H6 = 3.54
B7 = 1/1/2010 H7 = 6.87
B8 = 20/1/2010 H8 = 1.25

Since A1 = 1/1/2010 I want to select all cells from H2 to H7

I do not need this to be done automatically as I will push a button to activate the macro I just need the macro to select the correct cells when i push the button.

Can someone please help me figre this out I am a complete newbie when it comes to Macro coding, but I do understand the rest of Excel very well.

Thanks.
 

A:Excel Macro to select specific cells

Read other 7 answers
RELEVANCY SCORE 73.2

Good Evening,

I am trying to save a workbook using a macro, but I can not get it to save in a folder called Sugar_cookies. It just saves in the specified drive. Any idea what I do wrong?

ActiveWorkbook.SaveAs Filename:= "N:\Sugar_cookies" & DateDay & addinfo

Thank you,
codedreamer
 

A:How to save excel in a specific folder using a macro

Read other 6 answers
RELEVANCY SCORE 72.4

Hi guys,

I am trying to write a macro in excel to select a specific area. So far I have:
Code:


Sub Print_Checksheets()
Dim endrow As Long
Dim endcell As Long

Sheets("Horizontal Checklist").Select
endrow = WorksheetFunction.Max(12, Sheets("Horizontal Checklist").Cells(Rows.Count, "B").End(xlUp).Row)
endcell = Cells(endrow, "D")

Cells("endcell").Select

End Sub
I have a sheet that has writing all down column B and the first part of the macro locates the bottom row in column B that has writing in. What I am then looking to do is select the cell that is in the bottom-most row, but in column D.

This will ultimately lead to me selecting the area from cell A1 to the cell in question (lower most row and column D.)

I hope that this is clear and I hope that somebody could help me with this problem.

Cheers,

Mack
 

A:Solved: excel: macro to select specific area

Read other 7 answers
RELEVANCY SCORE 72.4

Hello,

I am working in Excel (Hyperion essbase) where I am retreiving data from a number of sheets. I want to exclude a specific sheet in my workbook by it's sheetname (and not by countposition as I know I can use in this specific case by putting the sheet for instance last). The sheet I want to exclude is named "TOT" Here is my code that I thought would work but it still includes sheet "TOT" when running the macro:

Month = InputBox("YTD month?")
For i = 1 To Worksheets.Count
If Sheets(i).Visible And TypeName(Sheets(i)) <> "TOT" Then
Sheets(i).Select
ActiveSheet.Range("B7:C7").Value = "Y-T-D(FY09 " & Month & ")"
Columns("A:C").Select
EssMenuVRetrieve
End If
Next

Thanks!

/Daniel
 

Read other answers
RELEVANCY SCORE 71.6

Hi

I have a following macro:
Code:
Sub BondPrices()

Selection.AutoFilter Field:=16, Criteria1:="BONDS"

Range("S2:S10000").Select
For Each cell In Selection
If Not IsEmpty(cell) And IsNumeric(cell.Value) Then
cell.Value = CDbl(cell.Value)
cell.Value = cell.Value / 100
End If
Next cell

Selection.NumberFormat = "0.00%"
Selection.AutoFilter
End Sub
The problem is that every cell in the sheet gets divided by 100, not only the filtered cells (only BONDS).

Any help would be greatly appreciated.

Michael
 

A:Solved: Excel: Macro to divide specific cells by 100 with a filter

I've solved the problem myself by adding a line to the code that selects only visible cells:

Code:
Selection.SpecialCells(xlCellTypeVisible).Select

 

Read other 1 answers
RELEVANCY SCORE 71.6

How do you make a macro that does the following:

go to next cell down in Column D
copy contents (a single word) of that cell
select entire Column A
find first instance, in Column A, of the word that was copied from Column D, above, (the word is part of a sentence in a single cell in Column A),
underline just that word (not the whole sentence) in Column A
loop

 

A:Excel 2007 macro - locate specific word & underline it

Hi Brad, welcome to the forum

I just recorded a amcro that does that and translated it to vba code
Code:

Function ULinetxt(cVal As String, uStr As String)
Dim tp1 As Integer, tp2 As Integer
If InStr(1, LCase(cVal), LCase(uStr)) > 0 Then
tp1 = InStr(1, cVal, uStr)
tp2 = Len(uStr)
With ActiveCell.Characters(Start:=tp1, Length:=tp2).Font
.Underline = xlUnderlineStyleSingle
End With
End If
End Function

Code:


Sub test()
Call ULinetxt(ActiveCell.Value, "underline")
End Sub

You could use this function in vba macro and it's done

Select a cell with your data string and rin the test macro, replace the word "underline" with the text you want underlined and the first occurrence (if found) will be underlined, case is not matched.

Let me know it it works for you.
 

Read other 1 answers