Free Microsoft Excel 2013 Quick Reference

VBA code to import specific data from one excel workbook to another excel workbook

What I want to do is create a bottom in excel 2007 workbook (FileX.xlsm) that when pressed will open a window that will allow me to chose an excel workbook (FileY.xlsx) to import the data from, and input it into "FileX.xlsm". What I would like to do take data from "FileY.xlsm" that is in say “A1 to AN1” and import it into “A1 to A40” in "FileX.xlsm". If anyone has done something similar to this I know enough to piece it together with a little bit of help.

Thank you in advance


Post your answer or comment

comments powered by Disqus
Hello,

This is my first post to this forum (be kind! ) and I am relatively new to VBA and need some help. I am currently using VBA to copy specific data from one worksheet to another, but would like to change it to copy the same specific data from one workBOOK to another. I would also like to add a parameter that would only copy data entered on today's date. My workbooks are named "PM-Test" (source) and "Changes"(destination).I really hope someone can help me! The code I am using currently to copy from one sheet to another follows. Thanks in advance for your assistance!
Sub Copy1to2()
    
    Dim InSH As Worksheet
    Dim OutSH As Worksheet
    Dim i As Long
   
    Set InSH = Sheets("LOG SHEET")
    Set OutSH = Sheets("CLIENT REVIEW")
   
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    OutSH.Range("A2:Y30000").ClearContents
    
    With Intersect(InSH.UsedRange, InSH.Columns("f"))
       .AutoFilter 1, "=Revision-DRG"
        .Offset(1).EntireRow.Copy
        OutSH.Cells(Rows.Count, "A").End(xlUp).Offset(1).PasteSpecial xlPasteValues
        .AutoFilter
    End With
    
    Application.CutCopyMode = False
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    
End Sub


Hi Everyone,

Within VBA code to import web data, instead of spelling out a URL as source is it possible to specify a cell containing the URL such as B2 of Sheet1 ...

Thanks,

Have a great day!!

How to import specific data from text file (and each time of different size text file) to excel?

I have attached an example of the text file.

The data in the text file may look like this:

start of the txt file*******************************************************

2160. WST 0.3 MEMB 1 TO 1824
2161. *STA 0 ALL
2162. *PROJEKTUOJAMI ELEMENTAI
2163. DESIGN ELEMENT 1 TO 1824
STAAD SPACE -- PAGE NO. 42

SLAB/WALL DESIGN RESULTS
(by stresses in local axis for load carrying capacity)
------------------------------------------------------------------------------
Element Asx Mx Nx Load. N. Asy My Ny Load N.
sq.cm/m kNm/m kN/m (X) sq.cm/m kNm/m kN/m (Y)
------------------------------------------------------------------------------
1 TOP: 0.00 0.1 -33.1 1 0.00 -1.0 -187.4 1
BOT: 0.00 0.1 -33.1 1 0.00 -1.0 -187.4 1
2 TOP: 0.00 -0.2 -28.7 1 0.00 -1.2 -159.2 1
BOT: 0.00 -0.2 -28.7 1 0.00 -1.2 -159.2 1
3 TOP: 0.00 -0.3 -27.1 1 0.00 -1.6 -155.2 1
BOT: 0.00 -0.3 -27.1 1 0.00 -1.6 -155.2 1

*********** END OF THE STAAD.Pro RUN ***********

enf of txt file********************************************************

I want to get only this in the excel:

1 TOP: 0.00 0.1 -33.1 1 0.00 -1.0 -187.4 1 BOT: 0.00 0.1 -33.1 1 0.00 -1.0 -187.4 1 2 TOP: 0.00 -0.2 -28.7 1 0.00 -1.2 -159.2 1 BOT: 0.00 -0.2 -28.7 1 0.00 -1.2 -159.2 1 3 TOP: 0.00 -0.3 -27.1 1 0.00 -1.6 -155.2 1 BOT: 0.00 -0.3 -27.1 1 0.00 -1.6 -155.2 1

I know there is nothing impossible.. Can somebody help me with this?
Till now I'm using Notepad++ text editor. Holding shift key I select vertically each column in the txt file and copy that column to excel.
So it's time confusing... It would be a good start for me.

Thanks for paying attention

