Copy Range & Transpose To Another Workbook
Oct 17, 2007
Copy a specific row of column summations (row to be copied varies by # of entries for the month) from each sheet in the workbook into sequential columns on the 1st Sheet. For example, copy Sheet2Row103 columns A thru O and transpose to Sheet1 column “A” rows 1 thru 15, Sheet 2Row56 column A thru O and transpose to Sheet 1 column “B” rows 1 thru 15, etc.). Excel fails to identify the Sheet Number/ Name if you use copy, transpose and paste, it only works if the transpose is performed on the same Sheet!
View 4 Replies
ADVERTISEMENT
Oct 22, 2007
I have a workbook with data in it that I want to copy and put in another wookbook. The data looks like this.
D11/22'2005
CX
T-7.80
PEl Azabache
LDining Out
(blank cell)
D11/22'2005
CX
T-9.50
PAce Hardware
LHousehold
(blank cell)
What I want is a macro to copy that data until it finds a blank cell and paste/transpose that data in another workbook and then get the next set of data until an other blank cell.
It should look like this when done.
D11/22'2005CXT-7.80PEl AzabacheLDining Out
D11/22'2005CXT-9.50PAce HardwareLHousehold
View 7 Replies
View Related
Apr 17, 2008
I have a long header I am pasting to a column "A" on a new Sheet. When the header pastes it is repeating itself but with long blank spaces in between each repeat. The first time goes from A1 to A152, which is all I want. But it shows up again starting at A180225, and again at A212993, and again and again. I only want a single instance of the header in Column A. Here is the macro I am using:
Sub Sort_Cells()
Rows("1:1").Select
Selection.Copy
Sheets.Add after:=Sheets(Sheets.Count)
Sheets("Sheet1").Select
Columns("A:A").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
True, Transpose:=True
Selection.Columns.AutoFit
Range("B1").Select
End Sub
View 4 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
Feb 11, 2010
I m not able to use the standard Excel Paste Special function when I copied a range of cells in another Excel workbook opened in a separate instance of Excel. Instead, Paste Special thinks that I have copied some non-Excel objects and gave me the Paste As options. This is not the case if I open both workbook within the same instance of Excel. Could you share with us if there is a trick to trigger the normal Paste Special options in such situations (without having to invoke Macro procedures)?
View 2 Replies
View Related
Mar 11, 2014
I have 2 workbooks. 1 is where data is entered called wksPB, the other is like a database workbook. The wksPB has data to be feed from column B to F and there is a dropdown.
what im looking to do is, if the combobox value is selected as Decline then it should display a messagebox that reference value in cell B is denied. if combobox value is Agreed then the macro should search the database workbook for the text entered in column b of wksPB and then copy data of C:E of wksPB to H:J of database sheet where that text is found and also the approver name in wksPB C24 to the approver cell of where that text was found. I've reached till finding the text but what I get my head twiting on is how to copy the text from wksPB column B:F to database sheet column H:K. Im attaching the sample workbooks and the code where ive reached till.
[Code] .....
Attached Files
Replacement Records - 2014.xlsx‎
forum file.xlsm‎
View 1 Replies
View Related
Mar 2, 2014
I'll soon have an ETL process that will load about 150K rows into an Excel workbook. On of the columns will be the end user's userid.
I need to autofilter that external workbook based on the end user's userid, copy that range, clear a worksheet on the current workbook, and copy that range to the current worksheet.
For example, see the attached workbooks. I need to replace the data in Source.xlsb!Cases with the data in Output.xlsb!Case_List, filtered on my userid, which we'll call foo.
View 1 Replies
View Related
Jun 30, 2008
I have a very brief question
dlgAnswer = Application.Dialogs(xlDialogOpen).Show
Selection.Copy
Windows("Derivative YK pricing Mod G.xls").Activate
Columns("B:B").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Range("C5").Select
Application.CutCopyMode = False
Windows("EXPORT1.xls").Activate
ActiveWindow.Close
As you can see, I open a workbook with the dlgAnswer, I was wondering how I could close that opened workbook
Currently I have
Windows("EXPORT1.xls").Activate
ActiveWindow.Close
however the workbook will not always be called export1.xls, I want it to close whichever one I opened earlier.
View 9 Replies
View Related
Dec 23, 2008
Trying to copy a range from Sender.xls (sheet) Lists backstage
onto Userform.xls (sheet) Behind the Scenes
When trying to copy the values within a multicell range, the destination cell range (same size) becomes blank.
View 2 Replies
View Related
May 4, 2006
Background
Excel 2000. I would like to be able to copy a range on worksheet 1 in workbook 1 to a range in worksheet 2 in workbook 2.
Walk - Task described above with all fixed identities.
Double- Can the task be performed using variables (sheets(variablee)) for the worksheets and ranges?
Home Run - Can task be done with variables in workbook, worksheet, and range? Or just recommend a book or link that can get me there.
View 4 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
Jun 10, 2007
I am trying to copy a small range from one sheet in a workbook to another sheet in a different workbook. My ranges are spelled out in the SrcBook and the DestBook in the code below:
Sub CopyData()
Dim SrcBook As Workbook
Dim DestBook As Workbook*
****Application.ScreenUpdating = False
****Set SrcBook = ThisWorkbook
***
*** '*On Error Resume Next
****Set DestBook = Workbooks.Open("E:WIPIWS WIP.xls")
*** '*If Err.Number = 1004 Then
********Set DestBook = Workbooks.Add
********SrcBook.Worksheets(1).Range("D4:D6").Copy
********DestBook.Worksheets(1).Range("C10").PasteSpecial.Values
********Application.CutCopyMode = False
********DestBook.SaveAs ("E:WIPIWS WIP.xls")...........................
View 9 Replies
View Related
Oct 5, 2009
I want to copy a range (Columne J: first row to rows.count) from one workbook to another workbook which already exists. The target range is always different. The code I have is:
So far it only copies ONE cell, but even that doesn't work. I would need the whole range to be copied!
Sub FinalImport()
Call WhatRows 'to get the intZeile variable (number) via a inputbox
Workbooks("import.txt").Sheets("Sheet1").Range("J" & Rows.count).Copy _
Workbooks("Air Distance Table.xls").Sheets("Sheet1").Range("D" & intZeile)
End Sub
View 9 Replies
View Related
Oct 4, 2007
This happened because I mentioned worksheet instead of workbook. The purpose of my code is as below:
1. Add new workbook (say wb2)
2. Activate the workbook wb2
3. Copy a predefined range from already open workbook (say wb1)
4. Select the target cell in wb2 through user prompt by using mouse or keyboard
5. Paste the copied range from wb1 at the selected location of wb2
I am facing problem in selecting the cell in wb2 as wb1 always remain in foreground and new workbook does not come to foreground even I click on that.
What I wanted to mean by user form is that before the above code runs, another code runs that brings up one user form. In that user form user can select if he/she wants to copy the selected range to a new workbook or an existing workbook. Suppose user selects new workbook. This time the inputbox comes and asks user to select the target cell of the new workbook. The input box pops up but I can not select the cell in new workbook as the user form always stay there and does not allow the second work book to come in front.
I ran the above code seperately without running the user form code and it worked perfectly. Hence I would like to know how to deactivate the user form
View 5 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
Oct 19, 2009
I have a currently working macro to copy the current sheet as values and create a new workbook in the same folder destination. That code is as follows:
View 10 Replies
View Related
Mar 30, 2009
I have a code that will allow me to copy three worksheets from an open workbook to a new workbook. That works great, but I only need only a selected range from each of these worksheets to be copied (Range A1:AV60). Here is the code I use to select and copy the worksheets, how do I add a range within this code for each worksheet.
Sheets(Array("Req Page 1", "Req Ext 1", "Req Ext 2")).Copy
View 9 Replies
View Related
Dec 17, 2009
I am using command buttons to perform various macros.
I have worksheetA with a defined range of cells of information that i sometimes want to copy to the index file. I click the command button to run the macro that copies the range to the next available row in #Index.xls
How can i change the following macro to only copy the values, and NOT the formatting?
Also, why does this particular macro cause the Command Button to remain stuck in the pressed position? It is the only command button triggering a macro that actually stays pressed, requiring my to click it again to depress it.
here is the macro i have so far:
-----------------------------------
Sub copy_to_index()
Dim bk As Workbook
Dim bSave As Boolean
Dim lRow As Long
' test to see if #Index.xls is already open
On Error Resume Next
Set bk = Workbooks("#index.xls")
On Error GoTo 0
If bk Is Nothing Then...........
View 9 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
Sep 13, 2006
i have 2 workbooks attached, in the "work order masterlist 2006.xls" workbook, i'm trying to compare column F which is the R date to see if it matches the dates in the "2006 Calendar.xls". if it's the same date, i would like to copy from "work order masterlist 2006.xls", column C, which is the Client's name to "2006 Calendar.xls" under its appropriate date, assuming that:Dates of the months will not change column/row in the "2006 Calendar.xls"it will be an added bonus if i can copy and paste the description as a comment...
View 3 Replies
View Related
Sep 30, 2006
I need to copy the same data range from Sheet 1 of different workbooks in the same subdir to output.xls (also in the same subdir).
In workbook output.xls, I've defined the range of workbooks (source files) as name filenames. I need to copy the same data range (say B8:R8) from each source file to the cells adjacent to the filenames in output.xls.
Eg. I have defined the range of files to copy data from as filenames in Col A of Sheet 1 in output.xls.
Col A
sales_10-09-06
sales_10-10-06
sales_10-11-06
Workbook sales_10-09-06 contain sales data of various product to different firms as below:
Col A Col B Col C .................................. Col R
prod A prod B prod q
Firm 1 50 20 100
Firm 2 20 25 80
Firm 3 30 50 20
How do I copy B8:R8 of sales recorded for each day in sales_date.xls to Col B of Sheet 1 in output.xls? There are 200 files (specified by the name filenames in output.xls) that I need to copy B8:R8 from.
View 9 Replies
View Related
Sep 13, 2007
I am trying to create a method to select the values of contiguous range selections on excel worksheets.
Users will be making new worksheets in new workbooks out of the data from the old sheets.
Currently users make a selection is and copy it to a new worksheet.
Is it possible to retrieve from the 'clipboard', the values from the associated cells?
And then to parse them into columns in a new worksheet?
Earlier I tried this bit of ( it didnt work )
Sub AddNew()
' for passing highlighted text into the form so that I can pass it
'to a new workbook.
Dim PassData1 As String
Selection.Value = PassData1
Set NewBook = Workbooks.Add
With NewBook
.Title = "xxx"
. SaveAs Filename:="xxx.xls"
End With
Range("A1").Value = PassData1
End Sub
View 9 Replies
View Related
Aug 3, 2008
I put the following codes in the module of Workbook BS. The codes successfully opened the workbook of 2008. Unfortunately, nothing was pasted to Workbook BS of sheet2. let me know any things missing from my codes.
Sub COPY()
Dim wb1 As Workbook
Dim wb2 As Workbook
Dim rng As Range
Set wb1 = Workbooks.Open("C:Documents and SettingsuserMy Documents20082008.xls")
Set wb2 = Workbooks.Open("C:Documents and SettingsuserMy Documents2008BS.xls")
wb1.Worksheets("S").rng("B:B").COPY wb2.Worksheets("Sheet2").Range("A:A").End(xlToLeft).Offset(, 1)
End Sub
View 3 Replies
View Related
Jun 17, 2014
Simple transpose question: Lets say I have a verticle range of data from B3:B13. I wish to have code that will transpose that data into a horizontal range at D3:M3, is that possible?
View 5 Replies
View Related
Jun 9, 2014
I have 400 source files containing (among others) 8 sheets with daily results: "Fri 23", "Mon 26", "Tue 27", "Wed 28", "Thu 29", "Fri 30", "Sat 31 (if applicable)", "Mon 2".
Each sheet contains also:
State - D1
Role - D2
Staff ID - D3
Date - D4
Activity group name in column A (starting from row 8)
Activity type in column B (merged with C and D) (starting from row 8)
Activity time in columns E:GV (starting from row 8). Usually, there is none or only one value in whole range (e.g. E8:GV8). But sometimes there are two values.
Customer ID in row 6 (value appears only if time was reported in E:GV range)
CC Number in row 7 (value appears only if time was reported in E:GV range)
It's all about transferring values from all daily sheets in all files (.xls) sitting in folder C:WADFinal to one simple table (WAD_Consolidation_file.xls, sheet "Consolidated") consisted of 9 columns: Staff ID, Role, State, Date, Activity Group, Activity Type, Minutes, Customer ID, CC Number.
View 1 Replies
View Related
Feb 16, 2014
VBA code below. My aim is to be able to copy a range from one workbook to another and I get the Run Time Error 438 on the line colored red.
Can this code be corrected or is there an easier way to get this done?
[Code] .....
View 8 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
Nov 3, 2013
I need to copy a range of cells from one workbook paste into another and email that new work book.
View 3 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
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
