Over 1 million tech questions and answers.

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

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

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

RELEVANCY SCORE 200
Preferred Solution: Macro needed to copy x number of rows from one Excel file into a batch of Excel files

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: 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 127.6

Basically each morning I import data from an internet database to excel.
I need to divide the number of rows, that i receive daily in this excel document, to 4 worksheets designated to 4 staff members.
Each staff member needs to receive an equal amount of rows.

For e.g. if today i received 124 rows of data then each staff member should get 31 rows each.
Except the number of rows will vary on each day. I could get 200 plus rows of data.

Can someone please assist with writing a macro to divide up the rows in to the 4 worksheets? I would be very appreciative. Thanks in advance

I have attached the sample file for viewing....the data starts from row 3...thanks
 

A:Solved: Excel Macro to divide any number of rows equally onto certain number of works

Read other 11 answers
RELEVANCY SCORE 121.6

I am not sure if this is possible, but need to give it a shot. Hopefully someone understands what I mean!

I need to create a workbook with two sheets. On the first sheet are going to be options where someone (not myself) will need to input a number for a quantity of an item. On the second sheet will be rows of specifications for different items. I need to know if there is a formula that will take that number and copy/paste that many of the row that I link it to. I'm thinking I may need a third sheet? Number is input on sheet 1 and linked to sheet 2 where it copies a specific row and then pastes that many rows onto sheet 3?? The most important part of the problem is that it has to copy an entire row (or say 7 cells from a particular row) not just one cell.

Help!!
 

A:Excel formula to copy number of rows?

Read other 16 answers
RELEVANCY SCORE 120.8

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

Read other 16 answers
RELEVANCY SCORE 120.8

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

Read other 7 answers
RELEVANCY SCORE 119.6

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

Read other 11 answers
RELEVANCY SCORE 119.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 118.8

Hello, all. I'm looking to get assistance in creating a Macro to help format and move data in an Excel file: I have an Excel file with over 12,800+ rows and over 20+ columns, of data from about 192 companies (spanning ~16 years - 1994 - 2010 for each company).

Trouble is that there are several entries per year for each company.

What I want to do is to have every year, per company, be on one row: so I want to move the data for the same years, which come on different rows (in the data file), to the end of the row with the very first data for that year, which would make one long row (eg. if 5 rows of year 1999, make into 1 row of year 1999 by concatenation, and so on etc). And do this for each year for all the 192 companies.

Doing this manually is just inefficient, time consuming, and possibly prone to mistakes.

I've been told that a macro can do this, but as I'm a novice Excel user, don't know how to go about doing that. Even customer service & technical support at Microsoft weren't able to help me very much: they directed me here.

I have screen shots (of a simple spreadsheet I created) if anyone needs further clarification.

In the pictures, I only did the first company. But this is to happen for each year for all of the companies.

Please help. Thanks.
 

A:Help needed in trying to create a Macro? to cut, and concatenate rows in Excel

Read other 16 answers
RELEVANCY SCORE 117.6

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
 

Read other 1 answers
RELEVANCY SCORE 113.6

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.
 

Read other answers
RELEVANCY SCORE 108.8

I need to copy n number of rows from a sheet in my local machine to a sheet in fileserver.
My requirement is - if I select last 2 rows in the sheet in my local machine I need the rows to be copied after the first blank cell in the sheet in fileserver.
Also it would be helpful if the macro code is only in the local sheet and not in the fileserver sheet.

I am a newbie to macros pls help....
 

Read other answers
RELEVANCY SCORE 108.4

Hi There

It has been quite a while since I asked for help. I wonder if the following is doable as an Excel macro. The attached is only a demo with only 2 fields and a few rows of data. The real worksheet has more fields and around 3,000 rows.

Sheet 1 contains the data in its initial state and sheet 2 contains data after the macro is run. You will see from sheet 2 that the field labeled QUANTITY determines the number of rows to insert and copy the correct data into those inserted rows. I want the entire row to be copied down as opposed to only the data, since I have more columns than shown here. Is this possible to do? Can anyone help me with writing a macro, since I have over 3,000 rows to do.

Thanks for all your help. You guys are fantastic.

Mario
 

A:Inserting specific number of rows in Excel and copying data in those inserted rows

