Macro to copy a one worksheet in one book to the current workbook

[Frank Pytel]

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

 

[Gary''s Student]

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)

 

[Jacob Skaria]

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 Before:=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

 

[Jacob Skaria]

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 Before:=wbTarget.Sheets(1)

End Sub

 

[Frank Pytel]

Jacob;

This is perfect. You are a life saver. Have a Great Day.

Frank Pytel