Open Workbook Where Named Cell Contain Name & Path, Copy Range And Paste Values
May 19, 2009
I am successfully opening a .csv file using a variable value stored in a named range in my Main file (the variable includes the directory and path).
I copy data from the .csv file to the Main file then I need to close the .csv file without saving but I want to do that by using the
Windows("xxxx").Activate
command where "xxxx" is the namedrange in my Main file which stores the .csv filename (without the directory and path prefix).
I can use the
ActiveWindow.ActivatePrevious
command but if I have another workbook open, this one closes instead of the .csv file I opened from the macro.
I realise this is probably very basic and I've searched the forums but can't find any identical postings.
View 5 Replies
ADVERTISEMENT
Aug 3, 2014
I have an Excel sheet that contains a few thousand folder paths in the first column. The first few cells in the column look similar to below.
C:UsersNameDesktopFolder Copy12010360
C:UsersNameDesktopFolder Copy12010361
C:UsersNameDesktopFolder Copy12010362
C:UsersNameDesktopFolder Copy12010363
C:UsersNameDesktopFolder Copy12010364
C:UsersNameDesktopFolder Copy12010365
I am trying to create a macro that will copy any folders that exist in any of the paths listed in the first column. The folders should be copied to the path of the open workbook containing the macro. Below is the macro I have currently, much of which was taken from information I found in this thread [URL]....
VB:
Sub wrapper3()
x = 1
Set fs = CreateObject("Scripting.FileSystemObject")
While Sheets("Air").Cells(x, 1) <> ""
v = InStrRev(Sheets("Air").Cells(x, 1), "")
dest = ActiveWorkbook.Path & Mid(Sheets("Air").Cells(x, 1), v, 99)
[Code]...
This code seems to work fine if all of the folders exist to be copied. My problem is that some do not and it is creating a "Path Not Found" error for which I need a fix. If the folder doesn't exist at the path nothing should be copied and the next path can be evaluated.
View 2 Replies
View Related
Apr 6, 2013
I have created a spreadsheet some time ago and have been asked to improve on it but I'm rusty with VBA.
I have an automated ordering system that saves each sent order as the date e.g "05-04-2013.xls" but the management team want a graph with the data for the last 4 weeks compared. I have created a seperate workbook called "consumables report.xls" which has a column with the products listed followed by columns "Quantity" and "cost" which is repeated for the 4 weeks of the month.
I want to add a button to prompt the user to choose the saved order e.g "05-04-2013.xls" (all orders saved in same directory) to copy and paste the quantity and cost columns (c8,D69) into "consumables report.xls". I got this to work earlier but it would only paste the formulas and not the values. So I need
A prompt to open workbook
Copy range (c8,d69)
Close work sheet
Paste special .value (c8,D69)
I dont care if it has to open the workbook to copy the data as this will only be used once a month so it dosnt matter how slow the code is.
week 1 week 2 week 3 week 4
Product
quantity
cost
quantity
cost
cost
quantity
cost
1
2
3
4
5
6
7
8
View 7 Replies
View Related
Mar 26, 2012
Copy data from workbook, open existing workbook, select range and paste. But my copied data is lost.
Sub Select_Copy_Paste()
'
'
Windows("ElektroFunctiesDatabase.xlsm").Activate
Sheets("PowerSupply's").Select
Range("A2:I6").Select
Selection.Copy
[Code] .........
' Here i need to do something to paste data into r.address?
View 4 Replies
View Related
Sep 4, 2007
I have read post re this question but have not been able to answer my problem. I get the error message 'Application defined or object defined error' when running the code below. I should indicate the range counter currently indicated about 6,200 rows that this code will work on and the individual range names in the list of 6,200 rows are spread over at least 20 worksheets.
The code appears to be running but after some time it stops on the line of code 'Range(Cells(i, 1).Value) = Cells(i, 2)'.
Sub PopulateWithImportData()
Dim counter As Integer
counter = Sheets("Imported Data").Range("Counter")
Application. ScreenUpdating = False
Application.Calculation = xlCalculationManual
Worksheets("imported data").Select
Range("a1").Select
i = 1
Do Until i = counter
Range(Cells(i, 1).Value) = Cells(i, 2)
i = i + 1
Loop
View 8 Replies
View Related
May 4, 2008
I'd like to know if it is possible to run a macro in a workbook that will open another workbook (of the users choice) extract data from it, such as columns from its sheets then paste that data into the workbook the macro is running from. The file to be opened will change, so i've found some code that enables the user to select the file to open then open it.
Dim strFile As String
strFile = Application. GetOpenFilename
If strFile <> "False" Then Workbooks.Open strFile
This brings up the standard windows choose file to open box, then opens the selected file. Am I correct in thinking that the Dim here will store the file name I select in the open file box? If so, I'd like to know how to select the workbook using the dim so I can manipulate it from the workbook I'm running the macro in. Incidentally, once the the data has been copied, I'd like the workbook opened with the macro to be closed.
View 5 Replies
View Related
Apr 22, 2014
I tried all failed.We have about 160 different workbooks (one for each business unit) stored online. Staff enters information about their weekly revenue and expenses and here at head office I collect that information and consolidate them.What I am trying to do is that;1.) Create a master Workbook with ~160 worksheets (One worksheet for each unit) named exactly the same with other workbooks2.) And macro can pull the information from related files stored in a certain folderIt is very much like another members problem but I am not sure why I cant get that code working for me? [URL]
View 5 Replies
View Related
Jan 26, 2007
Am trying to copy 50 worksheets from One workbook, into separate existing files (overwriting previous file) in existing folders; once copied, destination files can not be linked to source file.
I'm able to copy the tabs into new workbooks, in the correct folders, but when trying to perform PasteSpecial in new books, original source file is also pasted over.
The parts of the file name and folder location are cell values located on each sheet.
...
For Each sheet In ThisWorkbook.Worksheets
sheet.Copy
Set workbook = ActiveWorkbook
With workbook.Sheets(1)
.UsedRange.Copy
.UsedRange.PasteSpecial xlPasteValues
.Cells(1).Select
Application.CutCopyMode = False
End With
View 4 Replies
View Related
Dec 20, 2007
Been playing with this for some time and can't quite figure it out. I'm trying to copy a named range and paste special value to another named range of the same size. I recorded a Macro that does what I need, and I'll use if necessary, but thought I'd try to consolidate the code a bit and can't get it to work. The recorded macro is as follows:
Sub PasteRanges()
Application.Goto Reference:="DataCopy30Yr"
Selection.Copy
Application.Goto Reference:="DataPaste30Yr"
ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _
IconFileName:=False
End Sub
My attempt to consolidate was this:
Sub PasteRanges()
Range("DataCopy30Yr").Copy Destination: Range("DataPaste30Yr").PasteSpecial (xlPasteValues)
End Sub
I get the Run-time error 1004 Copy method of Range class failed when I try to run this. Again, I can use the longer version with no problem, but in the interest of learning and since I have spent some time not being able to come up with the solution, I thought I'd ask the experts opinions before I gave up on it.
View 6 Replies
View Related
Mar 4, 2014
I would like to copy all the items below the ITEM row and paste it to cell A2 of another saved workbook (say, Saved.xls).
If Saved.xls is closed, the macro would open it and paste. If it is open then it will just paste.
View 1 Replies
View Related
Aug 11, 2007
how to go about taking data from a closed workbook with a range say A1 to A100, and place it in the work book i have open. I have 3 different sheets in the open work book but all the data is coming from the one sheet in the closed workbook.
View 9 Replies
View Related
Feb 21, 2009
I am a trainee dermatologist. We undertake allergy patch tests. There is a long list of different patch tests which are selected based on the patients history. I have put each of the types of patch testing on a master sheet. What I would like to be able to do is to have a control button next to each set, and when clicked would add that particular set to a "new patient" worksheet tab. And with every set having its own button, more than one set could be added to a new sheet. In the new patient worksheet id like the sets added with a single row gap between them.
I had tried, and managed to get a single series to work, but then it wouldnt allow me to add the next set, saying that sheet already exists. Ideally finally, id like there to be some way, or message to make sure after printing the new patient worksheet gets deleted.
I hope that all makes sense ! Even if I had a code for a single button that added to a master sheet at the next available row +1 I think I could manipulate the code to suit.
View 9 Replies
View Related
Apr 30, 2008
Is it possible, in vba, to copy two sets of cell values to two clipboards (clipboard1 and clipboard2?), open another workbook and paste those two seperate clipboard values into another two cells?
View 3 Replies
View Related
Dec 27, 2009
In my project I have two workbooks. I am working on getting one to pull data from another depending on which employee is selected from a drop down list.
TestLOG.xls contains a worksheet for each employee, with named sections within for various training the employee has, and down each row has information on the date this was received, initial, recurrent, etc. It is only this single worksheet for each employee that any data entry occurs, which makes things a lot more organized and efficient.
TestFORMS.xls is the workbook accessed by the records department which has different worksheets depending on what data is to be presented. When an employees name is selected, I need the form to pull the specific data from another workbook, and post it on the current worksheet. This get repeated a couple times to fill the adjacent columns of data. I can't just select the whole table from the other workbook because in this current worksheet for example, only specific columns are pulled from the other workbook.
To better explain the flow...
Current workbook is TestFORMS.xls
Current sheet is Test
Closed workbook is TestLOG.xls
In TestFORMS, sheet Test, when named cell Employee is selected with a value...
Then open TestLOG in the background and open sheet of same name as Employee, and cope range ACtype.
Back in TestFORMS, sheet Test, paste the ACtype data in the range named Type.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Ename As String
Ename = Range("Employee").Value
If IsEmpty(Ename) Then Exit Sub
Workbooks("TestLOG.xls").Sheets(Ename).Range("ACtype").Select
Selection.Copy
Workbooks("TestFORMS.xls").Sheets("Test").Activate
Range("Type").PasteSpecial Paste:=xlPasteValues
End Sub
Within the same sub, I would add additional lines of code to copy and paste the other ranges needed on the current worksheet.
My current hangup is that I get an error at Range("ACtype") as that is not the proper way to call the range.
There's more work to be done with the macro yet, like having it pull the data with TestLOG closed, which I believe is done by listing the path to filename.
Then I need to maintain cell formatting on the new table when the data is posted to it.
Hopefully the final task is to get this working so that when a new employee is selected in TestFORMS, the previous employees data is cut and the new posted, as the worksheet is just printed out for each employee and put on hardfile.
View 9 Replies
View Related
May 28, 2008
I have a named range within a sheet. It was pulled across when i performed a move/copy - create copy from another workbook. The name contains characters that cannot be displayed or recognised and so they are displayed as square
symbols. This causes me an issue as i cannot delete the name. Even if i paste the list names, i cannot copy/paste the name as the characters are not recognised. I also tried using a macro to delete all names, which did delete all names, with the exception of this one.
The VB code used was :
Sub Del_Names()
Dim myname As Name
For Each myname In ActiveWorkbook.Names
myname.Delete
Next myname
End Sub
View 9 Replies
View Related
Mar 17, 2014
Please see attached sample worksheet. Column A will be generated by the user manually.
I'm looking for a way in VBA to have A1:D20 in Sheet2 copied and pasted in the "Bank Reconciliation" Sheet based on how many "Markets" there are in Column A. Then, once that's complete to have A22:D30 (the smaller box in Sheet2) copied and pasted directly below those results.
I have what the macro would hopefully generate to the right in "Bank Reconciliation" (B6:E54) as an example. So if there's a market in A1, copy and paste the box to B6. If there's a market in A2, copy and paste the box directly below the first (B26) etc. etc. until it's done, then paste the smaller box directly below whatever the macro generates.
Book2.xlsx
View 3 Replies
View Related
Oct 31, 2008
how can i open a different path of the workbook in the listbox. if my listbox there only show the file name.
View 2 Replies
View Related
Sep 15, 2006
I am having trouble getting a macro to open a specific file from an open workbook in the same folder based on the current path of the open file. The application is this... I have two files in the same folder. The first is for data entry and it contains a macro button that will open the second which is a template to which the data in the first will be copied. I can easily do this with a fixed path to the second file, however, I would like to be able to move or copy the folder containing these files to other locations or computers without having to change the path each time. I would like to base the path to the second file minus the file name on the first (already open file) path. Assuming that the first file containing the desired macro button is already open, here is one of many of my attempts...
Workbooks.Open " ActiveWorkbook.PathJob Tracker -.xls"
Assuming the second file is the active workbook, I achieved a similar path assignment when saving the file...
ActiveWorkbook.SaveAs "Application.Path/Jobs Active/" & "Job Tracker -" & Range(" '[Job Tracker -.xls]Specifications'!$A$6").Value
View 2 Replies
View Related
Feb 16, 2010
I have a folder containing 40 single sheet excel workbooks and I would like to automate following tasks:
- Open each excel file (need to open the file so as to update it since it gets the data from another workbook through =formulas)
- Copy paste as values
- Save this as excel html in the same folder as original excel files (keeping the original file name)
- Close (original excel file should not be changed ie formulas should remain in place, only the html file will contain values)
- Since there will always be xHtml files with same name need the macro to replace the excisting file
My abilities with excel are limited to functions, no VBA knowledge other than finding ready codes and pasting them in the module.
Since this routine is to be run almost daily the macro should run all files, instead of one by one.
I just hope that I am not asking too much for excel to handle and I hope that explanation is clear.
View 9 Replies
View Related
Jan 8, 2013
I'm trying to use a macro that will copy & paste values from one workbook to another. Everything works fine until I try to get it to Paste to next available row.
It worked the first time, but after that it jumps, several hundred rows at a time.
This is what I have so far:
Code:
Sub CopyData()
Workbooks.Open Filename:="S:Production OfficeMonthly KPI Reports.xlsm", ReadOnly:=False
Windows("Weekly KPI Plan.xlsm").Activate
Worksheets("Data Input").Select
Worksheets("Data Input").[A4:AT650].Select
[Code]....
View 3 Replies
View Related
Sep 25, 2009
i facing another problem at here and do not know how to write the part of coding. it is pending for 2 weeks. i do not know how to explain so i attach the excel to explain. i hope can get any reply soon.
View 2 Replies
View Related
Dec 18, 2008
I need a macro that will copy a selected range from an existing worksheet in a workbook and paste the data to the last row in another workbook on a particular worksheet.
View 7 Replies
View Related
May 24, 2007
Is it possible to write vba code to copy the range A1:A20 from my workbook to a new workbook?
View 4 Replies
View Related
Jul 28, 2007
Copy and paste the values only from one workbook and a set of named ranges (Name1, Name2, Name3) but only the rows within the range were TRUE is found in column A - To a CLOSED workbook named Data / Sheet1 - located at C:NetworkTemplate starting with cell B10.
View 9 Replies
View Related
Aug 6, 2013
I need method, using a button, that looks at a cell--say EO2, for example--, looks back on a master worksheet at a specified row and range for a match, then looks at the information from a specified range below the matching cell (The information in this column will either be blank or have an "X" in the cell), and then those rows that do not have an "X" will be hidden in the corresponding rows in the working worksheet. Therefore, if at any time the value in "EO2" ever changes, then it will automatically find a new match and repopulate and hide information as before. About 130 columns will have its own button so that a "query" can be made that depends on the information in a particular cell in that column.
The master worksheet now has matrix of 287 rows and 58 columns. Each row is for an operating procedure and each column shows a job code. An "X" in a coordinate cell for a column/row shows whether that job code is responsible for knowing that operating procedure. So, on the working sheet, an employee's primary job code is given underneath his or her name. When the button is pushed, all the operating procedures not required for a given person will be hidden and only the required ones will remain visible--grouped, if you will. Qualification dates will be easier to see now that the information is consolidated. Whenever someone transfers to a new position, a new code will be inputed on the working sheet. When the button is pushed, a new grouping will result. Any operating instructions that overlap will still have qualification dates, so that information will not need to be transcribed.
View 9 Replies
View Related
Jan 24, 2014
how do i go about creating a macro to copy a range in sheet 1 in workbook A and paste that to a new tab(the last one) in the existing workbook B. i would like the tab to be renamed based on a cell value. preferably workbook B doesnt have to be open, and the save and close it
View 6 Replies
View Related
Sep 13, 2007
I am looking for either formula which will allow me to copy data from a workbook with multiple named sheets into a workbook with a single named sheet. For instance workbook 1 has sheets named bob, sue, and tom and there is a workbook 2 which only has sheet bob. I want to copy the range fo data from sheet bob in workbook1 to workbook 2 as long as workbook 2 has sheet bob.
View 2 Replies
View Related
Apr 10, 2014
I have a filtered worksheet -WB1 (filtered by Column "B"). I want to count the number of cells or rows in column "B"(Only the filtered ones) of WB1. Copy out that exact number of cells from another workbook(WB2) from the bottom moving up(Column "A") and paste it into WB1 column "I".
WB1 - Count Filtered Cell/Row with reference to Column "B"
WB2 - copy Cell count bottom to top of Column "A"
WB1 - Paste into Column "I"
View 5 Replies
View Related
Jun 29, 2007
I HAVE A SHEET WHERE USER ENTERS DATA, AND WHEN USER ENVOKES MACRO, THE SAID
DATA IS COPIED TO A 2ND SHEET WHERE IT IS STORED. NOW PROBLEM IS THAT IT COPIES
DATA OVER PREVIOUS DATA, BECUASE THE REFRENCE IS NOT DYNAMIC. WHAT I NEED THAT THE DATA IS COPIES EVERY TIME TO THE NEXT ROW,
View 5 Replies
View Related
Apr 5, 2008
I can copy one range to another with:
Rng1.Copy Rng2
How can I modify this so I only paste over the values (not formulas)?
View 4 Replies
View Related
