Copy Selected Range To A New Workbook

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


Copy Selected Range To New Workbook

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

Copy And Paste Selected Range Into Another Workbook Based On Headings And Next Empty Blank Row

May 14, 2014

I have a worksheet with some info that is filled in from other employees. I have Customer Name, Date of Oldest Invoice, Avg. Days Beyond terms in column N42:N44, with the data in column M42:44. I want to copy and paste that into another workbook, I need to transpose the data and then paste it into the next blank row. IF POSSIBLE, I would like to have some of the column headings the same as the above fields, and I would like to paste the data into the correct column heading.

Currently I have the code listed below. It copies the data, opens the spreadsheet, (this is where it fails) find the next blank row and transpose and paste the data. It does transpose and paste the data but it does not look for the next blank row, instead it just transposes and pastes the data in the last cell that was active when the work book was closed.


Private Sub CommandButton1_Click()' Paste Macro
' Macro recorded 5/14/14

Workbooks.Open Filename:="L:Financial Services! FRA !! Preston !3rd Party Collections Accounts 2014.xls"

[Code] ..........

View 8 Replies View Related

Copy Selected Sheets To New Workbook

Jan 25, 2009

I have a workbook with about 25 sheets. All the sheets are named. I'd like either a Macro or some VBA code whcih, when the user clicks a button will Unhide selected sheets, say "Equip Labor", "Equipment", Proj. Summary" and "Implementation Guide" and then copy these sheets into a new workbook created on the fly (Book1.xls) and then in the original workbook re-hide all the selected sheets. This way the user can save the new workbook as whatever name they want.
Is this possible? The closest post I could find was this:
I tried to modify this, but I'm not that good yet with VBA.

View 9 Replies View Related

Copy Columns From One User Selected Workbook To Another

Apr 27, 2014

I am using this code to copy columns C,D, and P from a user selected workbook into position C,D,E of workbook "Checklist.xlsx" (the one from were I run the VBA code). I don't have a clue about VBA, just have been gathering info, but the code works pretty fine. The only issue is that it opens twice the workbook "wbExt" hence the system prompts me if i want to reopen the already open workbook.

Some questions:

1) Is there a way to copy the 3 columns at once? something like "Columns("C:D, P").Select".
2) Is there a way to select the workbook from were to copy the column without needing it to actually open?
3) is there a way to paste the columns starting from row 3 instead of row 1 (Range("C1").Select)?

View 2 Replies View Related

Copy Selected Data To Workbook On Desktop..

Oct 13, 2009

As the buyer edits orders, I have a macro which pulls out all the parts with changes and puts them on a sheet named "Changes" in the attached "Sample" workbook. Since the buyer will do dozens of these edits a day, I'm trying to create a simple way for them to create a list of parts they may want to try and buy from another source.

I would like to add a macro that would open a message box telling the buyer to select the parts they want to copy. They could select whatever rows they want to copy to the "Backorder List", and select "OK" or hit "Enter", and the macro would open the workbook named "Backorder List.xls" on their desktop, (mine is located under "C:Documents and Settingsjerry.hutchesonDesktop"), and copy the selected rows to the workbook, then save changes and close the "List" workbook. It should only paste the values, to preserve whatever formatting is in the book being copied TO, and it should copy below any data already in the workbook.

To make things even more difficult, the macro would also need to copy the Vendor # and PO # from the top of the "Sample" workbook, (cells B2 and B3 respectively), and enter that data beside the other data just pasted beside the parts data in the backorder list. (I have entered sample data in the backorder list to show what I mean.)

View 2 Replies View Related

Copy Selected Worksheets As Values To New Workbook

Jun 22, 2008

I have a workbook with 30 some sheets. I would like to have a macro that if i select multiple sheets, will paste the values and formats of those sheets into a new workbook. I would like this to keep the names of the worksheets when transfered to the new workbook.

Thus far, I have been using this code, which does nearly everything i want, but instead of pasting the values, it gives me #VALUE! for nearly all the cells. Most of the cells are using Vlookup and/or Indirect functions to reference other sheets- not sure if this is relevant. The few cells that do paste accurately are either text or simply reference another cell on a different worksheet without a function.

Here's my code.

Sub PasteShtVal()
Dim w As Worksheet
For Each w In ActiveWorkbook.Sheets
With w.UsedRange
.Value = .Value
End With
Next w
End Sub

View 9 Replies View Related

Copy Current (Selected) Sheet To New Workbook And Save As Tab Name

Feb 28, 2014

I want to take the current sheet and copy to a new workbook (keeping the tab name) and then save as the tab name (xlsl) and close.

I've found ones that do a Saveas box or ends up saving as "Sheet1," "Sheet2" etc but can't seem to find one that keeps tab name and saves.

Here is the one I was using, but it saves all tabs not just selected:

