Over 1 million tech questions and answers.

excel macro for copying and pasting data from multiple excel files

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

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!

RELEVANCY SCORE 200
Preferred Solution: excel macro for copying and pasting data from multiple 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: 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 121.6

I have a spreadsheet that contains formulas. I want a macro that will copy the data only in that cell with the formula and paste only the data in another cell.

I have been scratching my head for a little bit on this one.

Thanks
 

A:Excel - copying data and pasting data in a formula cell

If I understand correctly, you can actually avoid using copy altogether.

Let's say you have the value 50 in A1 and the formula =A1*2 in A2. The following macro would copy A2 to D2:

Sub Macro1()
Selection.Copy
Range("D2").Select
ActiveSheet.Paste
End Sub

but because the formula uses relative references, the resultant formula (in D2) would be =D1*2.

So what you could do instead -- using a single command only -- is:

Range("D2").Value = Range("A2").Value

HTH,
bomb
 

Read other 3 answers
RELEVANCY SCORE 108

Hi All,

Just came across a wierd problem, part of my job consists of copying data from our reports website. This normally a very simple processs, just cut and paste into excel(2003). But recently I've had a new section added to the reports website which format wise is exactly the same as previous ones. The problem is when I go to copy and paste into excel, it copies all of the data into one cell, instead of spreading it across the appropriate number of rows and columns.

I know this sound confusing but anyhelp would be greatly appreciated.

Due to the sensitve nature of the data I cannot re-create an example.
 

A:Excel 2003 - Pasting Website data into excel

is this info in a grid/table on the website?
 

Read other 2 answers
RELEVANCY SCORE 105.2

I need an Excel macro to print a set number of rows from a spreadsheet based on user input. I have attached a sample input file. Each ten rows represents one label. For each 10 rows of data, I want the macro to prompt the user and ask: "How many labels for 5/4X6X12' GROOVED (the description field for each set of data) do you need? So, even though this item shows a quantity of 16, the user may only need to print 2 sets of this data. If the user answers "2", then I need to print 2 pages each having the 10 rows of data that goes with that description:

Type: Delivery
PO #: 38505 SO #: 121046
Ship to: WORK SITE - LOT 3
Address: LOT 3 DALE SUBD
Item: ZZAT0000030621
Desc: 5/4X6X12' GROOVED
QTY: 16 EA
Created by: Rsmith
Ship Date: 11-SEP-14

Any help on this macro would be greatly appreciated. Thank you.
 

Read other answers
RELEVANCY SCORE 105.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 102.8

Hi - I have an excel sheet that has several columns, including a quantity field. To make this data compatable with other spreadsheets, I need to expand the data out so that instead of a field that just reads "quantity 4" I would actually have 4 separate rows that are carbon copies of the original row (the one that says quantity 4). I've never used macros before and thought that it might be possible with a funky lookup function, but I'm not sure. Any help at all would be much appreciated.

Here's how the data is currently:
This is what I'm looking for:
There's just over 4,000 records so doing this manually would be a big pain.

Thanks!
 

A:Excel: automating Copying and Pasting

Read other 7 answers
RELEVANCY SCORE 102.8

Anyone know why MS Excel stops working and need to restart when I try to copy and paste?

Win 7 / Office 2003
 

A:Excel stops when copying & pasting

It just crashed in Word

Problem signature:
Problem Event Name: APPCRASH
Application Name: WINWORD.EXE
Application Version: 11.0.8324.0
Application Timestamp: 4bc9382e
Fault Module Name: unknown
Fault Module Version: 0.0.0.0
Fault Module Timestamp: 00000000
Exception Code: c0000005
Exception Offset: 00000000
OS Version: (Removed by me. Not sure if this was a security concern)
Locale ID: 1033

Additional information about the problem:
LCID: 1033
Brand: Office11Crash
skulcid: 1033
 

Read other 1 answers
RELEVANCY SCORE 102.8

When I use Ctrl + C, or right click to copy within an Excel doc, it crashes. It does this almost 100% of the time

A:Excel stops when copying & pasting

Try a reinstall. Uninstall, then install again. If recent install use system restore to remove it.

Read other 1 answers
RELEVANCY SCORE 102.8

How would I go about writing a macro that would allow me to to go through 10-12 XL files in a directory and copy the same range from all of them and to a new workbook or sheet for that matter. I'm not gonna be picky here cause it's just giving me a headache.
 

A:Copying the same range from multiple excel files

Can probably get you a solution, iz. But you DO need to be specific.

Here's what will help:

1. Sample range to copy.
2. Sample folder name in which files reside.
3. Exactly what you would want to paste them to: one sheet in a new Excel file, 12 sheets in a new Excel file, etc.
 

Read other 2 answers
RELEVANCY SCORE 102

Hello again,

I have a word doc that has a form in it. The form may have any number of fields (boxes) to type text answers into, or be a drop down list to select an answer from. I need an excel macro that extracts all data from the form, but with every five fields starting a new row in excel. So all data from the form will appear within 5 columns in an excel spreadsheet.

field 1 field 2 field 3 field 4 field 5
field 6 field 7 field 8 field 9 field 10
etc

Is that possible? I have had no luck to do it myself.

