Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to copy a one worksheet in one book to the current workbook
Good Morning; I posted the question in the subject line, to which I received quite a bit of help. JLGWhiz provided me with this snippet, which helped quite a bit, but I am still not quite there. I guess JLGWhiz got busy, which I completely understand, with other stuff so I am reposting. This is the snippet I received. Sub Copy() Dim OriginalWB As Workbook Set OriginalWB = Workbooks("Q12345678.xls") Windows("BlankQuote.xls").Activate Cells.Copy OriginalWB.Range("A1").PasteSpecial _ Paste:=xlPasteAll, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False End Sub This works great. My problem is I need to take the part that states Set OriginalWB = Workbooks("Q12345678.xls") And turn this into a variable. Workbook Q12345678.xls could be named anything. The Blank Quote.xls will always be named Blank Quote.xls. The Blank Quote.xls is actually the original. It will be renamed to something else. It is also the master that will be kept up to date. I want to open the old workbook and the Blank Quote.xls. I want to click a button "Copy" in the old workbook and it will copy Sheet1 of Blank Quote.xls to the old workbook. It can either copy over it or copy to it and I can rename it. Any suggestions. I really appreciate the help. I just can't seem to grasp this stuff. Thank you for your help. Have a great day. God Bless Frank Pytel |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to copy a one worksheet in one book to the current workbook
There are least two options to generalize: "Q12345678.xls" If you are always running from the oldworkbook, then start your routine with: Sub Copy() Dim OriginalWB As Workbook Set OriginalWB = ActiveWorkbook If you want the user to specify the workbook at runtime, then start with: Sub Copy() Dim OriginalWB As Workbook Dim s As String s = Application.InputBox(Prompt:="Enter Workbook", Type:=2) Set OriginalWB = Workbooks(s) -- Gary''s Student - gsnu200858 "Frank Pytel" wrote: Good Morning; I posted the question in the subject line, to which I received quite a bit of help. JLGWhiz provided me with this snippet, which helped quite a bit, but I am still not quite there. I guess JLGWhiz got busy, which I completely understand, with other stuff so I am reposting. This is the snippet I received. Sub Copy() Dim OriginalWB As Workbook Set OriginalWB = Workbooks("Q12345678.xls") Windows("BlankQuote.xls").Activate Cells.Copy OriginalWB.Range("A1").PasteSpecial _ Paste:=xlPasteAll, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False End Sub This works great. My problem is I need to take the part that states Set OriginalWB = Workbooks("Q12345678.xls") And turn this into a variable. Workbook Q12345678.xls could be named anything. The Blank Quote.xls will always be named Blank Quote.xls. The Blank Quote.xls is actually the original. It will be renamed to something else. It is also the master that will be kept up to date. I want to open the old workbook and the Blank Quote.xls. I want to click a button "Copy" in the old workbook and it will copy Sheet1 of Blank Quote.xls to the old workbook. It can either copy over it or copy to it and I can rename it. Any suggestions. I really appreciate the help. I just can't seem to grasp this stuff. Thank you for your help. Have a great day. God Bless Frank Pytel |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to copy a one worksheet in one book to the current workbook
Try the below 2 options Sub Copy() Dim wbSource As Workbook, wbTarget As Workbook Set wbSource = ActiveWorkbook Set wbTarget = Workbooks("BlankQuote.xls") wbSource.Sheets(1).Copy Befo=wbTarget.Sheets(1) End Sub OR Sub Copy() Dim OriginalWB As Workbook Set OriginalWB = ActiveWorkbook Windows("BlankQuote.xls").Activate Activeworkbook.Sheets(1).activate Cells.Copy OriginalWB.Range("A1").PasteSpecial _ Paste:=xlPasteAll, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False End Sub -- If this post helps click Yes --------------- Jacob Skaria "Frank Pytel" wrote: Good Morning; I posted the question in the subject line, to which I received quite a bit of help. JLGWhiz provided me with this snippet, which helped quite a bit, but I am still not quite there. I guess JLGWhiz got busy, which I completely understand, with other stuff so I am reposting. This is the snippet I received. Sub Copy() Dim OriginalWB As Workbook Set OriginalWB = Workbooks("Q12345678.xls") Windows("BlankQuote.xls").Activate Cells.Copy OriginalWB.Range("A1").PasteSpecial _ Paste:=xlPasteAll, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False End Sub This works great. My problem is I need to take the part that states Set OriginalWB = Workbooks("Q12345678.xls") And turn this into a variable. Workbook Q12345678.xls could be named anything. The Blank Quote.xls will always be named Blank Quote.xls. The Blank Quote.xls is actually the original. It will be renamed to something else. It is also the master that will be kept up to date. I want to open the old workbook and the Blank Quote.xls. I want to click a button "Copy" in the old workbook and it will copy Sheet1 of Blank Quote.xls to the old workbook. It can either copy over it or copy to it and I can rename it. Any suggestions. I really appreciate the help. I just can't seem to grasp this stuff. Thank you for your help. Have a great day. God Bless Frank Pytel |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to copy a one worksheet in one book to the current workb
or probably the otherway around..... Sub Copy() Dim wbSource As Workbook, wbTarget As Workbook Set wbSource = Workbooks("BlankQuote.xls") Set wbTarget = ActiveWorkbook wbSource.Sheets(1).Copy Befo=wbTarget.Sheets(1) End Sub -- If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: Try the below 2 options Sub Copy() Dim wbSource As Workbook, wbTarget As Workbook Set wbSource = ActiveWorkbook Set wbTarget = Workbooks("BlankQuote.xls") wbSource.Sheets(1).Copy Befo=wbTarget.Sheets(1) End Sub OR Sub Copy() Dim OriginalWB As Workbook Set OriginalWB = ActiveWorkbook Windows("BlankQuote.xls").Activate Activeworkbook.Sheets(1).activate Cells.Copy OriginalWB.Range("A1").PasteSpecial _ Paste:=xlPasteAll, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False End Sub -- If this post helps click Yes --------------- Jacob Skaria "Frank Pytel" wrote: Good Morning; I posted the question in the subject line, to which I received quite a bit of help. JLGWhiz provided me with this snippet, which helped quite a bit, but I am still not quite there. I guess JLGWhiz got busy, which I completely understand, with other stuff so I am reposting. This is the snippet I received. Sub Copy() Dim OriginalWB As Workbook Set OriginalWB = Workbooks("Q12345678.xls") Windows("BlankQuote.xls").Activate Cells.Copy OriginalWB.Range("A1").PasteSpecial _ Paste:=xlPasteAll, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False End Sub This works great. My problem is I need to take the part that states Set OriginalWB = Workbooks("Q12345678.xls") And turn this into a variable. Workbook Q12345678.xls could be named anything. The Blank Quote.xls will always be named Blank Quote.xls. The Blank Quote.xls is actually the original. It will be renamed to something else. It is also the master that will be kept up to date. I want to open the old workbook and the Blank Quote.xls. I want to click a button "Copy" in the old workbook and it will copy Sheet1 of Blank Quote.xls to the old workbook. It can either copy over it or copy to it and I can rename it. Any suggestions. I really appreciate the help. I just can't seem to grasp this stuff. Thank you for your help. Have a great day. God Bless Frank Pytel |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to copy a one worksheet in one book to the current workb
Jacob; This is perfect. You are a life saver. Have a Great Day. Frank Pytel "Jacob Skaria" wrote: or probably the otherway around..... Sub Copy() Dim wbSource As Workbook, wbTarget As Workbook Set wbSource = Workbooks("BlankQuote.xls") Set wbTarget = ActiveWorkbook wbSource.Sheets(1).Copy Befo=wbTarget.Sheets(1) End Sub -- If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: Try the below 2 options Sub Copy() Dim wbSource As Workbook, wbTarget As Workbook Set wbSource = ActiveWorkbook Set wbTarget = Workbooks("BlankQuote.xls") wbSource.Sheets(1).Copy Befo=wbTarget.Sheets(1) End Sub OR Sub Copy() Dim OriginalWB As Workbook Set OriginalWB = ActiveWorkbook Windows("BlankQuote.xls").Activate Activeworkbook.Sheets(1).activate Cells.Copy OriginalWB.Range("A1").PasteSpecial _ Paste:=xlPasteAll, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False End Sub -- If this post helps click Yes --------------- Jacob Skaria "Frank Pytel" wrote: Good Morning; I posted the question in the subject line, to which I received quite a bit of help. JLGWhiz provided me with this snippet, which helped quite a bit, but I am still not quite there. I guess JLGWhiz got busy, which I completely understand, with other stuff so I am reposting. This is the snippet I received. Sub Copy() Dim OriginalWB As Workbook Set OriginalWB = Workbooks("Q12345678.xls") Windows("BlankQuote.xls").Activate Cells.Copy OriginalWB.Range("A1").PasteSpecial _ Paste:=xlPasteAll, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False End Sub This works great. My problem is I need to take the part that states Set OriginalWB = Workbooks("Q12345678.xls") And turn this into a variable. Workbook Q12345678.xls could be named anything. The Blank Quote.xls will always be named Blank Quote.xls. The Blank Quote.xls is actually the original. It will be renamed to something else. It is also the master that will be kept up to date. I want to open the old workbook and the Blank Quote.xls. I want to click a button "Copy" in the old workbook and it will copy Sheet1 of Blank Quote.xls to the old workbook. It can either copy over it or copy to it and I can rename it. Any suggestions. I really appreciate the help. I just can't seem to grasp this stuff. Thank you for your help. Have a great day. God Bless Frank Pytel |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy worksheet into current workbook | Excel Discussion (Misc queries) | |||
Macro to copy a one worksheet in one book to the current workbook | Excel Programming | |||
Macro to copy current worksheet to pre-defined wookbook | Excel Programming | |||
Macro to copy active worksheet to new workbook | Excel Discussion (Misc queries) | |||
Copy a range of cells in an unopened workbook and paste it to the current workbook | Excel Programming |