Tech Problem Aggregator

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.

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

1 more replies
Answer Match 85.2%

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!!
 

More replies
Answer Match 107.52%

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
 

3 more replies
Answer Match 105.42%

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?
 

1 more replies
Answer Match 104.58%

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

8 more replies
Answer Match 102.9%

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.
 

1 more replies
Answer Match 97.86%

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?
 

2 more replies
Answer Match 97.44%

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

16 more replies
Answer Match 95.76%

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

1 more replies
Answer Match 94.5%

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

7 more replies
Answer Match 92.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

6 more replies
Answer Match 91.98%

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.

16 more replies
Answer Match 89.46%

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

16 more replies
Answer Match 88.2%

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

7 more replies
Answer Match 86.94%

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.

16 more replies
Answer Match 85.26%

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

9 more replies
Answer Match 83.58%

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

More replies
Answer Match 82.74%

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

2 more replies
Answer Match 82.74%

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

15 more replies
Answer Match 82.74%

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

13 more replies
Answer Match 82.74%

I am very new to writing macros and I'm trying to find a macro that will search for a key word within the whole spreadsheet. If the keyword is found in say 7 of the rows, those rows will remain and all others will be hidden (except for the first 4 rows as these have headings, etc.).I would be very grateful to anyone who can point me in the right direction!message edited by Adrian82

A:Excel Macro - Keep only columns with key words in them

I do not have access to Excel or VBA at the present time, so the best I can offer at this point is to suggest that you look up the VBA Find method via Google. You'll find various options for the use of Find and FindNext. Those are the methods that I would use to loop through your sheet, searching for the key word.If I were to write code to do this, I would start by hiding all Rows (except for the rows with the headings) and then unhiding the rows where the key words were found. That way is easier than hiding rows which don't contain the keyword. Finding something with VBA and unhiding the row where it was found is much easier than not finding something and then hiding the row where it wasn't found.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

2 more replies
Answer Match 81.9%

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?
 

3 more replies
Answer Match 81.9%

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
 

1 more replies
Answer Match 81.9%

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
 

3 more replies
Answer Match 81.06%

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


 

1 more replies
Answer Match 81.06%

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

8 more replies
Answer Match 81.06%

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
 

1 more replies
Answer Match 81.06%

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

12 more replies
Answer Match 81.06%

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?
 

1 more replies
Answer Match 81.06%

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
 

1 more replies
Answer Match 80.22%

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

9 more replies
Answer Match 79.38%

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

16 more replies
Answer Match 79.38%

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
 

3 more replies
Answer Match 78.12%

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
 

1 more replies
Answer Match 77.7%

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

1 more replies
Answer Match 77.7%

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
 

3 more replies
Answer Match 77.7%

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

6 more replies
Answer Match 76.86%

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
 

1 more replies
Answer Match 76.86%

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

8 more replies
Answer Match 76.02%

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

7 more replies
Answer Match 76.02%

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
 

2 more replies
Answer Match 76.02%

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

6 more replies
Answer Match 75.18%

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
 

More replies
Answer Match 75.18%

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

7 more replies
Answer Match 74.76%

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

 

1 more replies
Answer Match 74.76%

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.
 

1 more replies
Answer Match 72.66%

Hi,

I am trying to classify and code my inventory items in excel.
My classification sheet contains data in columns which I would like to copy to rows in another excel sheet.
Here is the sample data in ACMOTORS-ATTRIBUTES sheet:

The above sheet has to be converted to columns as in Item_Classificatios_Form sheet :

Can anybody assist me in writing a macro for the same.

Thanks,
mihaufo
 

A:Solved: Excel Macro to convert Rows from one sheet to columns in another sheet

8 more replies
Answer Match 72.66%

Quick question, should be easy but I have been struggling with it.
What I am trying to do is, copy the data from Row 2 and copy it into J1, and continue down the list until there are not more records.

ie.

1 ab ab ab ab ab ab ab
2 ba ba ba ba ba ba ba ba

Then delete the 2nd row, and continue on down.

1 ab ab ab ab ab ab ab ba ba ba ba ba ba ba ba

and continue down the list until the last record.

I have attached a sample sheet.

Please help!
 

A:Excel - Copy/Paste Macro

6 more replies
Answer Match 72.66%

This is my first time using an Excel macro, so I'm a total novice. I created the macro below. It seems to work just fine, but just as it's done I get a Microsoft Visual Basic error that says:

"Run-time error '1004':
Application-defined or object-defined error"

My choices in error-window at that point are "End", "Debug" or "Help".

I need help to get the macro working without the error.

The spreadsheet contains a product number in column 1 followed by an unlimited number of colors in column 2. Each 2-character color is separated by a comma. The purpose of the macro is to copy the row as many times as needed (1 for each color) and append each color code from col 2 to the product number in col 1.

The macro follows:

Sub FixModel()

x = ActiveCell.Row
y = x + 1
cursorpos = 3
addrow = 0

Do While Cells(x, 1).Value <> " "

Do While Mid(Cells(x, 2).Value, cursorpos, 1) = ","
Rows.Insert
Rows(x).Copy Rows
Cells(y, 1).Value = Cells(y, 1).Value + Mid(Cells(y, 2).Value, cursorpos + 1, 2)
y = y + 1
cursorpos = cursorpos + 3
addrow = addrow + 1
Loop

Cells(x, 1).Value = Cells(x, 1).Value + Left(Cells(x, 2).Value, 2)

x = x + addrow + 1
y = x + 1
cursorpos = 3
addrow = 0

Loop