I get reports every hour via email with data in .csv format (file name will be data_201106070900.csv - For 06/07/2011 9AM data) I can route the data delivery to a folder on a windows server.

Now, I want to import some data from the .csv files to Access DB. here are my challenges:

a) consider file name (data_201106070900.csv) of .csv file and extract 201106070900 from file name and import to a field in a table
b) When I open the .csv file in Excel I see the data I wanted is in E2:F14 range but the data is calculated using excel formulas from Col A, Col B and Col C. So, when I open the same .csv file in notepad I see the formulas
c) Once a file is processed it should be moved to another folder to avoid data duplication

Can some one give me pointers how to achieve this.

Thanks

Hello all

I am looking to import specific data from a text file (only certain lines). I assume using VB code is the way to go, but am not sure what the code should look like (I have done limited VB programming).

I am looking for code that will:
1. When run, will ask me for the path (let me browse) to the file location (but I could hard code path).
2. Will import (read) only certain defined information and append the data to the current data column. The file is like:

Box1_a, 10
Box1_b, 15
Box1_c, 20
Box2_a, 25
Box2_b, 30
Box2_c, 35
Box3_a, 40
Box3_b, 45
Box3_c, 50

And I just want to import the values of lines 1, 4, 7 (10,25,40). These values would be append to the data from the previous import.

Any ideas

Thanks

Charlie

Hi,

I am totally NEW to macro (VBA or Excel).

I wanted to know how to move specific data from a worksheet to another worksheet.

For example, Worksheet1 contain below data:
Order# Item
------------------
10001 Pencil
20004 Catridge
10006 Ball Pen
10003 Pencil Lead
20006 Paper
10005 Rubber

The Order# is the raw data from users. I want to move the data that have Order# begins with 1 to a new worksheet (Worksheet2) and then Order# begins with 2 to another worksheet (Worksheet3).

Can someone help?

I have a worksheet template that needs to import specific data from a CSV file but I'm not sure how to do this properly.

My scenario is this.

Column A in the CSV contains a code, such as AADEV, AANEW, BADEV etc

I need to examine this code, and extract all columns into my template where the first 2 letters of the code equal (say) "AA" regardless of what follows afterwards.

Any ideas?

I need a little help. I need to pull data from one excel workbook (A) and
display the data on another excel workbook (B) but i only want to pull the
date if in workbook (A) cell F is yes. If document (A) cell F is no then i do
not want to pull the data to workbook (B). Can this be done?

Hi

I just want to check that if it is possible to copy specific cells from one row to another sheet by using macro or any other script.

Attached is a sheet which explains what exectly I want

The sheet one is having Source data
Sheet three is the result sheet

If I put some code numbers (in number form) it should search the data from source sheet and update the same in result sheet in different rows

Please help

Hi guys.

I'm stuck. I'm trying to set up a macro which will import data from one worksheet to a master sheet. I need it to copy the information into specific columns but not overwrite any existing information which is already in the Master Sheet, but I don't even know where to begin.

Just so you're clear on exactly what it is I'm trying to do... I have a Master Sheet which lists all of our suppliers prices, margins etc etc... However, when we use a new supplier we send them a greatly condensed version of the Master Sheet - We call it the Supplier Sheet (no big surprises there)!

When the supplier sends it back to me I have to type it all out manually which is kinda time consuming. I'd really like to set up a "push button" system which allows me to simply drag the Supplier Sheet into the workbook, add the info into the Master Sheet, then be able to delete the now useless Supplier Sheet.

Is this possible? Can anyone give me some ideas or help please?

Thanks.

(I have attached a test copy of the file - all of the columns in blue are the ones which need the data adding to).

Hello!
I'am trying to write a code that copies specific cells from one workbook to another without overwriting existing data in the destinationbook. However, my codes fails. I want to do the same procedure for another six cells which are not next to each other in the sourcebook and are not going next to each other in the destinationbook
Here is my code, could somebody help to adjust?
Thank you!

	VB:
	
 copyrange() 
    Dim b1 As Workbook 
    Dim b2 As Workbook 
    Dim w1 As Worksheet 
    Dim w2 As Worksheet 
    Set b1 = Workbooks("POForm") 
    Set b2 = Workbooks("DataBase") 
    Set w1 = b1.Worksheets("PurchaseOrder") 
    Set w2 = b2.Worksheets("SpareParts") 
    Dim sourcerange As Range 
    Dim destrange As Range 
    Set sourcerange = w1.Range("O4") 
    Set destrange = w2.Range("A3" & Rows.Count).End(xlUp).Offset(1) 
    sourcerange.copy destrange 
    b1.Close 
