Macro independent of newbook name

[HA14]

I have a macro which should copy one sheet to a new book and then I
jump back and copy another sheet to the same new book. My problem how
do I make my macro independent of file names of the file I am copying
from and to?

I have called the file I am copying from ThisWorkbook but what should
I call the file I am copying to, mentioned as "book1" in the macro
below:

ThisWorkbook.Activate
Sheets("A&S kvt").Select
Range("A1:U56").Select
Selection.Copy
Windows("book1").Activate
Sheets("Sheet2").Select
ActiveSheet.Paste

Can someone help?

From
HA14

 

[Guest]

If you are always going to copy the sheets to a new workbook, create a
workbook object. Then your code becomes this:

Dim wkbNewBook As Workbook
Set wkbNewBook = Application.Workbooks.Add

ThisWorkbook.Activate
Sheets("A&S kvt").Select
Range("A1:U56").Select
Selection.Copy
wkbNewBook.Activate
Sheets("Sheet1").Select
ActiveSheet.Paste


HTH,
Pflugs

 

[HA14]

Thank you, this did some of it but unfortunately not all. Can you help
me one more time? I want to copy three different sheets from this
workbook to the same new workbook. The problem is now first sheet
copies into one new workbook but the two other sheets copy into a
second new workbook - how should I fix this?

Dim wkbNewBook As Workbook
Set wkbNewBook = Application.Workbooks.Add

Range("A1:U58").Select
Selection.Copy
Workbooks.Add Template:="Workbook"
ActiveSheet.Paste

ThisWorkbook.Activate
Sheets("A&S kvt").Select
Range("A1:U56").Select
Selection.Copy
wkbNewBook.Activate
Sheets("Sheet2").Select
ActiveSheet.Paste

ThisWorkbook.Activate
Sheets("SOL kvt").Select
Range("A1:U30").Select
Selection.Copy
wkbNewBook.Activate
Sheets("Sheet3").Select
ActiveSheet.Paste

Great if you can help again.
HA14

 

[Guest]

Just add a second workbook object. Then copy the second two ranges to that
workbook.

Dim wkbNewBook As Workbook
Set wkbNewBook = Application.Workbooks.Add
Dim wkbNewBook2 as workbook
set wkbNewBook2 = application.workbooks.add

Range("A1:U58").Copy
wkbNewBook.sheets(1).activate
ActiveSheet.Paste

ThisWorkbook.Activate
Sheets("A&S kvt").Select
Range("A1:U56").Copy
wkbNewBook.Activate
Sheets("Sheet2").Select
ActiveSheet.Paste

ThisWorkbook.Activate
Sheets("SOL kvt").Select
Range("A1:U30").Copy
wkbNewBook2.Activate
Sheets("Sheet3").Select
ActiveSheet.Paste

Hope that helps,
Pflugs

 

[HA14]

Thank you for the coding, just want to let you know I want it all
copied to the same new workbook and therefore the macro now reads:

Dim wkbNewBook As Workbook
Set wkbNewBook = Application.Workbooks.Add

Range("A1:U58").Copy
wkbNewBook.Sheets(1).Activate
ActiveSheet.Paste

ThisWorkbook.Activate
Sheets("A&S kvt").Select
Range("A1:U56").Copy
wkbNewBook.Activate
Sheets("Sheet2").Select
ActiveSheet.Paste

ThisWorkbook.Activate
Sheets("SOL kvt").Select
Range("A1:U30").Copy
wkbNewBook.Activate
Sheets("Sheet3").Select
ActiveSheet.Paste

Hmm, I keep having the problem that nothing is actually copied from
the first sheet which I cannot figure out why since the area A1:U58 is
correct. The two other sheets copy in fine to the new workbook -
strange....

Thanks again
HA14

 

[Guest]

How about trying:

Range("A1:U58").Copy
wkbNewBook.Activate
Sheets(1).Select
ActiveSheet.Paste

Pflugs

 

[HA14]

So strange still nothing copying in. I will find a solution somehow.
Thanks for your help on adding right coding on workbook.

From
HA14

 

[Guest]

Try this code:

Sub work()
' Note: You will likely have to change the
' name of the sheets to the actual names in
' your workbook.

' Create new workbook
Dim wkbNewBook As Workbook
Set wkbNewBook = Application.Workbooks.Add

' Copy first range
ThisWorkbook.Activate
' Change this name as appropriate
Sheets("Sheet1").Select
Range("A1:U58").Copy
wkbNewBook.Activate
Sheets("Sheet1").Select
ActiveSheet.Paste

' Copy second range
ThisWorkbook.Activate
' Change this name as appropriate
Sheets("sheet2").Select
Range("A1:U56").Copy
wkbNewBook.Activate
Sheets("Sheet2").Select
ActiveSheet.Paste

' Copy third range
ThisWorkbook.Activate
' Change this name as appropriate
Sheets("sheet3").Select
Range("A1:U30").Copy
wkbNewBook.Activate
Sheets("Sheet3").Select
ActiveSheet.Paste

Application.CutCopyMode = False

End Sub

It worked for me.

Pflugs

 

[HA14]

Thank you for keep helping me. In the meantime I found a great coding
doing another search. This one works perfect:

Application.ScreenUpdating = False

Dim NewFileName As String
Dim StartWkBk As Workbook

Set StartWkBk = ActiveWorkbook

NewFileName = InputBox("Enter file name to save overview of
Divisions per quarter (ROAD kvt, A&S kvt and SOL kvt) in separate
file: ")

With StartWkBk

.Sheets(Array("ROAD kvt", "A&S kvt", "SOL kvt")).Copy

ActiveSheet.Select
ActiveWorkbook.SaveAs Filename:=.Path & "\" & NewFileName & ".xls"
End With

Application.ScreenUpdating = True
End Sub

From
HA14