Read other 14 answers
RELEVANCY SCORE 105.2

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 103.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 102.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 101.2

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 100.8

How can I get the row numbers in an Excel worksheet to show and print?
 

A:Number Excel rows?

File > Page Setup > Sheet, put a check by Print-Row and Column Headings.
 

Read other 2 answers
RELEVANCY SCORE 100.8

I want my rows to automatically number beginning with #1, even though the data doesn't start until row 8. What formula would I use. (If I insert a row in the middle, would it keep the same numbering?)
 

A:Number Rows in Excel

sbudd said:

What formula would I use. (If I insert a row in the middle, would it keep the same numbering?)Click to expand...

=ROW()-7 (Yes)
 

Read other 2 answers
RELEVANCY SCORE 100.4

I am trying to come up with a code that will cut one or multiple rows and paste to a different sheet. The determination of what rows to cut is if it goes one day past the date in Column D.
Can anyone provide any guidance?
 

A:Excel Macro/Cut & Paste Rows

Hi, with the information you have given us (not much), a macro will do the job.
You could either make it event-driven or manual.
kevcott9 had a similra question but not with a condition in column D, but the idea would be the same, copy rows to different sheets.
This is event driven.
I've attached the file with the code I 'wrote' for him, maybe you can change it and use it too.
 

Read other 2 answers
RELEVANCY SCORE 100.4

Ok here is what I have.

I am working on a report that would identify duplicate rows on a spreadsheet and hightlight them.

As of right now I am useing:

Code:

=IF(AND(E3=E2,Q3=Q2),"Repeat","Unique"))

Then I highlight all the "Repeat" rows and resort them.

I was I have tried to come up with a macro and do not have much other then how to get the cells to highlight
Code:

With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With

I do know I am going to have to use an IF statement in the VB but not sure what comes next.

Thanx in advance.
 

A:Excel - Macro to hightlight dup rows.

Read other 9 answers
RELEVANCY SCORE 100.4

I'm not sure what this is called (possibly dynamic named range) so I will try to explain. I am trying to update a macro that was written by somebody else. In the macro he used row numbers. I have added rows within the referenced area and now the macro pulls information from the wrong rows. I want to change the macro to allow for more rows whenever I feel like we need to add them. The piece of the code that I want to update is below. If I change the row numbers, it works properly, but I need them to allow for movement. I'm not sure how the rest of the macro uses this piece, so if I need to post the rest of the macro or the actual document, please let me know.

'Project Level Documents
rowHeader = 1
rowPDDstr = 2
rowPDDend = 5
rowTOGPMstr = 6
rowTOGPMend = 8
rowRAstr = 9
rowRAend = 12
rowSOWstr = 13
rowSOWend = 13
rowTestStrategystr = 14
rowTestStrategyend = 14
rowImplementation_and_Transition_Strategystr = 15
rowImplementation_and_Transition_Strategyend = 15
rowIssue_Mgmtstr = 16
rowIssue_Mgmtend = 17
rowRisk_Mgmtstr = 18
rowRisk_Mgmtend = 21
rowChange_Mgmtstr = 22
rowChange_Mgmtend = 25
rowProject_Planstr = 26
rowProject_Planend = 26
rowProject_Statusstr = 27
rowProject_Statusend = 27
rowProject_Reviewstr = 28
rowProject_Reviewend = 29
 

A:Excel Macro to allow adding rows

Hi, Welcome to the forum.
This is something I tell every poster.
Please start by telling us which version of Excel you're using.
What you explain may be clear to you but try and envision somebody who has no idea of what you're intending to achieve.
I do suggest you attach a sample (copy) of you file with non-private data and add some informtaion there, somehting like showing a 'before' and 'after' so that one us can understand what you need.
 

Read other 3 answers
RELEVANCY SCORE 100.4

Hello

I am a total vba beginer and really need some help. I just can't get my head round the following. Any help would be so very much appreciated.

This is what I am trying to do-

I have a sheet with many columns of info. This info is basically a listing of the fruits that Shop A, B and C sell and the different prices and other stats. I want to copy across the rows of info for each shop, sort by fruit but exclude some of the stats.

example,