Sample form attached, thank you
____________________________
 

A:Solved: Excel macro to extract Word form data into Excel

Read other 7 answers
RELEVANCY SCORE 102

Hi there

Here is a simple question but I don't know the answer. Say I create a drop down list in Book1, cell A1, sheet1 using validation. The list is in cells A1, A2, A3, A4 in Sheet2. The names in the list are Winter, Spring, Summer, Fall. The name of the list is "Seasons" in sheet2.

Now I copy and paste the drop down list from Book1, cell A1, Sheet1 to Book2, Cell A1, Sheet1.

I now notice that the drop down list in book2 is working, even though there is no list of the name of the seasons in Book2. Why is that??

I also noticed that if I change the file name of book1 to a different file name and then copy and paste the drop down list to Book2, sheet1, cell A1, I get a message saying if I want to keep the same name or change the name of my list. I find this very confusing. Can someone expalin to me what is going on??

Mario
 

A:Excel - Copying and pasting a dropdown list

When you copy, a reference to the list in Book1 is automatically created, e.g.:

(Name) List -- (Refers to =[Book1]Sheet2!$A$1:$A$4.

See CTRL+F3 to check.
 

Read other 3 answers
RELEVANCY SCORE 102

Good morning,

My apologies in advance for posting a question that has been answered many times here and on the interwebs already, but the answers I find are mostly the same and I need further clarification, if possible.

I work at as a tech at a law firm and I'm having an issue where 2 users are using a shared excel workbook to copy and paste data into from miscellaneous client websites, such as client names, loan numbers, etc.

Every once and a while, when one of the users is done making their changes and saves their work, their workbook size will jump anywhere between 20MB and 250MB in size from the original, which is usually in the lower KB range. All answers I've found include copying and pasting the content into a new spreadsheet, recreating the document, finding the last used cell with Ctrl+End and clearing the blank spaces, and removing text styles like boldness and colors. All these solutions do work, but it's hard to keep telling an attorney to do these things without them biting your head off about it - because this does happen multiple times a day on this one spreadsheet. It is, in fact, due to blank cells, as the last active cell usually hits the 1,000,000 number range when the file size jumps up.

I heard there's away to lock a spreadsheet to a predetermined cell count, but I'm still researching this method. If anyone can provide me further explanation on why this happens and how to prevent it, please do. Thanks in advance.
 

A:Large Excel File: Copying & Pasting

Read other 7 answers
RELEVANCY SCORE 102

Hello

I'm hoping there is a saviour out there for me.
I'd be grateful for any suggestion or advice.

I have two identical laptops (IBM Thinkpad T22). They are identical in build. They both have Windows 2000 and Office 2000 installed on them.

I have an Excel spreadsheet and Powerpoint presentation.

I highlight data from cells A1 to H15 in excel and click edit>copy.
On a new slide in Powerpoint I click edit>paste special>microsoft excel worksheet object.

On the one laptop it pastes all the data in perfectly. On the other laptop only cells A1 to C15 are pasted in.

I have attached a screen dump from both pc's so you can better understand what I mean.

The only thing I have a hunch on is the settings. On both pc's, on the pasted data, I right click and select "format object". On the size tab I noticed that on the "good pc" the units are in cm but on the "bad pc" its on Inches ("). I changed the regional settings on the "bad pc" to English UK (Im in the UK!) and this changed the settings to cm's. I tried changing the size of the object on the "bad pc" to the same size of the object on the "good pc" but it didnt work. The settings dont hold. I change them but when I go back in they are back to where they were before.

Can anyone help me?
I am baffled how two identical pc's have this difference.
I have compared all settings (Tools>options etc) and they are all the same.
... Read more

A:Problems copying and pasting from Excel into Powerpoint

Well, you won't believe it, no one does.
Print settings have TONS to do with it.
Make sure the same printer and driver are selected as the default on both PCs...

Better yet, don't use the method you're using.
Select the cells in Excel.
Hit the View % dropdown and choose Selected area.
Save the file.
Now, go to PPT.
Insert-Object from file. Choose the Excel file.
I HOPE you'll get better results, but I don't swear to nuthin when it comes to copying/pasteing from excel
Also, pasting special as a picture often brings more desirable results, but won't be updateable.
 

Read other 3 answers
RELEVANCY SCORE 101.6

I'm working on a Bill of Material creation automation project that requires some expertise in VBA, and I have none.
The objective is to run a macro from an excel spreadsheet called "PGE BOM", to do the following:
1) Go to the folder "C:\Documents and Settings\Desktop\Auto Project"
2) Find all the .mdb databases in this folder
3) Find "HistoricalMaterialItemsAll" table in EACH of those databases in step 2, and import the data from the columns listed below into PGE BOM.xls's columns C through G:
DrawingNumber
ItemNumber
Quantity
PgeCode
Description
The following is a VBA code that my friend had written in Excel 2007. Unfortunately I have an older version (2000) and the code does not seem to be compatible with Excel 2000.
Sub ImportAccessData()
Stop
dPath = "C:\Documents and Settings\Desktop\Auto Project\"
sFile = "*.MDB"
strSrch = dPath & sFile
Set TargetWB = Application.ActiveWorkbook
Set TargetWS = TargetWB.ActiveSheet
sRow = 2
bFile = False
If Dir(strSrch) <> "" Then
strFlNm = Dir(strSrch)
bFile = True
End If
Do Until bFile = False
strPath = dPath & strFlNm
Call GetData(strPath)
strFlNm = Dir
If strFlNm = "" Then bFile = False
Loop
End Sub
Sub GetData(fl)
Stop
strSQL = "Select HistoricalMaterialItemsAll.* From HistoricalMaterialItemsAll"
Workbooks.OpenDatabase fl, strSQL, xlCmdTable
Set WB = Application.ActiveWorkbook
Set WS = Applicatio... Read more