Sub CopySheet()
Dim fname As Variant, ws As Worksheet, wb As Workbook
Dim nCol As Integer, iCol As Integer


View 1 Replies View Related

Copy All Selected Sheets (data / Formats / NOT Formulas) To New Workbook

Oct 2, 2013

I had a macro on my previous laptop that worked, but didn't have the macro backed up so it was lost.

I need a vba macro that will copy all active/selected worksheets into a new workbook. Included requirements:

* Maintain tab names

* Only bring in contents/data with all formats (including logo image, but NOT formulas or hidden rows/columns)

* Keep the file name the same except adding "- FINAL" to the end

View 3 Replies View Related

Why Won't This Selected Range Copy To Another Sheet?

Feb 23, 2007

I have a button on the Players worksheet that has this
Why would it not work?

Range("NewData").Range("B5:B" & Cells(Rows.Count, "B").End(xlUp).Row).Copy
Sheets("Players").Range("A3").PasteSpecial Paste:=xlValues

I am trying to copy Column B starting at B5 down to the last used cell in column B. into the players sheet starting at cell a3.

I try it and it highlights 4 cells and that is it???

View 9 Replies View Related

Copy Range And Paste Depending On The Selected Checkbox?

May 30, 2014

Im looking for a macro that can get a cell RANGE, the data in the RANGE will be copied then if the checkbox is selected it will copy the same data in the RANGE and add the selected checkbox in next column

I have column A and Column B < this is my selected range


I will have a form that has checkbox and buton.

When all the checkbox is selected. when the button is click. the result will be.

View 1 Replies View Related

Copy Selected Range & Save To Text File

Mar 4, 2010

I also use VBA in a terminal emulation program that I use the following command to 'screen scrape' the current display memory and quickly Save it to a .txt file:

.SaveDisplayMemory "C:File1.txt", rcOverwrite
What I want to do in Excel is, Copy the current selected Range() and Save it to a .txt file.

I know how Save the current Sheet to a .txt file, but can't figure out how to Save just a Range() of cells.

View 9 Replies View Related

Copy Lines Depending On Agent Selected And Date Range

Feb 3, 2010

I am having issues with this Macro since I have to run it on computers only supporting Excel 2003 and computers with Excel 2007. The Macro in use is as follows:

View 9 Replies View Related

Range Copy: Differ Depending On The Active Cell That's Selected

Feb 22, 2009

The rows will differ depending on the Active Cell that's selected and I don't know how to specify this.
The range I want to copy is from Column B to DA on the worksheet ("Staff") which I want to paste to another worksheet ("Leavers"). This is as far as I got

'FindRemove = lstRemove.Value
'If FindRemove = "" Then End

' Goes to the start of the Data column

' Tests current cell against FindRemove
'If ActiveCell.Value = FindRemove Then
'Call CopyPerson
'Exit Sub
'Else: ActiveCell.Offset(1, 0).Select
'End If
'Loop Until ActiveCell.Value = ""
'End Sub

View 9 Replies View Related

Copy Rows To New Sheet Based On Date Range Selected On A Form

May 8, 2009

This spreadsheet must perform calculations for every line since new items are added every day, so VBA is probably better than copying formulas down every line of the spreadsheet:

With the press of a button, I need to be able to select a range of dates and copy all lines within the range to a seperate sheet with the desired name under the same headings they currently reside under. I have included some modified code that is being used in another spreadsheet that was created for me, but I do not pretent to understand all of it and I no longer work with the creator of the spreadsheet. How do I use a button to open the form for date selections and entering the name of the new sheet, and then use the start button on the form to begin the matching and copying to a new sheet? If there is an easier way I am all for that too.

Also, I need to keep a total of all items by month as well as a monthly and annual average of the Total Item Value on the FY09 tab. This will eventually produce another sheet when a button is pressed to submit as a report. I think part of this answer is in using the MONTH(serial_number) function but I can only get this to work for a single cell. I need to search the entire Distribution 'D' column, match all the months to the FY09 tab to the respective month, and calculate the totals and averages. I think SUMIF may also be needed as well but need the MONTH(serial_number)to work first. If there is a way to code all of this in VBA that would be fine as well.

I have a pivot table on Sheet1 where I am trying to get the totals and averages described above but I am not sure it can do what I need. In column 'B' I need the total number of each item as well as the total number of all items. I tried various formats and adding the totals from the Totals tab but I have not figured it out.

View 7 Replies View Related

Formula To Copy Lines Depending On Date Range And Agent Selected

Jan 30, 2010

What I would like to do with the information that I have put together on a different workbook is to reference an Agent (column A), and a time range (Date is in column D) and then copy the row from A to I for each occurrence during that range of dates so we can see how many and what errors have occurred during any range of dates selected. (I will have different people inputting errors at different times so the dates will not be sorted unless they have to be). I want to be able to select different agents/date ranges to be able to get a glance of how that particular agent did during this time frame.