End Sub

Thank you for any help anyone can provide!
 

A:Excel Macro to Copy a Row in a Spreadsheet

9 more replies
Answer Match 72.66%

I have not coded vba macros fo many years. I am trying to create a macro that opens a second spreadsheet from the first. Copies a predefined range of cells from the second spreadsheet into the active cell of the first spreadsheet. The copy range in the second spreasheethas previously been highlighted. Here is the code:

Sub OpenSpreadsheet()
'
' OpenSpreadsheet Macro
'
' Keyboard Shortcut: Ctrl+m
'
' Dim mybook As Workbook.Name

Rows("14:14").Select
mybook = ActiveWorkbook.Name
Workbooks.Open Filename:="C:\Users\Peter\Desktop\Hiatt slide repair 2~ACTUAL.xls"
Selection.Copy
ActiveWorkbook.Close (False)
Windows(mybook).Activate
Selection.Paste

End Sub

I get an error message "obiect desn't support this property or method" for Selection.Paste. If I remove that line of code, I can then do a ctrl V after running the macro, and it will paste the data properly, so the problem is with the VBA paste statement. Please help.
 

A:Excel macro to copy from 1 spreadsheet into another

Probably you should use Selection.PasteSpecial instead
and I think there is something wrong with the order of things in your macro but that's becasue I don't see what your trying to do

Rows("14:14").Select
mybook = ActiveWorkbook.Name
Workbooks.Open Filename:="C:\Users\Peter\Desktop\Hiatt slide repair 2~ACTUAL.xls"

< here you are in the opened sheet but what selection????? >

Selection.Copy
ActiveWorkbook.Close (False)
Windows(mybook).Activate
Selection.Paste or Selction.PasteSpecial

But the problem lies in the red part
 

3 more replies
Answer Match 72.66%

I have an Excel 2007 doc a client has given me of their customers. But of course the data is not in proper columns, but it does follow the same format throughout the doc. (see attached)

I want a Macro, that will copy/paste the cells in the current format to a new worksheet with the "correct" format.

Name Address City/St/Zip Bday Name2 Phone
 

A:Excel Copy/Past Macro

I first added a column of numbers for each line and then just wrote some simple formulas to concatenate the data with a "-" between each item.
I then copied all that data and pasted (special, values) that data into a new worksheet. Text to Columns, using the "-" as a separator split the data into the groups (Name, Address, etc.) The valid data is listed every 8 lines, so I made a column of numbers that are 8 apart (3, 11, 19, etc.) and have Vlookup look for that value in the list that was copied over from the original page. A different formula looks for the different items (Name, Address, etc.) so they all appear in their own column.
Once you have done this, just Copy and Paste Special, Values the data over the Vlookups and you can delete the stuff you don't need.
You can run a macro while you do this, but, once it is done, when are you going to use it again?
 

1 more replies
Answer Match 72.66%

I am trying to come up with a macro that once executed will go to a defined name, insert a row, copy what is in row one and then paste the copy in the row that was just inserted.
 

A:Solved: excel macro copy

7 more replies
Answer Match 72.66%

I am looking to create a macro to copy cell contents in A1 and paste in cell A2 without losing A2's contents. Then move to B1 copy different contents, then A1 and paste in cell B2 without losing B2's contents. Every group has different content, I need this to continue for a specific row range...

Thanks!!!
 

A:Excel Macro: Copy/Paste

that's called concatenate.
You could record a macro that does that for one line so you can see what it does, then edit it for the range you require.

BTW welcome to the forum
 

1 more replies
Answer Match 72.66%

Is it possible to make a macro that copy predefined cells in a selected row into new row/cell ?
For example : I select row 4 push the 'macro button' and it copies A4, B4, C4, D4 and E5 to D21, D22, D23, D24 and D25.

If this is possible , could someone please write me an example to this and if not write and example how this could be done ?

In advance , thanks!

-risanger
 

A:Excel copy and paste macro

Hi, welcome to the forum.
In principle, everything is possible with a macro as long as the code is correct.
You could start by recording a macro yourself and follow the steps that you have explained above.
Once you're finished you stop the macro recording and take a look at the code.
This will show you more or less what it could look like, the only difference being that recoding a macro translates the coordinates to an offset R1C1 or similar instead of select Cells(2,3) (that is row 2 column 3) which is the same as Range("C2")

Try this out and see if you can work it our from there.
One important thing you should remember that you should always mention which version of Excel you're using,just a question of good habbit and complete information.

Just holler if you get stuck
 

1 more replies
Answer Match 72.66%

Hi Guys,