A:Excel Macro - VBA code to import access data to excel

Hello, and welcome to the board!

When posting code, please use CODE tags, which extremely helps with readability.

Perhaps you could try using the code constant???...
Code:

Option Explicit

Sub ImportAccessData()
Dim dPath As String, sFile As String, strSrch As String
Dim TargetWB As Workbook, TargetWS As Worksheet
Dim sRow As Long, bFile As Boolean, strFlNm As String, strPath As String
dPath = "C:\Documents and Settings\Desktop\Auto Project\"
sFile = "*.MDB"
strSrch = dPath & sFile
sRow = 2
bFile = False
Set TargetWB = Application.ActiveWorkbook
Set TargetWS = TargetWB.ActiveSheet
If Dir(strSrch) <> "" Then
strFlNm = Dir(strSrch)
bFile = True
End If
Do Until bFile = False
strPath = dPath & strFlNm
Call GetData(strPath, TargetWB, TargetWS)
strFlNm = Dir()
If strFlNm = "" Then bFile = False
Loop
End Sub

Sub GetData(fl As String, WB As Workbook, ws As Worksheet)
Dim strSQL As String, iRow As Long, sRow As Long
strSQL = "SELECT HistoricalMaterialItemsAll.* FROM HistoricalMaterialItemsAll"
Workbooks.OpenDatabase fl, strSQL, 3 'xlCmdTable
iRow = 2
Do Until ws.Cells(iRow, 1) = ""
ws.Cells(sRow, 7) = ws.Cells(iRow, 5) 'Get the Description
iRow = iRow + 1
sRow = sRow + 1
Loop
Application.DisplayAlerts = False
WB.... Read more

Read other 1 answers
RELEVANCY SCORE 101.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 100.8

Hi there

I have downloaded a form from Revenue Canada named gst190-fill-11e.pdf. The PDF form is fillable. To make a long story short, I have recreated the form in Excel 2000 and created an Excel program that automatically populates the cells. The problem is that Revenue Canada will not accept my Excel form and demands that I use their PDF fillable form. I'm tired of copying and pasting data from my Excel form to their gst190-fill-11e.pdf form, since I have hundreds of these to do. It is very time consuming. I tried to record a macro by copying and pasting from my Excel form onto their form but my macro looks something like this.

Range("A11").Select
Selection.Copy
Range("E11").Select
Application.CutCopyMode = False
Selection.Copy
Range("A16").Select
Application.CutCopyMode = False
Selection.Copy
Range("A18").Select
Application.CutCopyMode = False
Selection.Copy
Range("C18").Select
Application.CutCopyMode = False
Selection.Copy
Range("E18").Select
Application.CutCopyMode = False
Selection.Copy
Application.CutCopyMode = False

In other words, the name of the form did not appear in this recorded macro. Is there a way to create a macro that copies and pastes cells from Excel into a fillable PDF file??

If anyone out there has any idea how to do this, I would greatly appreciate it. If you want to look at the form just google it.

Mario
 

Read other answers
RELEVANCY SCORE 100

I'm trying to create a macro that will sort on numerous fields. The macro will be stored in my "personal" file as I need to use it in a new report every month. I think I know what the probelm is. It looks like it hard coded the name of the worksheet where I originally created it. I need the highlighted piece to be flexible depending on whatever file is open.

Sample portion of macro starting at the beginning...

