View Full Version : SOLVED: Open a workbook and copy data into another
primaryteach
12-12-2008, 09:49 AM
Can any one help with the following?
I need a macro that can open another workbook, select a given sheet and copy range of data (e.g. A2:AK20000 - yep it's big!!), then paste only the values (i.e. not the formulas) back into my original main file. The data would be pasted into cell A20001 and then sorted according to column A. This way I can add another file's data, as long as I never exceed the 20000 row.
I have seen a macro that can do this if you have typed the exact pathname/filename into a cell, but this a few issues I want to avoid:
1) Having to know the exact pathname, as errors might occur when typed.
2) Because the main file will import the data from each file in a different location / different computers, pathnames will have to be typed each time - hence not very user friendly.
I know a small bit of vba, but you might as well treat me as a newbie!
If you can help I would be really grateful.
Cheers
Simon
UK Primary School Teacher
mdmackillop
12-12-2008, 10:23 AM
Hi Simon,
Record a macro doing exactly what you have described. That is a good starting point. Post the result and we can look at tidying it up.
Regards
MD
primaryteach
12-12-2008, 10:48 AM
Sorry, my basic macro is as follows. I haven't included the sorting the data.
Sub Opensesame()
'
' Opensesame Macro
' Macro recorded 12/12/2008 by Simon R
'
'
Workbooks.Open Filename:= _
"C:\Documents and Settings\Simon\My Documents\smaller file.xls"
Range("A11:AK20000").Select
Selection.Copy
Windows("Main file.xls").Activate
Range("A21000").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
ActiveWindow.ScrollRow = 1
Range("A10").Select
Application.CutCopyMode = False
Windows("smaller file.xls").Activate
ActiveWindow.Close
End Sub
mdmackillop
12-12-2008, 12:02 PM
Give this a try
Option Explicit
Sub Opensesame()
Dim wsTgt As Worksheet
Dim wbSource As Workbook
Application.ScreenUpdating = False
Set wsTgt = ActiveSheet
Set wbSource = Workbooks.Open(Filename:= _
"C:\Documents and Settings\Simon\My Documents\smaller file.xls")
wbSource.Sheets(1).Range("A11:AK20000").Copy '< is sheet correct?
wsTgt.Range("A21000").PasteSpecial Paste:=xlValues
wbSource.Close False
wsTgt.Range("A10").Select
Application.ScreenUpdating = True
End Sub
primaryteach
12-12-2008, 12:16 PM
This does tidy up my original code, thank you, but I still have the problem from my original post, i.e. the macro code identifies a specific file, rather than letting me choose which file I want to source my data from (the location and filename will vary each time I want to use it). Is that possible?
Also, is there a way of getting rid of the message about the 'clipboard having a large amount of data'?
Thanks again,
Simon
mdmackillop
12-12-2008, 12:42 PM
Option Explicit
Sub Opensesame()
Dim wsTgt As Worksheet
Dim wbSource
Application.ScreenUpdating = False
Set wsTgt = ActiveSheet
wbSource = Application.Dialogs(xlDialogOpen).Show
ActiveSheet.Range("A11:AK20000").Copy
wsTgt.Range("A21000").PasteSpecial Paste:=xlValues
Application.DisplayAlerts = False
ActiveWorkbook.Close False
Application.DisplayAlerts = True
wsTgt.Range("A10").Select
Application.ScreenUpdating = True
End Sub
lucas
12-12-2008, 12:50 PM
Sub Opensesame
That's funny Simon.......I'm borrowing it if you don't mind.
primaryteach
12-12-2008, 02:35 PM
mdmackillop (http://www.vbaexpress.com/forum/member.php?u=87) a real big thank you. I have adjusted the code to include the sort and so far so good.
I have one last issue, if I cancel on the open workbook window it closes down the master file without saving. How does the code need to change to make sure it returns the user back to the master file?
Simon
P.S. Lucas you are more than welcome!
mdmackillop
12-12-2008, 02:44 PM
Add the second line here
wbSource = Application.Dialogs(xlDialogOpen).Show
If wbSource = False Then Exit Sub
primaryteach
12-12-2008, 03:54 PM
Brilliant. Thank you for your help.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.