Here is an example of the work sheet where the data is originaly.


Here is an example (in a different workbook) of what the sheet should look like if I was searching errors for Albert Ozier between 1/25/2010 and 1/28/2010.


View 9 Replies View Related

Add Range Names To List Of Selected Cell & Copy Paste The Chosen Name

Mar 21, 2009

I am carrying out some sensitivity testing on a model, and would like to greatly increase the speed of the process by being able to call in from a user defined cell a named range, which is then pasted to the right of the cell.

The attached file steps through the process I have in mind.

View 9 Replies View Related

Copy Data From Workbook / Open Existing Workbook - Select Range And Paste

Mar 26, 2012

Copy data from workbook, open existing workbook, select range and paste. But my copied data is lost.

Sub Select_Copy_Paste()

[Code] .........

' Here i need to do something to paste data into r.address?

View 4 Replies View Related

Copy A Range Of Cells In Another Workbook Opened In A Separate Instance Of Workbook

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

Search Cell Value In Another Workbook And Copy Range From Current To Other Workbook

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

Autofilter Another Workbook And Copy Range Into Current Workbook

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

Copy Range To New Workbook & Close Existing Workbook

Jun 30, 2008

I have a very brief question

dlgAnswer = Application.Dialogs(xlDialogOpen).Show
Windows("Derivative YK pricing Mod G.xls").Activate
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False

As you can see, I open a workbook with the dlgAnswer, I was wondering how I could close that opened workbook

Currently I have


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

Excel 2010 :: Copy Range Of Cells From One Sheet To Another Depending On Option Being Selected From Dropdown Box

Aug 13, 2012

Using macro's on Excel 2010. What I'm trying to do is create a macro that will copy a range of cells from one sheet to another depending on a option being selected from a drop down box. I've tried to use formulas but without success.
sheet 1 contains a list of approx 20 people with rows containing sales figures per week.

Is it possible to have a macro that will copy the rows to sheet2 depending on the dropdown? The drop down has already been setup with people's names

if sheet 1, cell a1 (with data validation setup) dropdown contains "mr smith", copy sheet2 row A1:A9 to sheet 1 cell a2
if sheet 1, cell a1 dropdown contains "mr cooper", copy sheet2 row B1:B9 to sheet1 cell a2
etc... for each name in the dropdown

The idea is so that i select a dropdown and it copies the sales figures that match the dropdown name, if i then select another dropdown, the corresponding figures are copied to the same place.

View 3 Replies View Related

Copy Multi Cell Range To Destination Workbook Range

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

Copy A Selected Range Of Cells On Sheet One To A Range On Sheet Three

Aug 7, 2008

Hi. Does anyone know a formula to copy a selected range of cells on sheet one to a range on sheet three when a check box in checked. Ex. copy range a4:j4 on sheet one into a4:j4 on sheet three once the check box for on sheet one is checked?

View 9 Replies View Related

Fill Range Of Cells With Text When Listbox Option Selected - Clear When Not Selected

Jul 25, 2014

I am using this code to hide or unhide rows of text on another sheet:

Sub ProcessSheet1ChangeOnCellJ7(ByVal Target As Range)

Dim sAddress As String
Dim sValue As String

'Get the address of the cell that changed without '$' signs
sAddress = Target.Address(False, False)


When the "Not Pursuing" list box option is selected (in cell "J7" or "J8" in Sheet 1) I need to add (or over-write) "Not Pursuing" to the range of cells in column "B" (in the "Tasks" sheet), but only for that particular Goal, meaning a limited range of cells in column "B". If the "Pursuing - Show All Tasks" option is selected for a Goal then these same cells need to be blank so that the appropriate person can enter their name into the cell.

The purpose for adding "Not Pursuing" automatically to these yellow highlighted cells is that it will facilitate filtering of tasks by individual in the "Tasks" sheet..

Again I have tried several times to upload a sample file and am unable to, which I know makes it more difficult to solve. (Is there some common mistake people make? I know it's an allowed format and is very small in file size....)

Code solution can be entered directly beneath:

If Target.Value = "Not Pursuing" Then
ActiveWorkbook.Sheets("Tasks").Rows("29:29").EntireRow.Hidden = False
ActiveWorkbook.Sheets("Tasks").Rows("30:48").EntireRow.Hidden = True

View 1 Replies View Related

Copy Range From Workbook To Workbook

May 4, 2006

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

Prompt Open Closed Workbook Then Copy Range And Paste Special Into Open Workbook

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


View 7 Replies View Related

Copy Range To Different Workbook

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
********Application.CutCopyMode = False
********DestBook.SaveAs ("E:WIPIWS WIP.xls")...........................

View 9 Replies View Related

Copy Range To Another Workbook

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

Copyrights 2005-15, All rights reserved