If column 2 equals shop A and column 12 equals pears, then copy the row of info into the new sheet but only the info in columns 2,5,8,12 and 18. I then want a blank row
Then I want if column 2 equals Shop A and column 12 equals apples, then copy the row of info into the new sheet but only in the info in columns 2,5,8,12 and 18.
Then I want a blank row
Then I want if column 2 equals Shop A and column 12 equals peaches, then copy the row of info into the new sheet but only in the info in columns 2,5,8,12 and 18.
Then a blank row and repeat process for Shop B and C

If Shop A/B/C has no apples/peaches/pears then nothing will be returned

After each shop's fruit has been listed I want to total up the prices (column 18 in the orginal sheet)

And then I want a grand total of Shop A+ shop B +Shop C as the last line.
I hope I am clear

Thanks so much in advance
 

A:Excel Macro that copies whole rows

Read other 7 answers
RELEVANCY SCORE 100.4

I have spreadsheet that has a bunch of parts listed in separate categories. Column A is the quantity column. I want to be able to hide all rows (using a macro) that have a zero in column A. The workbook has several sheets that need the same functionality. The macro must not hide rows with nothing in them or nothing in column A. For example a row may have content in other columns but nothing in column A because that row is not used for a specific part (therefore there will never be a quantity entered).

The code below works great except that it hides all rows with that are zero or blank. I just need to figure out how to make it skip the blank rows (column A has no data). Any ideas? It doesn't have to be with my code just something that provides this function for all sheets in the workbook. Thanks.

Option Explicit
Sub HideRowsWithZeros()
Dim ws As Worksheet
Dim c As Range
Dim rngRange As Range

Application.ScreenUpdating = False

For Each ws In ActiveWorkbook.Worksheets

ws.Select
Set rngRange = Range(Cells(1, 1), Cells(65336, 1).End(xlUp))

For Each c In rngRange
If c.Value = 0 Then
c.EntireRow.Hidden = True
End If
Next c

Next ws

Application.ScreenUpdating = True

End Sub
 

A:Excel Macro to hide rows

Read other 6 answers
RELEVANCY SCORE 100.4

I need some help in creating a macro in Excel 2010 for deleting rows that contain specific characters (the pound sign) in the text. I cannot get it to recognize the # as text. Also the # is embedded as part of a line of text which varies, so I also need a wildcard. Any help is greatly appreciated. thanks.
 

A:Excel macro for deleting rows

use instr("original text", "#")
 

Read other 1 answers
RELEVANCY SCORE 99.2

I am building an excel model and want to link a macro to the "calculate" button based on the above check boxes. If the boxes are selected, the relevant graphs would need to unhide and show and then hide as the boxes are deselected again.

I've attached a screen shot of what I'm working on.

I would appreciate any help possible on how to write the code for this macro.

Many thanks
 

A:Excel 07 - Macro to hide/unhide rows

Welcome to the board.

Screenshots saved as bitmaps are huge. Convert them to (e.g.) jpgs, then you can upload without zipping.

Re this part of your post specifically:

Macro to hide/unhide rows

, one "basic" answer is:

Private Sub CheckBox1_Click()
If CheckBox1 = True Then
Rows("11:20").EntireRow.Hidden = False
Else
Rows("11:20").EntireRow.Hidden = True
End If
End Sub

Do you actually have ten charts, or is it far more in reality?
 

Read other 2 answers
RELEVANCY SCORE 99.2

Hi All,

I have a spreadsheet containing 30 address blocks that I'm trying to clean up. A sample is attached.

All of the data is in column A. Each address block is 6 rows, like this:

Name
Company Name
Street Address
City/State/Zip
Phone number
Email Address

Between each 6 row block is a varying number of additional rows that might or might not contain extraneous information. I want to remove those extra rows leaving only the address blocks.

I'm thinking about counting the "x" number of rows between email address "@" symbols, and then just deleting X-6 rows. But I'm not sure how to accomplish this.

Any ideas how this might be accomplished, or any better suggestions to accomplish the goal?
 

A:Solved: Excel macro/vba to delete rows

"might or might not contain extraneous information"

For Each Cell In Range("A1", Range("A" & Rows.Count).End(xlUp))
If InStr(Cell, "@") <> 0 Then
Cells(Cell.Row - 5, 1).Resize(6, 1).Copy Range("B" & Rows.Count).End(xlUp).Offset(1)
End If
Next Cell

If those are real names and addresses, you should remove your file.
 