End Sub 

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


I need a macro that will extract specific data from one worksheet to another worksheet. There are 2 "filters" to consider (If a cells value is "A" then, If a cells Value is "No" then) "Extract Data" to another sheet.. there are 38 rows to be considered in the selection. the specific data is contained in 4 columns. I have reached a point that I have the macro working on one line only in the destination sheet, it needs to fill about 35 lines.

Hi,

I'm trying to develop a temporary Excel database system at work until we can get hold of a proper Database system. I would like to create a function using a button to generate VBA code that will submit form data from one Excel document into a general repository within a separate Excel document.

Many people will use the form.xlt file to enter data and submit it to the database.xls file. I cannot find anywhere online which will show me how to connect to another Excel file, create a recordset of the data being capture in my form, and then exporting it to my database xls document.

Many thanks to anyone who can help me out on this.

Hi All,

I have been trying to figure out a way of copying some specific data from one excel spreadsheet to another. I have attached the spreadsheet showing the data that i am wanting to copy across.

What i am wanting to achieve is to copy the filename and data about the creation dates and modification dates of a database. The tricky part is how to take the first file revision i.e. 1582 - Spool Installation - 001.db taking the date created and time created and placing this in the new sheet. Then reading down the revision numbers i.e. 1582 - Spool Installation - 008.db and taking the date modified and time modified, putting them into the new sheet.

I am wanting to make this into a macro so that i dont have to keep copying the data across, which is a very laborious task especially when there are around 100 databases to search through.

I have a little knowledge about VB.Net but im not too familiar with VBA for Excel. Any help would be very much appreciated.

Cheers,

Dan

Test Database Sheet.xls

Hello group,

I am trying to write a vb code to copy a field from one Excel worksheet to other Excel file but having error message as “run time error… Subscript out of range”. Here is my code:

Dim rw&, col&, cel As Range
rw = "O:AdminCheque Pick Up Forms - Requested" & Sheets("Report").[D:D].Find("").Row
col = 4
For Each cel In Range("D36")
Sheets("Report").Cells(rw, col) = cel.Value
col = col + 1
Next

End Sub

Please help in identifying the error
Thanks

I wonder if someone can help me out. I have a workbook that contains
multiple worksheets, one of which is a master data sheet.

I would like to copy specific data from this sheet to multiple
worksheets based on specific criteria.

Master Data Worksheet is setup as follows:

Date Salesrep Customer Project $ Status, etc.
Aug A ABC etc.
Aug A DEF etc.
Aug B GHI etc.
Aug C JKL etc.
Aug C MNO etc.
Aug D PQR etc.

I would like the macro to lookup the Salesrep name and copy the enitre
row for that rep over to the specific Salesreps workbook and then save
this data.

Thanks in advance for your help.

Mark.

Guys

I need to copy data from one excel file to other. Am doing this in
asp.net with c# as backend code. Do I need to two excel Application
objects? No matter if I use two excel objects or one, I am ending up
with one excel object in memory even after closing them. Whats the best
way to do this without leaving any Excel apps around? Please help.

Heres what my code looks like:

Excel.Application exclApp = new Excel.ApplicationClass();
Excel.Workbook... srcWb = Open("source.xls",...)
Excel.WorkSheets srcSheets
Excel.Worksheet srcSheet

Excel.Workbook dstWb = open("dest.xls",....)
Excel.Worksheets dstSheets
Excel.Worksheet dstSheet..

dstSheet.range = srcSheet.range;

cleanup..

I call ReleaseComObject() on all above objects and finally.

exclApp.Quit();
GC.Collect();

Hey,

I have a rather simple issue I guess. I am trying to transfer data from one excel file to other. I tried using the following code but it shows some funny text that I dont understand. Can someone please help me resolve this fast.