ActiveWorkbook.Worksheets("PWQ QA Report v.05").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("PWQ QA Report v.05").Sort.SortFields.Add Key:= _
Range("O2:O15173"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
:=xlSortNormal
ActiveWorkbook.Worksheets("PWQ QA Report v.05").Sort.SortFields.Add Key:= _
Range("D215173"), SortOn:=xlSortOnValues, Order:=xlDescending, _
DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("PWQ QA Report v.05").Sort.SortFields.Add Key:= _
Range("E2:E15173"), SortOn:=xlSortOnValues, Order:=xlDescending, _
DataOption:=xlSortNormal
 

A:Solved: Sort Macro for multiple files- Excel 2010

Read other 6 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 97.6

Hello,

I need help to create an Excel macro that would

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

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

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

Looking forward to your help!

Thanks a lot.
Mzz
 

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

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

Read other 1 answers
RELEVANCY SCORE 97.2

Hi,
I want to merge two excel files into a 3rd one.

The data are in the files Book1, Book2 and the result in Book3.

I know that i can use VLookup to do the above. But what i have pasted is just an example. The real excel files contains about 9 columns with at many repeating row!!!

Any help will be much appreciated.
Thx
 

A:Excel - macro to merge 2 excel files using a common column

Hi welcome to the board. How are you going to treat the duble items, If you want to merge 2 files adn duplication occurs here must be one cell whihc is common to let's say Book1 and Book2.

Do you have a common value? Index? Whatever you call it?
 

Read other 3 answers
RELEVANCY SCORE 96.4

I have about 100 (maybe more) excel files that are "invoices". Each file has the exact same row and column headings, but different data.

I would like to put certain cells from each of these files into one excel file. I started by using the external reference command to get the data from the first file, and it worked fine.

I made a list of the filenamens in a column, and attempted to copy the "command" to use the filename in the first column for each row, therefore pulling identical cells from all the files. When I change the command, Excel then wants to format it as text.

Is there a simple way to do this?

Ken
 

A:Accessing data from multiple excel files

Read other 6 answers
RELEVANCY SCORE 95.6

Hello.

I have a very basic knowledge of Excel and I was wondering how I can act out this simulation:

I would like to have a single XLS document (which I assume would use Pivot) where I extract data from multiple XLS documents (some of which contain multiple multiple work sheets as well). The main purpose is so that I can have a drop-down box that lists every person's name, and the documents they can be found in.

For example, if I select the name "Jim" in the box, it would show the documents that his information is located in, such as "Registrations1.xls" and "Registration2.xls". The document names are posted on the same cell in every file, just as the data is listed in the same columns.

Thank you in advance!
 

A:Excel Help (extracting and ordering data from multiple files)

Any help is appreciated. If possible, could someone perhaps tell me how I could approach this situation? What should I read up on, and where can I find tutorials that can help me with this problem?
 

Read other 2 answers
RELEVANCY SCORE 95.2

Microsoft Office 2003

I have data in cells A1 to C3. I do a multi-select so that I only pick cells A2, A3, C2 and C3. When I paste it into Word, it pastes all the data in between as well. Is there any way to get it to paste only the multi-selected data?

Thanks,
Todd
 

A:Pasting multiple selections from Excel into Word

Read other 7 answers
RELEVANCY SCORE 94.4

I need help to create a macro that will do the following:

I have several rows in a worksheet, sorted by a column titled "Status". "Status" can either be open or closed. For each row, if "Status" is closed, I need to have the entire row cut and moved (pasted) to a separate worksheet starting on the next available row.

So, first the macro needs to recognize the rows that that have Status = closed. Then it needs to cut the entire row, find the next available or empty row in the next worksheet, and pasted the row there.

Can somebody help me? Thank you so much.
 

A:Excel MACRO Help: Copying row into a different worksheet

Read other 7 answers
RELEVANCY SCORE 92.4

I have a spreadsheet containing a list of Invoices to print. As part of my Macro I need to be able to copy a row of data from the list (columns a to k), however the row will be different every time and is chosen by the user.

So I was looking at putting in a prompt to ask the row number to copy and go from there

Any help welcome.
 

A:Solved: Excel Macro help - copying user defined row

welcome to the board. Dou you already have a macro that does anything special?
all you need is define a variable and an input box, then if the nr is a valid row exceute the copy command

e.a.g
Code:

Sub Sample()
Dim xrow As Integer
xrow = Application.InputBox("enter row nr.", "ROW", 2, , , , , 1)
If cell(xrow, 1).Value = "" Then Exit Sub
Range("A" & xrow & ":K" & xrow).Copy

' and here come the rest of the code to paste or whatever the macro has to do

End Sub


See if this helps you on the way
 

Read other 3 answers
RELEVANCY SCORE 92.4

Hi all,
Basically what I have is two sheets in the same workbook in Excel.
In the first one, I have numbers in the range C16:C74, with a description belonging to each number in D16 : D74.
What I want in the second sheet is the number of C16 (sheet1) and the description of D16 (sheet1) to be copied in cells D6 and D7 of sheet 2 respectively.
This shouldn't be so hard, but then I'd like C17 and D17 of sheet 1 to be copied into cells D18 and D19 of sheet 2 respectively. And so on and so forth for the whole range of C16 : D74.
Would this be possible in a macro? I've tried recording using relative references, but to no avail. What basically is the problem is that the offset differs per sheet. The offset in terms of rows in sheet 1 is 1 (C16 needs to be copied, then C17 etc.), whereas the offset in terms of rows in sheet 2 is 12 (the value from sheet 1 needs to be copied to D6, the next to D18, the next to D30 etc.).
Many thanks in advance!
 

A:Solved: Excel - Macro, copying with varying offset

Hi, welcome to the forum. I suggest you attach sample files with dummy data explaining what you have and what you require.
I will make it easier to understand.
It is also advisable to mention which Excel version you're using.
 

Read other 10 answers
RELEVANCY SCORE 92.4

First time poster here,

I need help writing a macro which I will link to a button. Here is what I'm looking for:

I have 4 columns of data starting in rown B29 and ending in G29 (one of the columns is actually 3 merged columns which is populated by a dropdown list). 2 of the columns have formulas.

I would like the user to click the button, which will add a blank row with all the formatting from above (including the same drop down list and formulas). However, I would like to be able to add multiple lines and have the new line always appear at the end of the list.

As if that weren't complicated enough. I want to add another button to act as a 'reset' button which will delete the added rows, leaving me with the original blank row.

I tried recording my own macro but that doesn't work because the merged cells un-merge as the new row is added. And I can't get the new row to always appear at the bottom if I add more than one.

Is that enough info?

Brian
 

A:Excel Macro to Add Line while Copying Formats/Formulas

Read other 6 answers
RELEVANCY SCORE 90.4

Hello,

I have two problems:

1.
I have a lot of scanning data, all date and time stamped. All the scans show different status of shipped packages (all shipments have unique ID#, but there are multiple scans on route for all of them to track the packages). I want to find all the packages, for which there was "misdirected" scan, then for all of them, show the scan before. So, the end result would be all the scans that show for each package where it was misdirected (the terminal# of the scan before the misdirect scan).

2.
I want to automate this: run 5 queries every two hours between 8am and 10pm, copy and paste all results into Excel to specified areas (e.g. first query results to A1, second to A5000, third to A10000, etc. or onto different tabs), save as .csv to a specific folder, then send it as attachment to a given e-mail address. Even partial solution can be good.
I only know macros (somewhat), not as much VBA, so I tried that with the TransferSpreadsheet command, but can't get it work.

Any help is greatly appreciated.
 

A:Solved: ACCESS: Show second latest data and export data to excel from macro

Read other 14 answers
RELEVANCY SCORE 90.4

When i export my inventory from my DB into an excel file, my trailer brands are listed as they are in my DB (of course). But when i bulk upload them with XML feed to trailer inventory sites (HTW.com & HTT.com for our example here). My inventory is listed on their site, but it's not searchable because the brand search is a drop down menu on these sites.

Say i goto HTW.com and do a search for 'Blue Ribbon' trailers none of my blue ribbon trailers show up because all of mine are listed as 'BLUE RIBBON TRAILERS, LTD' and the site is searching for 'Blue Ribbon' and although they are the same trailer brand HTW.com does not see them.

What i'm looking for is maybe a Macro or even formula that once i tell it what site i'm working on, will go through my list and replace all my spellings with the proper ones for that site...

Attached is an XLS file with 3 columns, the first is my list as is from my DB, the other 2 columns are the proper spelling for each corresponding site...

Excel's find and replace feature is a VERY crude fix to this, but since it's so time consuming it's NOT a viable solution.

Any takers??

Slurpee? Zack? Anyone??
 

A:Solved: Excel -Macro or Formula? Replace my data with similar required data - sample

for the record, a complete solution would be GREAT, but not necessary...
I just need someone to point me in the right direction and maybe a small working sample of like 5 brands and i'll expand the list from there...

cheers
 

Read other 3 answers
RELEVANCY SCORE 89.6

Hi all,
This is a great board-- I have received a lot of help from here!
This is my next project.
Open the attached file and, on the right of the form, there are six cells with text.
I need to concatenate these with one space in between each one, then copy the group of them and use the clipboard to paste so I can open the "save as" dialog box and paste it as the filename. We need this in our environment to be able to create the file name quickly, paste it and save the file.
I want to be able to do this with a button that activates a macro.
You can see the button on the right side when you open the file. The macro assigned to it called "createfilename". The code is in module 1.
I got it started, but I know it needs help.
Thanks!
 

A:Solved: Excel macro to concatenate and copy cells for pasting to "save as" dialog box

Read other 7 answers
RELEVANCY SCORE 89.6

hello specialists,

need a help in my job (not a school
I have Sheet1 with 5 columns and 400 rows.
the columns (A:E)are: name / number / number / sum / quotient
last two have the formula.

I need a macro which make me new sheets for those 300 rows,
using the data from the Sheet1.

f.e
Sheet2 -> data from Sheet1, range (A2:E2) beginning at A1
Sheet3 -> data from Sheet1, range (A3:E3) beginning at A1
...
Sheet100 -> data from Sheet1, range (A100:E100) beginning at A1
...

it would be great, if it could be possible, to create all of this with the formula, i mean, i change data at Sheet1, and all over data in the following sheets are changing.

Is it possible to name new Sheets with the data from column 1 (f.e name)?

Was trying with the For/Next loop - doesnt look like win.

Cheers for any help. Final code would be a bit more desirable
p.s finished already 15 new sheets by hand ;/ thats not so easy. I will adjust your code to my work.

Best regards,
no-programmer.
 

A:[EXCEL&VBA]copying data from sh. to sh.

Read other 10 answers
RELEVANCY SCORE 88.8

Hi there ... I want to write a macro that would take the user to the next new unique data in a column. I have a list of account numbers that may be listed 3 times back to back in the column or up to 50 times back to back. I have a particular cell pointed at the top of the column, (ie, "=A1") and I want a macro to take the user to the next new account number in that column, which could be any number of rows down.

Suggestions? The cell I want the reference to change is in sheet1 and the account numbers are in a different sheet, same workbook.

Thanks!
 

A:Macro for Excel - next new data

Read other 14 answers
RELEVANCY SCORE 88

I want to create a worksheet in excel from data in another Excel workbook. The data needed is in a workbook with 52 sheets, I need the same cell from each of the 52 sheets to be inserted into the new table.
I.e. Cell G6 from the 52 worksheets into a single worksheet listed vertically.

Any help would be appreciated.
 

A:Solved: Excel multiple copying

Read other 16 answers
RELEVANCY SCORE 88

Hello - I want a macro in excel 2003 or (workspace pro 6.5 or Happy Harvester2) that collects info from the web sub page, one case after another case, till done. It should put selected info in excel, each case on a different line. However, the excel "repeat" under "edit" that I am using, fails to accomplish moving to the next line; instead it repeats the first line. I have made the macro with mouse clicks, and dont understand VBA:

Desired excel result"
ADDRESS CITY OWNER LENDER CASE DATE
123 Maple | Xenia, Ohio | Able Jones | Bell Mortgage | cv1223 | 11/04/2010
380 bell st | Dayton, Ohio | Blule | Bank America | dv 3348 | 10/03/2010

Web page listing cases URL: http://www.co.greene.oh.us/pa/pa.urd/PAMW6500
Able jones cv 1223
brown cv 129
blule cv 3348

Sub page with details on one case
URL: http://www.co.greene.oh.us/pa/pa.urd/pamw2000*o_case_sum?473007
Bell Mortgage vs Able JOnes cv1223
address: 123 Maple
Xenia, Ohio 45385

filed 11/04/2010 status open
Thanks, thanks so much.
 

A:Excel macro geting web data

What program did you actually create the macro in? Can you post your existing code?

Rollin
 

Read other 1 answers
RELEVANCY SCORE 88

I would like to write an Excel macro that includes pausing for data entry from the keyboard. For instance I have a spreadsheet for a simple bookkeeping project. I would like a macro that, when invoked, would do this:
Type "Current income" in the cell the cursor is on.
Move two columns to the right.
Stop for me to (1) enter a dollar amount and (2) hit the "enter" key.
Move two columns to the right.
Stop for me to (1) enter a dollar amount and (2) hit the "enter" key.
End of macro.

My thanks for any help!!
 

A:Excel macro for entering data

Read other 11 answers
RELEVANCY SCORE 87.6

Any ideas of how to copy checkbox data from Outlook 10 Calendar to Excel?

I created a large number of custom controls in Outlook Calendar. These include a number of checkboxes. I created a custom view with all of the fields (it's in list format so it looks like a spreadsheet). I used CTRL-A and CTRL-C to select all and copy. When I pasted into Excel none of the checkbox controls transferred. Everything else is there. I tried the same thing with Access with the same results.
 

A:Copying data from Outlook Calendar to Excel

Controls are not data, you cannot copy them elsewhere.
 

Read other 1 answers
RELEVANCY SCORE 87.6

I am trying to combine information from 2 spreadsheets into a new spreadsheet. Basically, I am trying to link company information from both spreadsheets to create a new spreadsheet with the combined information (where available). The problem is that not all the companies from "Loc.xls" are listed on "Fin.xls". "Fin.xls" is a more detailed listing of financial data with more companies. "Loc.xls" has addresses of companies (all are listed on the other spreadsheet, but "Fin.xls" has other companies too). Both spreadsheets have an alphabetical listing of companies, but with different information I would like to combine for the companies that are listed on both spreadsheets (the companies listed only on "Fin.xls" will just have empty cells for certain information). I am hoping there is an easier way to combine information rather than by tediously cutting and pasting because I have a rather large list. What would you suggest to simplify this task?

(note: I created a small version of the 2 spreasheets as a bmp file to give a general visual representation)
 

A:Excel Spreadsheet Data Copying/Combining

Read other 6 answers
RELEVANCY SCORE 87.6

Hello,

I need to sort extensive data and am facing the following problem:

There are two sheets: Sheet1 and Sheet2

Columns in Sheet 1 are the following:
company PERMNO number, dates, market capitalization (no data in this one)

Columns in Sheet 2 are the following:

company PERMNO number, ticker, dates, market capitalization

In Sheet2, there is a whole range of dates (between 1990 and 2004) for every single company, whereas in Sheet2 there is one or 3-4 dates for a every single company.

I would like to match the exact date/dates in Sheet1 in the range of dates in Sheet2 and then if they match, copy the corresponding cell value from the market capitalization column in Sheet2 to the empty market capitalization column in Sheet1. I also want that the company PERNO numbers match.

In short: if PERMNO numbers match, match the date/dates in Sheet1 within the range of dates for the same company in Sheet 2 and copy the market capitalization value to Sheet1.

Example:

Sheet1:
A B C
1. PERMNO DATES MARKETCAP
2. 13123 199803
3. 13123 199904
4. 65456 200005
5. 44550 200104
6. 44550 200211
7. 44550 199601

Sheet2:
A B C
1. PERMNO DATES MARKETCAP
2. 13123 199803 8900000
3. 13123 199804 7767575
4. 13123 199805 7567657
..............
.............
99. 13123 200412 7999999
100. 65456 199803 4141241
101. 65456 199804 4145331
101. 65456 199805 4145551
............
............
200. 65456 200412 4458888

When we match the date from sheet1 - 199803 and 199904 within the range from She... Read more

A:Need help with excel VBA - matching and then copying data from two sheets

Off the top of my head, I would think all you need to do is use the built-in "find" function. Look it up. Make sure sheet 2 is sorted before you use the find.

For each PERMNO in sheet1, do the find function on sheet2. When it finds a match it returns the row of the FIRST match in sheet2. At this point, loop through the rows in sheet2, starting on the row that the find function returns, until you hit a PERMNO that is not the one you are looking for. This will work only if sheet2 is sorted. On each of those iterations you can check for the matching date, and on that match you can do the copy of the marketcap.

In VBA, you have to be very careful about which sheet you are referencing. So make sure to always put sheet1.cells(3,4) instead of just cells(3,4).

Hope this helps somewhat.
 

Read other 3 answers
RELEVANCY SCORE 87.6

I need to write a formula to merge data from two Excel Spreadsheets. Both spreadsheets are located on my local “C” hard drive on my computer. The spreadsheets are used to track technician visits to stores. The primary spreadsheet is called “Compliance” and has mostly information about the stores. The second spread sheet is called “Access” and has information about the technicians and their visits to the stores. The data is alpha numeric. Each spreadsheet has a column of data in common which is called “Store Number”. On both spreadsheets the Store Number data occupies Column A.

I need to copy the four columns from my spreadsheet called Access onto my Compliance spreadsheet and have the data from Access line up with the data from Compliance by matching via the Store Number field. I would like to have all four columns from Access end up to the left of the data on my Compliance spreadsheet. I have been trying to set up a VLookup formula but I have not been able to get it to come out right, maybe I am using the wrong formula or going about this wrong.

If anyone has any suggestions they will be appreciated. Thanks.
 

A:Solved: Need Help Copying Excel Worksheet Data

Read other 9 answers
RELEVANCY SCORE 87.6

Hi There

Quite new to this. I am creating a macro to use create multiple charts. The idea being that I use column A everytime as the X axis and then columns B, C, D etc as the Y axis. Each graph will only have one series, i.e. chart 1 uses AB, chart 2 uses A and C, chart 3 uses A and D etc etc. I am attempting to use dymanic named ranges. My code is almost there however I have two problems; the first graph is produced fine. The second graph however includes colum B as a second (unwanted) series, the next graph includes columns B and C, etc etc. Why is this as they are not included within the named ranges? Secondly, the first graph comes out with a chart title fine however the subsequent graphs do not. Below is my code...

Sub charts()

Range("a1").Select
Range(Selection, Selection.End(xlDown)).Select
myrangeAREA = Selection.Address

Range("b1").Select
Range(Selection, Selection.End(xlDown)).Select
myrangeRWE = Selection.Address

Range("c1").Select
Range(Selection, Selection.End(xlDown)).Select
mrRWECHA = Selection.Address

Range("d1").Select
Range(Selection, Selection.End(xlDown)).Select
mrtest = Selection.Address
With ActiveSheet.ChartObjects.Add(Left:=100, Width:=327, Top:=75, Height:=229)

.Chart.SetSourceData Source:=Sheets("Sheet1").Range(myrangeAREA, myrangeRWE)
.Chart.ApplyCustomType ChartType:=xlUserDefined, TypeName:="custom1"
End With

With ActiveSheet.ChartObjects.Add(Left:=150, Width:=327, Top:=125, He... Read more

A:Excel VBA macro - multiple charts

Can you post your workbook?

Regards,
Rollin
 

Read other 2 answers
RELEVANCY SCORE 87.6

Give me a hand to impress the boss!

I have a column which has a list of different values. For each value in this column i wish to create a new sheet and name the sheet the same name as the value in the column.

For this i have set up a do/while loop , however, after about 27 turns, it stops.

My guess is that it is something to do with the sheet Name property, or the number of sheets that are there. i.e. in the VBA project box on the code screen, it appears like this
Microsoft excel objects
-sht1(main)
-sht11(value)
-sht111(value1)
-sht1111(value2)
etc until
-sht111111111111111111111111(valuex)

Here is a summary of the loop;

Dim strValue1, strValue2 As String

Do While intCounter < intNumSheets
strValue1 = ActiveSheet.Range("E" & intCounter).Value
strValue2 = ActiveSheet.Range("E" & intCounter + 1).Value

If strValue1 <> strValue2 Then
Application.CutCopyMode = False
ActiveSheet.Copy After:=ActiveSheet
ActiveSheet.Name = ActiveSheet.Range("E" & intCounter + 1).Value
End If
intCounter = intCounter + 1
Loop

Email me
 

A:Excel Macro - Multiple sheets

Yep. Bad news when that occurs. After while, you won't be able to open the workbook. You must be using 97, huh?

Each time you make a copy of a copy, it adds the one.

Try doing it this way instead:

Create yourself a sheet you'll use as a template.
Copy ONLY that sheet each time you create a new sheet.
You can hide the template sheet.

Don't ask me for the code, LOL!
I don't code, but I can troubleshoot it pretty well. I know that makes no sense...
 

Read other 1 answers
RELEVANCY SCORE 87.2

Hey,

Posting here before, I received several prompt replies. Due to my success, I will try once again, but this time with a different issue/question.

I have a spreadsheet with 1 column and 3000 rows. In each row, there is a 1 cell block with a series of numbers/characters. I need some type of formatting/macro that will extract only certain characters from each row and place them somewhere else. In example, I have the following rows:

7r74$#@$i4 i3o45$#---$#@$#/4432438
4327$##@$#4343u3i33$$( #343432822
4324*& 345435**(&^*&^342434234324

I need to extra the 12, 13, & 14th character of each row, whether it be a letter, number or space, it doesn't matter. Now the 12, 13 and 14th character need to be placed in there own newcolumn to the right of where they were prior. So the results, would look like this:

7r74$#@$i4 i3o45$#---$#@$#/4432438 i3
4327$##@$#4343u3i33$$( #343432822 343
4324*& 345435**(&^*&^342434234324 435

The post here will not let me space it correctly, but to the right of the first entry would be " i3", the second, "343" and the third "435".

So, it seems pretty simple, but I am not knowledgable enough with formulas, macros or vb to be able to figure this out. I also need to be able to adjust the formatting/macro/vb script every couple months as the "character extraction" place could change.

Thank you, hopefully someone is familiar with what I am trying to do and can understand this basic i... Read more

A:Excel, Formatting/Macro Creation for Data

Are you okay with just a formula? Try this if your data is in A1:

=Mid(A1,12,3)

12 is the starting position, and 3 is the number of characters to return.

Let us know if you do need it in a macro.

HTH,
 

Read other 3 answers
RELEVANCY SCORE 87.2

Hi,
I am trying to run a macro which will insert a row of formula to the last row of my data. Say for example.

A1: Formula
A2: 1
A3: 2
A4: 3

I wish to set the macro to always insert into the last row of column A, instead of at Cell A4. Something like,

A1: Formula
A2: 1
A3: 2
A4: 3
A5: Formula

something to do with lastrow formula? Any help would be greatly appreciated.

my current macro is written like this,
'
Rows("13:13").Select <- the formula row
Selection.Copy <- copying the formula
Rows("17:17").Select <- i wish this to go to the last row instead**
Selection.Insert Shift:=xlDown
End Sub

Any help would be much appreciated.

Thanks
 

A:Excel Macro : inserting copied data to the last row

Howdy, and welcome to the board.

Something like this:
Code:

Sub test()
Dim lngLastRow As Long
lngLastRow = Cells(Rows.Count, 1).End(xlUp).Row
MsgBox lngLastRow
End Sub

then you could use for the next formula row:

Cells(lngLastRow + 1,1)
 

Read other 3 answers
RELEVANCY SCORE 87.2

Hi,
I have a major problem crunching large amounts of numbers coming off an instrument I work with but the file format it puts the results into is terrible and I cannot change it. So I must export the results in a format which needs some serious formating before it becomes of any use to me.

I have used excel macros to delete rows and rows with specific text values. But now I would like to create a macro that will reorganise this data in a more user friendly format. I did use the sort asending command to partially solve the formating problem but it would still require alot of work/time to organise it into the final format I need.

This is the current format of the instrument date:

Analyte Meas. Intensity
Rh 10
Y 98
Cs 66
Co 98
Analyte Meas. Intensity
Rh 10
Y 114
Cs 72
Co 86
Analyte Meas. Intensity
Rh 20
Y 108
Cs 44
Co 90
Analyte Meas. Intensity
Rh 20
Y 68
Cs 44
Co 66
Analyte Meas. Intensity
Rh 490.008
Y 58
Cs 34
Co 78
Used the "sort asending" command to separate data into useful form but need to get the data into either of the two tables shown below

Analyte Meas. Intensity
Analyte Meas. Intensity
Analyte Meas. Intensity
Analyte Meas. Intensity
Analyte Meas. Intensity
Co 98
Co 86
Co 90
Co 66
Co 78
Cs 66
Cs 72
Cs 44
Cs 44
Cs 34
Rh 10
Rh 10
Rh 20
Rh 20
Rh 490.008
Y 98
Y 114
Y 108
Y 68
Y 58
I would like to turn the data into this final format using a macro.

Co Cs Rh Y
98 66 10 98
86 72 10 114
90 44 20 108
66 44 20 68
78 34 490.008 58

Can somebody help me please ... Read more

A:create an excel macro to reorganise data?

ZIP and post a sample workbook.
Rollin
 

Read other 1 answers
RELEVANCY SCORE 87.2

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

Read other 6 answers
RELEVANCY SCORE 87.2

I have a need to move data from one worksheet that has an associated date to the correct spot in another worksheet with column that runs from 1/1 to 12/21.
Worksheet 2
1/1
1/2
1/3
1/4
...
12/31

Worksheet 1 has:
1/29 45
1/30 23
2/4 13
The macro would read the date in column A of worksheet 1 and put the data into the correct row of column B in worksheet 2. So that after running the macro worksheet 2 would look like this:

1/1
...
1/28
1/29 45
1/30 23
2/1
2/2
2/3
2/4 13
...
12/31

I hope my intention is clear and understandable.

Thanks in advance for any help,
tom
 

A:Excel Macro to move data into correct rom

Read other 8 answers