PDA

View Full Version : Copy range in worksheet into new workbook - input box



aleszib
06-10-2018, 10:07 AM
Dear All,

Can someone help me:

1. How do I have to change my code that it will copy data from active worksheet to new workbook, which I would like to name with value inserted into input box?




Sub copyTableIntoNewWorkbook_Sheet1()
' locate the dynamic range / table
Dim rngTable As Range
With ActiveSheet.[b2] ' top left cell of the dynamic range
Set rngTable = .Resize(Range(.Offset(0), .End(xlDown)).Rows.Count, _
Range(.Offset(0), .End(xlToRight)).Columns.Count)
End With
' create new worksheet
Dim wbNew As Workbook
Set wbNew = Workbooks.Add
' copy table to new worksheet
rngTable.Copy wbNew.Sheets(1).[a1] ' top left cell where to copy the table to
End Sub


2. How do I have to change my code that I can copy more sheets from active workbook to another workbook?

Thank you in advance!

Logit
06-10-2018, 08:19 PM
.
Here is an excellent resource for creating a new workbook and copying data to it :

https://ccm.net/forum/affich-720350-excel-a-macro-to-create-new-workbook-and-copy-data


This macro will copy all sheets in a workbook and paste them in another workbook, after any existing sheets :



Sub CopyWorkbook()


Dim sh as Worksheet, wb as workbook


Set wb = workbooks("Target workbook") 'Replace "Target workbook" with the name of the other workbook
For Each sh in workbooks("source workbook").Worksheets 'Replace "source workbook" with the name of the workbook being copied
sh.Copy After:=wb.Sheets(wb.sheets.count)
Next sh


End Sub