Read other 2 answers
RELEVANCY SCORE 99.2

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 99.2

Hello!

I wonder if someone could help me? I'm trying to combine multiple rows into a single row. My data currently is formatted:

E-mail,First Name,Last Name,Event,Ticket Name,Spaces
[email protected],John,Taylor,C,81,5
[email protected],Mary,Smith,A,81,2
[email protected],Mary,Smith,D,144,1
[email protected],Mary,Smith,I,81,1
[email protected],Mary,Smith,G,82,1
[email protected],John,Taylor,A,81,2

I'd like to be able to combine rows so that my data looks like this:

E-mail,First Name,Last Name,Event,Ticket Name,Spaces,Event,Ticket Name,Spaces,Event,Ticket Name,Spaces,Event,Ticket Name,Spaces
[email protected],John,Taylor,C,81,5,A,81,2
[email protected],Mary,Smith,A,81,2,D,144,1,I,81,1,G,82,1

I found a macro on an old techguy thread which just about does the job, only it keeps repeating the last name and doesn't reprint the header. I've tried to edit it but I know nothing of Visual Basic and can't figure out how it's working. Here's the code from that thread:
Code:
Sub test()
MainSheetRows = Range("B" & Rows.Count).End(xlUp).Row
For Each Cell In Range("B1:B" & MainSheetRows)
x = WorksheetFunction.CountIf(Range("New!B:B"), Cell)
If x = 0 Then
y = WorksheetFunction.CountA(Rows(Cell.Row))
Cell.Offset(, -1).Resize(, y).Copy Sheets("New").Range("A" & Rows.Count).End(xlUp).Offset(1)
Else
y = WorksheetFunction.CountA(Rows(Cell.Row)) - 2
z1 = ... Read more

Read other answers
RELEVANCY SCORE 98.4

This is what I want to achieve: I want to merge all excel files in a directory into one .csv file. The files in the directory are only *.xls, *.xlsx, and *.csv . I use a simple batch file that I put in the directory which has the following script:


Code:
echo off
copy /a *.* merged.csv
pause
And it works just fine but it merges the files alphabetically, which is not convenient for me. I want them to be merged in the order of their Date of creation or by Date modified. I've read this but I can't seem to make the script work for me. Please help me. Thank you very much in advance.

A:I need help with a batch file that merges excel files into one

Hi, Welcome to Seven forums.

Try this.

for /f "tokens=*" %i in ('dir /b /o:d /A:-D "C:\YourDir\" ') do type "C:\YourDir\%i">> merged.csv & echo.>> merged.csv

Read other 3 answers
RELEVANCY SCORE 98.4

Hi,

I have several completed templates that I need to summarize into a spreadsheet.

Is there a way for me to list down the files and the location of the excel spreadsheets and run a macro for copying the data I need from the files listed and pasting them into a spreadsheet format?

Here's what I am doing now, manually.

1. Open file1
2. Copy B2
3. Paste to SummaryFile on B1
4. Copy C2
5. Paste to SummaryFile on B2
6. Copy D2
7. Paste to SummaryFile on B3
8. Close file1

9. Open file2
10. Copy B2
11. Paste to SummaryFile on C1
12. Copy C2
13. Paste to SummaryFile on C2
14. Copy D2
15. Paste to SummaryFile on C3
16. Close file 2

I hope somebody can help.

Thanks,
Monica
 

A:Solved: Excel Macro needed for creating a spreadsheet from data on several but similar files.

Read other 7 answers
RELEVANCY SCORE 98.4

I am trying to find a way to find a specific text within a worksheet, select that cell and the 9 rows under that row, and delete the 10 rows.
 

A:Solved: Excel Macro -> Delete Multiple Rows

Read other 16 answers
RELEVANCY SCORE 98.4

If you check the file attached.

please can you help me in moving data with duplicate names to have the 2nd and 3rd email address moved to a new column and delete the row

need an excel macro to automate this as i am working with a large number of records like this in one excel sheet. i.e, i want to select the sheet and move data in every 2nd row to the columns in row above at the end of columns in that row.
 

A:Excel Macro to Move data in Rows to column

I'm unable to understand the entire requirement, but for moving the records with duplicate names, I've written few lines. This will cut the duplicate record rows (Consider "Pupil Forename" for removing)from the "Report Data" and will post into "Sheet3". Before running this macro please insert "Sheet3". Let me know your requirement, hope I will be able to resolve it out. Thanks!

