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

F

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
 
G

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)
 
J

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
 
J

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top