Macro to paste selected data to new book

[Guest]

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!

 

[Guest]

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.