Code:
Sub test()
Sheets("Report Data").Select
Dim trow As Long
trow = ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.Rows.Count).Row
For i = trow To 1 Step -1
If i = 1 Then
Exit Sub
End If
If Cells(i, 1).Value = Cells(i - 1, 1) Then
Rows(i).Select
Selection.Cut Destination:=Sheets("Sheet3").Range("A65536").End(xlUp)(2)
End If
Next i
End Sub
 

Read other 2 answers
RELEVANCY SCORE 98.4

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 98.4

I have a excel file like below.

Column1 |Column2|Column3|Column4|Column5|Column6|Column7
Row 1 EEEE 1 2 3
Row 2 4 5 6

I want to move data in row 2 (4,5,6) to Row 1 columns 5,6,7.

Could you help me with an excel macro to automate this as i am working with a large number of records like this in one excel sheet. i.e, i want to select the sheet and move data in every 2nd row to the columns in row above at the end of columns in that row.

your assistance is greatly appreciated.

thank you
 

A:Excel Macro to Move data in Rows to column

Read other 8 answers
RELEVANCY SCORE 98.4

Hi, I have approx 25,000 rows of data relating to parts held in inventory, on a spreadsheet in Excel 2003 SP2 .

One common cell on each line is the Supplier Stock Code and the data in it is duplicated on different rows as the same Supplier Part is held under several different Part ID's on our system, ie,

Cell A2 0-320311-00
Cell A3 0-320311-00
Cell A4 0-320311-00
Cell A5 0-330317-29
Cell A6 0-330317-29
Cell A7 0-330317-29
Cell A8 03400BK08345
Cell A9 03400BK08345
Cell A10 03400BK08345
Cell A11 03400BK08345

I have sorted the data into Supplier Stock Code order and need to find a way to automatically insert a blank row (or two) after each change, ie, in the example above it would be between rows 4 & 5, and 7 & 8.

Does anyone know of a Macro which will do this?

Many thanks,

MarG38
 

A:Solved: Excel Macro to automatically insert rows

First things first -- welcome to the board.

2nd thing:

"insert a blank row (or two) after each change"

Hmm: for what purpose? The "official" line is "DON'T do that, it's VERY bad design". K?

So what I did was drag your last entry down to r25000, to generate 03400BK08346,
03400BK08347, and so on. "Test data".

Then I ran the code at the bottom of this post. It ground away for 12 minutes (make coffee, visit bathroom, yada yada) & then fell over on account of "ran out of worksheet" (bear in mind I had c.25K unique values, you have far fewer).

Last (but most definitely not least) thing: back up your work before trying the code.

HTH

Sub test()
Application.ScreenUpdating = False
LastVal = Range("A" & Rows.Count).End(xlUp)

Do Until LV2 = 1
x = Range("A" & Rows.Count).End(xlUp).Row
x = Range("A" & x).End(xlUp).Row

If Cells(x, 1) = LastVal Then
LV2 = 1
Else
y = Application.Match(Cells(x, 1), Columns(1), 0)
z = WorksheetFunction.CountIf(Columns(1), Cells(x, 1))
Cells(y + z, 1).Resize(2).EntireRow.Insert
End If
Loop

Application.ScreenUpdating = True
End Sub
 

Read other 2 answers
RELEVANCY SCORE 98.4

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 &#8220;00/00/0000&#8221;.

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 98.4

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 98.4

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 98.4

Hi
I have set up the following macro to enable users of the spreadsheet to insert a row and copy the formating and formula's. For some reason, when I go to insert a new row again, it inserts the row in a completely random place and not in the exact same place. People may want to add 20 rows one after the other to populate and I am trying to enable them to do this without having to worry about the formulas in there (I want to protect the formula's to ensure they don't overwrite them). Can anyone help? This is my code from Excel 2007 (I recorded a macro, I am not competent and writing them from scratch):

Sub InsertRowStock()
'
' InsertRowStock Macro
' Insert a row into the stock sheet and copies all formats and formulas
'
' Keyboard Shortcut: Ctrl+i
'
ActiveCell.Offset(27, 0).Rows("1:1").EntireRow.Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
ActiveCell.Offset(-1, 0).Rows("1:1").EntireRow.Select
Selection.Copy
ActiveCell.Offset(1, 0).Rows("1:1").EntireRow.Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
End Sub
 