Would appreciate any help on this issue. I have uploaded an example spreadsheet which outlines the result I am going through. Sheet one will have a splash screen (which hasn't been set up yet other than a table) which will copy a searched result from sheet2 into the table on sheet1.

I have looked into bombs example from a few years back and recently mentioned on this forum but cannot adapt it to my needs. My experience is quite basic and I haven't done this for a few years. When using bombs previous example I managed to swap it around so that you can search sheet 2 and paste the results into sheet 1 but I had a few issues.

I was looking for a button where when a user hits it a windows pops up. The user would then type their search term and hit ok. The term they search for would search column G in sheet 2 and pull up all matching rows where the data is found. The only cells that need to be displayed are G, M and N for each respective row and these will be copied into the table on sheet 1. This is outlined in the test spreadsheet uploaded (but the length could be ignored).

As you can see from the test data sometimes there will be similar (Apple 1, Apple 2) and if a user searched for App or Apple all of the results would need to show. There is no limit to the amount of Apples in the table. The table is not of fixed length and will constantly be added to. Bomb's example would not show the duplicates - although this was intended for his Macro (I am unsu... Read more

A:Search and copy Macro in excel.

7 more replies
Answer Match 71.82%

Hello,

I have managed to write a macro that copies a table from excel into a new document in word, here is the code:

Sub controlword()

Dim appwd As Word.Application

Set appwd = CreateObject("word.application.9")

appwd.Visible = True

appwd.Documents.Add

Set wdApp = New Word.Application

Range("a1:j7").Copy

appwd.Selection.Paste

End Sub

I would like to make it so that the table copies into word NOT into a new document but into any open document at the location of the cursor...

Could anybody help me out with the code for this...

Thanks
 

A:Macro to copy a table from excel into word

You need to Set appwd to use the currently open Word Document not CreateObject.

Sub Controlword()

Dim Appwd As Object

Set Appwd = GetObject(, "Word.Application")

Set wdApp = Appwd.ActiveDocument

Range("a1:j7").Copy

Appwd.Selection.Paste

End Sub
I didn't test for the cursor location.
 

1 more replies
Answer Match 71.82%

I am very new to VBA and have a scenario that I could use some assistance with.

At work, I have 300 individual excel files, and I need to copy certain cells from each of those files and paste into a summary file. Since it is extremely time-consuming to do this manually, I was wondering if there was a macro that could open the file, refresh pivot tables (I have formulas in the 300 workbooks which are linked to pivot tables in the summary file), copy cells and paste values in the summary file (in the next available row, not to over-write previous data) and save workbook. Then repeat this process for the rest of the files in the folder. I would prefer not to have to write specific file names, as there are 300 of them and growing.

I am using Excel 2007.

I have attached the Summary file and 2 property files as examples. I need to make sure that the formulas in the Qtr1 & Qtr 2 workbooks (column D) are updated with the pivot tables in the Summary file. Then I need to copy cells B2727 from 'WS' worksheet and paste values to the next blank row in the range P2:R4 in the Summary file, and also copy cells B3131 and paste values to the next blank row in the range S2:U4 in the Summary file. Then save property file and close. Then open next file in the folder and repeat.

Please let me know if you need more info. I appreciate any help or feedback!
 

A:Help with Macro to copy & paste between Excel workbooks

Hi, welcome to the forum.
The Qtr1 & Qtr2 workbooks column D are unclear to me, but the B2727 and B3131 okay.

I put in a vba module with a macro and a funtion the macro Update will ask for confirmation of the Excel sheet and if you answre yes it updates the date.

Nothing with the pivot tables just the two rows into P-R and S-V

test and tell me if this is a beginning of what you need.
 

1 more replies
Answer Match 71.82%

Hello guys!
I have a problem, and although I thought the solution was simple, it turned out to be not at al.

I have an excelfile, it contains multiple worksheets, (approx 20)
All these worksheets have a different layout, so the columns have a different size.
When I make a report I have to copy every single worksheet in excel and then have to paste them one by one as a picture in word. This takes a lot of time ofcourse..

So I thought let's make a macro.
I started to paste all the data of the different worksheets into one worksheet and then I could use 1 simple paste and copy into word and voila..
But this didn't worked out because the columns have a different size, so some of the text became unreadible.

Then I made a macro which copies one worksheet and then puts it into a new document in word and saves it, that worked out, but I don't know how to write the code in order to make the macro, after it has copied the first worksheet and pasted it into word, to make it copy the second worksheet and paste it into word and so on for all the worksheets

This is the code to make it copy ONE single worksheet and paste it into word,
but could someone please help me with the code so that it also copies the second worksheet and pastes it into the same word file?
Code:
Sub proWord()
Dim varDoc As Object
Set varDoc = CreateObject("Word.Application")
varDoc.Visible = True
Sheets("Rapportgegevens").Range("... Read more

A:(Macro?) Copy Paste Excel to Word

9 more replies
Answer Match 71.82%

I need assistance with creating a macro. I need to create a lot of different reports but I want the macro to create them for me. Is there way to create a macro by: having a list of data, sorting it all by column A, then copying whatever is the same name in column A (and whatever is associate with column A- to the right) and inserting it into a new worksheet and then doing it for all the different data that is in column A. Then once it is put into the worksheet, to save it with whatever the name is from Column A in the first worksheet.

I figured that there needs to be a loop statement in the end so it will get all the grouped data together into seperate worksheets.

Thanks for your help. I have attached a spreahsheet to get a better idea of what I was looking for.
 

A:EXCEL MACRO- sort, copy different data

6 more replies
Answer Match 71.82%

In the "Checkout" tab, I am wanting to copy rows if/when a cell (column "B") in that row is greater than zero. I need this macro to go to the next row and the next row and the next row until the cell in column "B" is blank. Once the selection has been copied, I need it to pastespecial only the values and not fomulas in the "Database" tab.

I have attached an example spreadsheet.

Any and all help is greatly appreciated.
 

A:Excel Macro to copy row if cell is greater than zero

This is a duplicate of your other post
 

1 more replies
Answer Match 71.82%

I am trying to format a play list for a flash player. The first sheet is a list of songs from 1960. The 2nd sheet is a list of commercials/drops. After every 4 songs, I want to insert a drop from the drop worksheet. I need a macro that insert the row and paste the drop row. Once it has cycled through the drop list, it needs to start over from the first drop. The number of songs and drops vary per year.
The only thing I have found is a way of inserting the rows. I have no clue how to format the rest and hope someone does. It will save hours from having to do it manually.
Do While Not IsEmpty(ActiveCell)
ActiveCell.EntireRow.Insert
ActiveCell.Offset(5, 0).Select
Loop
 

A:Excel Macro Insert/Copy Paste

11 more replies
Answer Match 71.82%

Scenario:
Excel spreadsheet 1 with 'X' number of lines in it.

Goal:
Create a macro in sheet 2 that will copy a formula from line 2 (in this same spreadsheet) to line 'X'. I am simply doing a reformat in sheet 1 using some simple formulas so I can get this into a format to import.

Example:
First, determine number of rows in spreadsheet 1. I think this can be done with the counta function. I am trying to set a variable up that holds this value. This is what I have in the macro currently to do this:
Dim x
x = "=COUNTA(CSEXPORT!C:C)-1"

I am hoping that this set x = 10.

Second, I need to copy line 2 in spreadsheet 2 down 'x' rows.

I have tried relative positioning thinking that would work, but the macro still specifies specific cells. I thought I could use EndDown, EndUp to do what I needed, but it still puts in specific cell values.

The only thing that I need to know is how to use the variable in the VBA script. I don't know how to tell it to go to cell A(x). I have attached a spreadsheet with a rough example. The macro within this sheet is not completed.

Thanks
 

A:Excel Macro that will copy formula 'X' cells

6 more replies
Answer Match 71.82%

I'll try to be as descriptive as possible.

Current Status:
I have an excel file with two sheets: sheet1 and sheet2.
Column "A" will have lots of data.
Some rows in column "A" (or the first cell for that row) will have the word "Directory".

Intent:
I need to search sheet2, only in column "A", for the row that contains the word "Directory" and copy that whole row onto sheet1 in the same row. Continue doing that until it completes through all of the rows.

Example Data:

(Before)
sheet2
Row Column A Column B Column C
1 blah blah blah blah blah blah
2 blah blah blah blah blah blah
3 blah blah blah blah blah blah
4 Directory cool text huh?????
5 blah blah blah blah blah blah

sheet1
Row Column A
Row Column A Column B Column C
1 blah blah blah blah blah blah
2 blah blah blah blah blah blah
3 blah blah blah blah blah blah
4 blah blah blah blah blah blah
5 blah blah blah blah blah blah

*Action: Column "A" find "Directory" Copy Row 4 to sheet1

(After)
sheet1
Row Column A
1 blah blah blah blah blah blah
2 blah blah blah blah blah blah
3 blah blah blah blah blah blah
4 Directory cool text huh?????
5 blah blah blah blah blah blah
 

A:Simple Excel Macro to Copy Row from one Sheet to Another

"copy that whole row onto sheet1 in the same row"

Do you mean like this?

(make sure what you already have is "safe" before you try, obviously)

Sub test()
If ActiveSheet.Name <> "Sheet2" Then Exit Sub
LastRow = Range("A" & Rows.Count).End(xlUp).Row
For Each Cell In Range("A1:A" & LastRow)
If InStr("Directory", Cell.Value) <> 0 Then
x = Cell.Row
Rows(x).Copy
Sheets("Sheet1").Cells(x, 1).Insert Shift:=xlDown
End If
Next Cell
Application.CutCopyMode = False
End Sub
 

1 more replies
Answer Match 71.82%

Hi

I'm really new to writing code and I'm trying to develop some stuff in Excel 2007.

I want to record a macro for a command button that when clicked, will copy the data from Cell A6, and past it with formatting and everything into whatever cell I selected.

I can get it to copy and paste, but only into a certain cell.

Help required!

Thanks

C
 

A:Solved: Excel Copy and Paste Macro

MacShand, welcome to the forum.

Try This:
Code:

Range("A6").Copy
ActiveCell.PasteSpecial Paste:=xlPasteAll
Application.CutCopyMode = False

What the code does is it copies the contents in cell A6 to whatever cell you have selected at the present time. So, make sure before you run the code the correct cell is selected or you may copy over needed data.
 

3 more replies
Answer Match 71.82%

Hello,

I know nothing about programing an excel Macro but I need help createing a macro to do the following:

file 1
col a ffffcol b
1 ffffffffff48
2ffffffffff 26
3ffffffffff 53
4 ffffffffff8

File 2
col c fffffcol d
1
1
2
3
3
3
4

I want to fill the rows in col d in such a way that 1 coresdponds to 48 and so forth.

Also, the file has over 7000 rows of information so doing it by hand is not a viable option
 

A:Solved: Excel Copy paste macro

6 more replies
Answer Match 71.82%

Good morning. I consider myself a novice when it comes to Macros. I have written several simple macros using the macro recorder and VBA. My current dilemma is I have a template (worksheet #2) in which I wish to copy a range of rows several times within the same worksheet with a blank row between each copied range. The number of copies is dependent on the number of rows of data (cost centers)within worksheet #1.

I have seen some macro language that should accomplish the copy range, however, the next step is what throws me. For each copy of the template range, I'd like to insert the data (cost center) that comprises the rows on worksheet #1.

Essentially I want to create a copy of the template range for every cost center listed on worksheet #1. See the attached example.

Once the copies are made and the cost centers are inserted, my database will refresh the worksheet #2 and bring forth the data for each cost center. Macro language is not needed for the last step.

Thanks in advance for your assistance. Please let me know if the inquiry is not clear at any point.
 

A:Solved: Copy & Insert Macro - Excel

10 more replies
Answer Match 71.82%

This is my 1st time. I am trying to obtain the macro code for my spreadsheet. I want data to be copied from worksheet TASKS to worksheet COMPLETED based on data entered in a cell. Both worksheets have the exact same layout.Utilizing Rows A-H. I would like for all data in row to be deleted from TASKS tab and moved to the COMPLETED tab (under the same section) if Column F equals 100%. See example of layout below (have to use .... to seperate).Tab = TASKS (to be copied & deleted from)Task..................................Start Date.........Proj Compl.........Actual Compl..........%........Resource Req.............Notes Section = MEETINGS Name of Meeting..............02/08/10...............02/09/10.............02/10/10.............100%.......None.......................NoneTab = Completed (data to be moved to - under the same section) Section = MEETINGSAlso...If I need to insert rows in the future for new tasks, will the macro apply to these?Hopefully this makes sense.

More replies
Answer Match 71.82%

Hello,

I am totally a beginner with VBA so I hope you guys could help me!

I have a collection of data in excel in which I should search for a certain "name". Once the name is found, the macro should be able to select the entire row of the match and then copy to another sheet (could be in the same workbook).
I try to use the code described on this topic (already closed): http://forums.techguy.org/business-applications/703562-search-macro-excel-2003-a.html

However, my problem when using this code is that it is only copying the first "occuring" of the "name" and in a sheet I have, for example, 42 occurences for a name "example" and I want to copy all of them, not only the first.

So, could you help me please?
I am using winXP with excel 2003.

Thank you,
Agatha
 

A:Find, copy and paste macro in Excel

16 more replies
Answer Match 71.82%

Referencing cells: I have a spreadsheet with sales tax in D1, net amount in D2 and the total in C3 and the name in B3. What I am trying to do is copy and paste the contents of C3 to C1 & C2 and repeat for contents of C6 to C4 & C5 and so on until all rows are processed
 

A:Copy, Paste, Repeat macro in Excel

Sub test()
For Each Cell In Range("C1:C" & Range("C" & Rows.Count).End(xlUp).Row)
If Cell = "" Then
Cell.Value = Range("C" & Cell.End(xlDown).Row)
End If
Next Cell
End Sub
 

1 more replies
Answer Match 70.98%

Hello,I need a macro that searches key words in columns B,C and D from Sheet 1 and returns values from column A in sheet 2 in column A.It would be great if the input key words could be entered in a dialog box. The key words can be combinations of letters and numbers.Thank you in advance!

A:Excel Macro to Search for Key Words and copy results

This is quick and dirty, but should accomplish what you asked for. My guess is that there are more details that we will require before a complete solution can be offered.
Sub FindKeyWordBCD()
Dim myKey As String
Dim k As Range
Dim nxtRw As Long
'Get keyword from user
myKey = Application.InputBox("Please Enter Key Word")
'Search for keyword
With Sheets(1).Columns("B:D")
Set k = .Find(myKey, lookat:=xlWhole)
'If found, copy Sheet1.Column A value to next empty row in Sheet2.Column A
If Not k Is Nothing Then
nxtRw = Sheets(2).Cells(Rows.Count, 1).End(xlUp).Row + 1
Sheets(1).Cells(k.Row, 1).Copy Sheets(2).Cells(nxtRw, 1)
End If
End With
End SubClick Here Before Posting Data or VBA Code ---> How To Post Data or Code.

3 more replies
Answer Match 70.98%

Hi,

I'm looking to set up a macro to do the following:

Open up a workbook called Works Order Register
Find my Works Order on the register by matching the WOnumber (I8) and Contractor name (C1735) from Works Order workbook.
Once found copy various data e.g cell I1735, I1737 etc across to columns D, E etc on the register against the corresponding row found in the previous step.

I've got parts working ok, but not the whole thing together.

Thanks for help.
 

A:Excel Macro to lookup reference and copy data

12 more replies
Answer Match 70.98%

Hi,
I am trying to sort the colored rows, copy and paste it in particular format.
I have attached a sample excel file.
1. A cell has values which are Purple in color.
2. B cell has values, yellow, Blue and White in color.
3. I want concatenate A-Purple, B Yellow and paste it in next work sheet "Result"- Col2
4. Blue rows need to be copied and pasted to Col2
5. White row need to get pasted to Col4
6. Want to repeat this for whole worksheet.
Can you please suggest a macro for this?
Thanks,
 

A:Need excel Macro for copy paste colored rows

7 more replies
Answer Match 70.98%

Hi,

I would like to be able to input a number into a cell B3 on sheet 1 - 'Head Count'. Then get the macro to look up the number that was input and copy and paste all the rows relating to it from sheet 2 - 'Data' and paste them on 'Head Count' next to the input cell.

I am having trouble with getting the macro to copy and paste depending on the target cell. This is what i have so far:

Sheets("Data").Select
'Copy cells of Data from rows containing cost centre in col G of the active worksheet (source sheet) to cols
'of Head count (destination sheet)
Dim DestSheet As Worksheet
Set DestSheet = Worksheets("Head Count")

Dim sRow As Long 'row index on source worksheet
Dim dRow As Long 'row index on destination worksheet
Dim sCount As Long
sCount = 0
dRow = 1
For sRow = 1 To Range("G65536").End(xlUp).Row
'use pattern matching to find "Significant" anywhere in cell
If Cells(sRow, "G") Like "Worksheets("Head Count")Range("B3")" Then
sCount = sCount + 1
dRow = dRow + 1
'copy cols

' With Range("A14:AA14", Range("A65536").End(xlUp))
'.Paste

Cells(sRow, "A").Copy Destination:=DestSheet.Cells(dRow, "A")
Cells(sRow, "B").Copy Destination:=DestSheet.Cells(dRow, "B")
Cells(sRow, "C").Copy Destination:=DestSheet.Cells(dRow, "C")
Cells(sRow, "D").Copy Des... Read more

A:Excel macro copy and paste depending on a cell

Can you upload a workbook with dummy data so we can see how you've structured the data.
 

1 more replies
Answer Match 70.98%

Hi. I am trying to run a macro to copy items on 1 sheet and have it paste them on the 2nd and 3rd sheet. I am trying but can't quite seem to get the excel code to do what I want. I can't post a copy of it because I accidentally deleted it.

Can anyone help me?
 

A:Solved: Excel: Copy/Paste Macro Code Help

9 more replies
Answer Match 70.98%

Hi there,

I have to prepare several daily emails which are prepared using a range of data (for example B3122) from Excel 2003 and then have to be sent using Outlook 2003. Unfortunately, due to various management requirements I am unable to merely attach the Excel file as an attachment or paste the Excel data into the email as a straight paste - I have to paste as Unformatted text and then manually change various lines of the email to Bold and Underlined text.
I am trying to write a macro that will do this automatically but am unable to get the Excel data pasted into Outlook (as unformatted text or otherwise). I am assuming that for the font changes to bold I will have to use a Find function in the macro.

The code that I have got so far is as follows:

Range("B3122").Select
selection.Copy

Dim olApp As Object, olMail As Object
Set olApp = CreateObject("Outlook.Application")

Set olMail = olApp.CreateItem(0)
olMail.To = "[email protected]"
olMail.Subject = Range("B3")
olMail.Display

I am new to VBA and so I may be missing a really obvious solution but after trawling the net I have been unable to find an answer.

Many thanks to anyone that can help and make my work a lot less tedious!
 

More replies
Answer Match 70.98%

Hey all,

Here's my situation, any help is greatly appreciated:

I have a list of 13 items in one column. A couple of the cells will be hidden by the macro, but then what I need the macro to do is copy the first 10 unhidden items onto the clipboard.

What row(s) will be hidden will vary from week-to-week. If I were to simply have the macro copy a cell range, there would be a chance of it copying a hidden cell. I believe what I need is a way to have the macro do a count of how many unhidden cells it has selected, and stop when it reaches 10.

Forgive my ignorance but I am literally just picking up Visual Basic as of this week.

EDIT: Just to clarify, I have the hiding of the cells taken care of. I just need some help figuring how to copy the top 10 cells that aren't hidden.
EDIT 2: Found that I could delete the hidden cells. Worked perfectly.
 

More replies
Answer Match 70.98%

I need to create a macro in Excel that will look for a certain text within a column (Column A), Copy the data in another column (Column B), and paste the data in another column (Column C).

For Example....

Column A
ABC1
ABC1
ABC1
ABC1
ABC1
DEF2
DEF2
DEF2

Column B
1000
1500
78
562
235
89
456
258

So, I would like to copy all the data in Column B that correspond with "ABC1" in column A, and paste that data in Column C.
 

A:Solved: Excel Macro Search and Copy Rows

16 more replies
Answer Match 70.98%

Hi

This is very frustrating for me but probably very easy for someone with the know!

I use excel for accounts and enter the relevant details along a row; 1 row for each transaction. I want to be able to select specific cells from that row and paste them into an Excel invoice template i have.

I have used the macro record button to do this and it works fine except for one thing; it always copies the cells from the row i created the macro from. I would like it to copy the cells from the row i have selected. Hope this makes sense.

It obviously selects the cell (c4 etc) in the code but i want it to select based on the row of the cell selected if you know what i mean.

Here is the basic code i have
Sub CreateInvoice()
'
' CreateInvoice Macro
' Macro recorded 01/09/2007 by User
'
' Keyboard Shortcut: Ctrl+i
'
ChDir "X:\Accounting\Customer Accounts\Invoices"
Workbooks.Open Filename:= _
"X:\Accounting\Customer Accounts\Invoices\Blank Invoice.xls", UpdateLinks:=3
Windows("Accounts September 2007.xls").Activate
Range("B8").Select
Selection.Copy
Windows("Blank Invoice.xls").Activate
Range("B4").Select
ActiveSheet.Paste
Windows("Accounts September 2007.xls").Activate
Range("C8").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Blank Invoice.xls").Activate
Range("B2").Select
ActiveSheet.Paste
Application.CutCopyMode = False
With Selection
.H... Read more

A:Excel copy & paste Macro for selected cells

16 more replies
Answer Match 70.98%

Hello

I am currently working on an excel file where I want to collect some data from a sheet (sheet B) to another sheet (sheet A)

Please see doc attached in my second message right below.
example:
1. Item AB3005 is Cat. 1 starting prod in Jan 2011
2. Would like to go and get range of data in Sheet B that correspond to Cat 1.
3. Then would to paste this range of data to Sheet 1.
4. Range of data should be pasted so that data under N in Sheet 2 corresponds to data pasted under start date in sheet 1
Sheet A
Item Category Start Date May-10Jun-10Jul-10Aug-10Sep-10Oct-10Nov-10AB3005 Cat. 1 Aug-10AD3307 Cat. 2 Jul-10CD2520 Cat. 1 Sep-10

Sheet B
Categories -2 -1 N+1 +2 Cat. 1 B C C D D Cat. 2 B C C D E Cat. 3 A C C F F

Results from Example:
Item Category Start Date May-10Jun-10Jul-10Aug-10Sep-10Oct-10Nov-10AB3005 Cat. 1 Aug-10B C C D D AD3307 Cat. 2 Jul-10B C C D E CD2520 Cat. 1 Sep-10B C C D D

I tried few times already and have been able to put hte right range from sheet B in front of the right category in sheet A. But I have not been able to adjust the range so that they are aligned with the start dates.

Would you have any idea how to do that?

Thank you for the support

Bootoine
 

A:Excel MACRO - Copy Range depending on 2 different variables.

6 more replies
Answer Match 70.98%

Hi everyone!So I was wondering if there was a way to do the following. Saying I want to make monthly reports for a country (Italy) and two product categories (Juice & Water) but the worksheet containing the data is massive and I have to do that every week. The rows look something as following:Country - Product - OtherstuffWould there be a macro in the way "If Italy and Juice" cut paste whole row in a different workbook? Cheers!message edited by Odysseus

A:Excel Macro for conditional Copy/Paste for whole rows

Since you still haven't told me how you want to pass the country names to the macro (even though I've asked twice) I once again have make an assumption. This code will work for the criteria that you posted in Response #8 since I assume that you want the country names hard coded into the macro.The code creates an Array with the county names that you provided and then loops through the array searching for each element.(Note: This code still copies the rows to Sheet2 of the same workbook. I'll leave it up to you to modify it so that it copies the rows to your other workbook. Practice is good.)
Option Explicit
Sub CopyData()
Dim c As Range
Dim firstAddress As String
Dim nxtRw As Long
Dim cNum As Integer
Dim Country_Arr() As Variant

'Build array of Country names
Country_Arr = Array("Italy", "Slovakia", "Switzerland")

'Loop through Country names Array
For cNum = 0 To 2

'Search Sheet 1 Column A for array elements.
With Sheets(1).Columns(1)
Set c = .Find(Country_Arr(cNum), LookIn:=xlValues)

'If found, Check Sheet 1 Column B for Juice or Water
If Not c Is Nothing Then
firstAddress = c.Address
Do
If c.Offset(0, 1) = "Juice" Or _
c.Offset(0, 1) = "Water" Then

'If True, determine next empty Row in Sheet2, copy Row Values from Sheet1
nxtRw = Sheets(2).Range("A" & Rows.Count).End(xlUp).Row + 1
c.EntireRow.Copy
Sheets(2).Range("A" & nxtRw).PasteSpecial Paste:=xlValues
End If... Read more

14 more replies
Answer Match 70.98%

update on the below question:I think something is wrong when I opened the .csv file.If I leave the file open then run the macro, it works ok. If I open the file within the macro, I get the format problem.Any advice?Thanks!Hi,

Please can someone give me some advice on this?

I am writing a vba code in Excel 2003 to copy from a source .csv file and paste into xls file.

First, I tried this using macro recording, it worked fine. so I recorded the macro and tried to apply it. Something strange happened.

One of the columns is date, and originally is format Date (*01/01/01). If I manually copy and paste, the format is fine. If I use the macro, some (and only some) of the cells in this column will change format to General and align to the right, while the others keep the original Date format and align to the left.

How did this happen?

I viewed the .csv file using Notepad, and cannot see any difference between these dates that end up in different format.
And if I manually copy/paste, there will be no format issue at all.

Please can someone help?

Thanks!
 

A:copy/paste in excel macro changes cell format

upon a closer look, i think I found some clue, but still far from a solution, here is what I observed.

for date 09/07/2006, excel interpret it as Sept 07 06
for date 20/06/2012, excel interpret it as June 20 2012

please note the year format is different!

any advice pls?
 

1 more replies
Answer Match 70.56%

I’m hoping there is a formula within EXCEL that will allow me to complete this comparison. I’ve been working on it all day manually and realize it will take a long time to complete this way.
My computer is running on Windows 7 Home Premium and my EXCEL is 2010
I have two different files and within each are three columns of data that I need to compare. They’re labeled NC, NCI, and SECNCI
To be considered a “match” I need all three fields within a row to match exactly to a row within the other file. The first file called REQUEST NET FILE has over 37000 rows of data so naturally there will be many repeats of these same three fields in that file, but each row where the three fields match any row of data on the second file called DEPLOYED TABLE FILE needs to be the same to be considered a match.
If it doesn’t match, if possible, I’d also like to know why. This could fall into two different scenarios.
NC CODE IS NOT ON DEPLOYED TABLE FILE or
NCI/SECNCI combo not on deployed table for this NC
Once complete, I’ll run the same process in reverse so both files will end up with a match or no match next to each row.
If I need to run two separate compares, one to get the match, and then one on just the “no match” results to find the second bit of info, I can do that. Or if the second criteria can’t be found via excel formula, just the “match” “no match” result will be acceptable, as that will save me so much time.
I was thinking a vlookup could do the compare for me, but ... Read more

A:EXCEL 2 files compare data in 3 columns

7 more replies
Answer Match 70.56%

I have been trying to copy my document files using a cd and dvd with no success.
 

A:Copy document files to cd or dvd and display those files and retrieving a specific f

Perhaps try a good CD / DVD burning software package such as Nero.
 

2 more replies
Answer Match 70.56%

Hi, I am new here.

Have to create database based from data stored in xlsx file. Converting to CSV, and than importing to database would be an nice solutione, but the problem is a little bit difficult.
The text in the xlsx file is stored in 1 column, while there is 409 rows.

Looks like this:
I have to separate, and copy parts of the each row, based on specific characters.

Will explain on the folowing text(this is in column A)
(BAB) STOFFOVÁ, V. et. al. : Informatika, informa&#269;né technológie a*výpo&#269;tová technika : Terminologický a*výkladov slovník. 1. vyd. Nitra : Fakulta prírodných vied UKF v*Nitre, 2001. 230 s. ISBN 80-8050-450-4

1. '(BAB)' should go to Column B. So select text till first ')' character, and copy to B, in same row.
2. 'STOFFOVÁ, V. et. al.' goes to column C. spec. char. here is ':'
3. 'Informatika, informa&#269;né technológie a*výpo&#269;tová technika : Terminologický a*výkladov slovník.' This goes till first dot.
4. The rest of the text.

I am pretty sure in that there is some nice and smart solution for this, but I cant manage it.
Any help would be highly appreciated.

Thank You in advance!
red
 

A:Solved: Copy/paste text in excel based specific characters

16 more replies
Answer Match 70.14%

I have Windows XP-Pro SP2 installed on a P4 1.8GHz CPU with 768MB RAM

I would like a script that opens a commamd window and calls for a file (icon file) to be copied to a specific folder on 5 different drives. I would like to be able to either paste in the file path or drag and drop the file onto the command window. The icon file will always come from the same folder "C:\WINDOWS\system32\00 ICONS\System Folders" and be copied, sometimes replacing a file with the same name, to these 5 folders:

"W:\WINDOWS\system32\00 ICONS\System Folders"

"X:\0 aAPPS\00 a Install First\00 ICONS\System Folders"

"W:\WINDOWS\system32\00 ICONS\System Folders"

"N:\0 aAPPS\00 a Install First\00 ICONS\System Folders"

"D:\0 aAPPS\00 a Install First\00 ICONS\System Folders"

I apologize if I am using incorrect terms. I don't know if a batch file could do this or if it would have to be done with vbs. I would appreciate the help. I know nothing about writng vbs script and only a little about writing batch files.
Thank you.

A:Script to call for a file then copy file to specific folders

My question was answered on a different site.

Here is the link:

http://www.theeldergeek.com/forum/in...1&#entry280051

1 more replies
Answer Match 70.14%

i was hoping if anyone can provide me code for the macro i need . in my case i have to select the data manually from the source sheet. the selected data range needs to be copied from the active worksheet to the destination workbook. the data should go into specific columns in the worksheet of destination workbook. I have very limited knowledge in coding. If it can be done it would reduce the data entry time drastically.

A:Excel Macro to copy user selected data to new workbook

I assume it can be done, but not without a lot more detail.VBA code needs to know where the data is coming from and where it is going. If the user selects a range, that makes it easier, but the code would still need to know exactly where the data is going. Workbook name, Sheet name/number, the "specific columns" that you mentioned, etc.If you can supply that detail, we can see what we can do for you. If you are going to post any example data, please click on the following line and read the instructions on how to post data in this forum.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

13 more replies
Answer Match 70.14%

Hi there guys

Looking for help in excel 2007 for the macro part.

Basically in sheet1(BOM), we have configurations which our customer order to us (in the form of CP1,CP2..CPn). In Sheet2 (CP) details about CP are mentioned, basically each CP has list of items under it. Typical order from customer includes combination i.e. CP1&CP2 or could be CP2&CP3. What I want to do is based on the order in BOM sheet, need to search the details of CP's in sheet2 (CP) & copy the rows belong to that particular CP in the BOM sheet. So that I can make the BOM list for that particular order. Each CP has variable number of rows. Appreciate your help in making macro for this.

Rgds, MintC
 

A:Excel Macro to copy multiple rows based on selection

11 more replies
Answer Match 70.14%

I have two worksheets within the same workbook. WkSheet "Data" as my source and wksheet "Form" that I need to populate from the source.

I need to be able copy the quantity from my "Data" wksheet along column A, starting with A5 to my "From" wksheet in column A, starting with A9.

I need the quantity from the 'Data" wksheet to be pasted to the next open cell along the column A in my "Form" wksheet.

Can someone please help!!! Thanks.
 

More replies
Answer Match 70.14%

Hi,

I was wondering if anyone could help me. I need to extract data from multiple excel files into a new excel file and sum the values. The data comes from excel files that are made from a template so the data is always in the same place or same cells. For example, i have data saved daily in excel files with the following naming convention 080204, 080304, 080404,..... and so on. What i would like to do is have a macro that will create a week ending sheet by importing and summing the data from that week.

Thanks for reading!
 

A:excel macro for copying and pasting data from multiple excel files

thanks for all those who read!!!! let me know if anyone needs the code!
 

1 more replies
Answer Match 69.72%

Can there be a macro in excel to find column headers of the excel file in word docs and fill them in excel respectively for all the docx files in a folder. word data/file is not in table format and all docx files are similar

I tried recording the macro but it doesn't work
 

A:Solved: Fill excel Columns from word files

16 more replies
Answer Match 69.72%

I started out with the two previous posts about macros (post 1, post 2) that move rows into columns, but I'm having a little trouble getting it to work for what I need. First, I'm trying to do a data merge into Word, but in order to get the data merge to work/look the way I want it to, I need the XLS data to be in columns.

I'm attaching an example file that has one worksheet of the sample data (though my "real" data has about 300 rows) and the other worksheet is an example of the outcome I need.

Is there anyway I can do what I need? Exporting into another file or another worksheet within this file would be fine.

Thanks in advance.
 

A:XLS Macro to move data to columns, delete empty rows, delete duplicate columns

11 more replies