| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro independent of newbook name
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro independent of newbook name
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" wrote: 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro independent of newbook name
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro independent of newbook name
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" wrote: 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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro independent of newbook name
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro independent of newbook name
How about trying:
Range("A1:U58").Copy wkbNewBook.Activate Sheets(1).Select ActiveSheet.Paste Pflugs "HA14" wrote: 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Independent column widths | Excel Worksheet Functions | |||
| Independent Lists... | New Users to Excel | |||
| independent macros | Excel Programming | |||
| Run a .xla as a independent application | Excel Programming | |||
| can one cell contain more than one independent number | Excel Discussion (Misc queries) | |||
Hybrid Mode