Application.ScreenUpdating = False
Dim sPath As String
Dim fName As String
Dim s As String
s = CurDir
'sPath = "C:MyfolderMyfiles"
ChDrive sPath
fName = Application.GetOpenFilename( _
Filefilter:="Excel Files (*.xlsx),*.xlsx")
ChDrive s
ChDir s
If LCase(fName) = "false" Then Exit Sub
With ActiveSheet.QueryTables.Add _
(Connection:="TEXT;" & fName, _
Destination:=Cells(Rows.Count, "A").End(xlUp).Offset(1))
.Name = Replace(LCase(fName), ".xlsx", "")

.TextFileStartRow = 2
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.Refresh False
End With

I am trying to copy certain data from one book to another.

Example: If workbook "A" contains "PIR" in column "E" pull data from column K,D,C,O,R,S in that order and populate in seperate workbook cells A,B,C,D,E,K in that order. So K=A, D=B, C=C, O=D, R=E, S=K

Somewhat new with macro's however comfortable with excel.

Thanks in advance

Hi All,
I have a excel workbook containing 20,000 rows of data. I will have to develop a macro to copy a certain range of data from that excel workbook & paste the same in another excel workbook, rename the workbook & save. Suppose one column contains certain rows with values as 1, next set of rows as column value=2, another set of rows as values =3. I will have to take all the rows corresponding to value 1 and paste in the second excel workbook, rename, save & close. This process repeats for column values=2 & 3 as well.Please help in writing the Macro Code.

The source Excel resides in the location:E:AniSIMS
The destination excel resides in the location :C:AMSIT

Please help me in writing the Macro for the same as this is URGENT for me. Looking forward to your kind assistance with the Macro Code for the same.

Thanks
Ani

I wish to import information using a macro from one Excel Workbook to another.

Where's the steps I wish to undertake.

1. User opens workbook and activates macro (Quick Button)
2. Macro will pop up InputBox and ask for the row line in the destination workbook in which to import the information of the source workbook.
3. The macro will then verify in the destination workbook to ensure that it is infact empty and has no information allready inscribed. If no Proceed with macro, If yes return to step 2
4. Gather the information from the 20 or so fields in the source workbook and copy them into respective fields in the destination workbook.

Thank You

Hello Again!

Is it possible to import just specific columns from one Excel worksheet to another. For example, my rows are listed below:

"Name" "Emp #" "Birthday" "Department" "Hire Date" "Years of Service"

How can I import just the Name, and Birthdate column information to another Excel Spreadsheet?

Thanks a million!

hi, I'm trying to transfer data from one excel file to another. The format of the file i want to transfer the data to is different from the file I'm getting the data from. How can I transfer these data to different cells. I could copy and paste all the way through but it would take forever. So probably a macro or a piece of code that could help??

subject: Trying to go carry data from one sheet to another and have the formula rows increment

Hi,

A bit of background, I am helping the local rescue animal charity to create a an excel workbook instead of having everything on scattered pieces of paper. I am volunteering my time, and I can do simply things with excel, but I get confused when I have to use a mix of functions.

What I've done so far, as they have requested. They have been running for three years now and I am going to enter the data for the first year.

1. I have a sheet in a workbook that has 249 rows, the number of donations they received that year. Row 250 will add the total. Simple I can do.

2. The next sheet, will be for Jan, next sheet for Feb, and then more sheets for every month of the year. They are supposed to - on the directions of a volunteer accountant - create a summary sheet with all the details from the first running details sheet that can be printed off for each month of the year.

What I am trying to do:

- The first sheet is completed.

- On the second sheet - for Jan (I figured once this one is set up, I would copy it for the rest of the months)

I am trying to enter in a formula that calls the cells for:
a. The date
b. Who donated - including mailing addy
c. amount donated
d. receipt requested for income tax purposes

I can do this manually which would take forever, or I can come up with a formula that when I put it in the row for each column heading on the second sheet, I can just drag the formula down and all the data entered into the first sheet will auto be sorted to the right spot on that month sheet.

I think I would need to use the indirect and address function but I am a one function person, more then one and I get confused.

I would appreciate any help, anyone can give me.

Thanks in Advanced!

Julia


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