A:Macro to insert rows into Excel 2011 spreadsheet

Just one other thing, I think the rows it is inserting are every 27 rows down but I want a row always to be inserted in the one specific place on the worksheet.
 

Read other 1 answers
RELEVANCY SCORE 97.6

I know you can hide and unhide rows and columns but can you make it so they don't even exist?

I have a just under 2m picture I want to put as a background but when you put the file as a background it tiles it the whole 256 columns and 65,536 rows even if I have them hidden.

This makes my 2.5m spreadsheet over 32 megs in size even with the columns/rows hidden.

I need to be able to see the lines on this above the picture for this project and I can't find any way to do this. I thought I could put it in the background but this pesky problem turned up.

I tried adding more space to the jpg that was empty white space but this too also increased the file size.
 

A:Excel - Pictures and Limiting the number of rows and columns?

Read other 8 answers
RELEVANCY SCORE 97.6

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!
 

Read other 1 answers
RELEVANCY SCORE 97.2

I have a drop down list in D1 and what I need is a macro that will search for the selected value through column D and hide the rows that don't match. Think of it as like the autofilter function in excel.
 

A:Solved: Excel macro to hide rows if value does not match header

Read other 9 answers
RELEVANCY SCORE 97.2

What I need to do is insert 3 blank rowes in to a my excel sheet before bold text. im having to hand ball it at the moment and the pages are sometimes 18 pages long eg:
1900 - every time there is a bold heading I need to insert 3 blank rows befor the bold text. all the bold headings are in the same column.

Please help if you can, thanx all
 

A:Excel Macro to insert 3 blank rows b4 bold text?

Read other 8 answers
RELEVANCY SCORE 97.2

Hi,

I'm new to this, so any help would be appreciated.

My task is this - make checkboxes that would swoh only wanted rows and then copy any wanted rows to another sheet which has a specific design.

So I have 2 columns "Description" and "price".To each row I made 15 checkboxes with macro that should remove unwanted rows using this macro:

Sub CheckBox2_Click()
Rows("1:3").Select
If Selection.EntireRow.Hidden = False Then
Selection.EntireRow.Hidden = True
Else
Selection.EntireRow.Hidden = False
End If
Range("$A$1").Select
End Sub

And this actually works (to my surprise), but the thing is I want to make a button that would have macro which would copy only checkboxed (marked) rows to another worksheet. I tried to record macros, but that didn't work because it seems that I have to link all checkboxes to that button and only then I'll get a result. Now it copies all rows, but not the marked ones.

SO PLEASE HEEELP ME
 

A:macro excel 2007 that copies filtered rows to another sheet

Read other 13 answers
RELEVANCY SCORE 97.2

In Excel 2007, I have a spreadsheet with 14 columns. Twelve of the columns contain categories of data that correspond to the entries in the other two columns. I need to change the list so that each item/amount in each category occupies its own row, as follows:

Eg.

From this:

col1-----col2-----category1-----category2-----category3-----etc... up to 12 categories
aaa------bbb---------------------$55
ccc------ddd------$44--------------------------$66
To this:

col1------col2------category------amount
aaa-------bbb------2-----------------$55
ccc-------ddd------1-----------------$44
ccc-------ddd------3-----------------$66
I know how to do this manually, but it needs to be done frequently on many different sheets. So, I need a way to do it automatically.

Many thanks for your help
jeannie
 

A:Excel macro - change column data to multiple rows

I have your data mapping from A:N to P:S, with no headers. In case that's wrong, make sure your work's saved before trying this.

Sub test()
LastRow = Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To LastRow

For Each Cell In Range("C" & i & ":N" & i)
If Cell <> "" Then
DestRow = WorksheetFunction.CountA(Columns(16)) + 1
Cells(DestRow, 16).Resize(, 2).Value = Cells(i, 1).Resize(, 2).Value
Cells(DestRow, 18) = Cell.Column - 2
Cells(DestRow, 19) = Cell.Value
End If
Next Cell

Next i
End Sub

HTH (welcome to the board)
 

Read other 2 answers
RELEVANCY SCORE 97.2

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