Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to paste selected data to new book
I need a macro that will enabel me to perform the following function:
1. Paste selected data (particular range of cells) from worksheet A (residing in workbook A) to a new Book 1 worksheet. 2. Paste selected data (particular range of cells) from worksheet B (residing in workbook A) to the same Book 1 worksheet. This data will be pasted on the next available empty row below the data in step 1. 3. Continue to paste data from worksheet 3 (and on) to next available row in Book 1. Thanks in advance for your help! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to paste selected data to new book
Since I cannot see your workbook and cannot guess what your criteria for
selecting the sheets to copy from nor the data to copy, I wrote this so that you can fill in the blanks, so to speak. There are two input boxes that allow you to enter, first, the sheet name and, second, use the mouse to select the range to copy. Then a message box will ask you if you have more, so you can repeat the process until you finish all the sheets in your workbook. If I had more information on the criteria for doing the copying, I probably could have avoided using the inputboxes altogether. But this will save a little effort. Sub CpyToNewWB() Dim myRng As Range Set wb1 = ActiveWorkbook Set NewWB = Workbooks.Add NewWB.SaveAs Filename:="NewBook.xls" wb1.Activate AGAIN: lr = Workbooks("NewBook").Sheets(1).Cells(Rows.Count, 1).End(xlUp).Row mySht = Application.InputBox("Enter name of sheet to copy from.", _ "SHEET NAME", Type:=2) Set myRng = Application.InputBox("Select the range to copy.", _ "RANGE TO COPY", Type:=8) x = myRng.Address Sheets(mySht).Range(x).Copy Workbooks("NewBook").Sheets(1).Cells(lr + 2, 1) cont = MsgBox("Do you have another range to copy?", vbYesNo + _ vbQuestion, "COPY ANOTHER RANGE?") If cont = vbYes Then GoTo AGAIN: End If End Sub When you copy this over, be sure to watch out for line wrap errors. "tomhelle" wrote: I need a macro that will enabel me to perform the following function: 1. Paste selected data (particular range of cells) from worksheet A (residing in workbook A) to a new Book 1 worksheet. 2. Paste selected data (particular range of cells) from worksheet B (residing in workbook A) to the same Book 1 worksheet. This data will be pasted on the next available empty row below the data in step 1. 3. Continue to paste data from worksheet 3 (and on) to next available row in Book 1. Thanks in advance for your help! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to copy & paste-special-values data to selected worksheets | Excel Discussion (Misc queries) | |||
automate a cut and paste of data from one work book to another | Excel Discussion (Misc queries) | |||
I wish automate a cut and paste of data from one work book to ano | Excel Worksheet Functions | |||
Importing Selected Source Book Data | Excel Discussion (Misc queries) | |||
Create new book..paste data..save..close | Excel Programming |