Copy between variable workbook names

[Guest]

I create two workbooks from a template. That works, but then I want to copy
from the one and Paste Special as values - sheet for sheet. (The one
workbook is linked to an Access db. I do not wish to work with the live
data).

My attemp so far:

Dim SourceWB As Workbook
Dim TargetWB As Workbook
Set SourceWB = "Backup " & Format(Now, "yyyy-mm-dd") & ".xls" (File
successfully created earlier and still open)
Set TargetWB = "ValuesOnly " & Format(Now, "yyyy-mm-dd") & ".xls" (File
successfully created earlier and still open)
Windows("SourceWB").Activate

Then the copying should start. Exact dupes sheets (July, Aug, etc) from
template, but paste special as values required. I have recorded a macro to
do this.

My questions:
- SourceWB to TargetWB above don't work and I'm struggling to debug.
ActiveWorkbook.Next won't work if the user has a third workbook open (I
think).
- Copying between workbooks with exact same sheet names can be easier than
with my recorded macro?

Thanks for any advice.

 

[Guest]

You are very close but you need to specify some worksheet and range
objects(as near as I can tell from your question) Try something like this...
Sub Test()
Dim wbkSource As Workbook
Dim wbkDestination As Workbook
Dim wksSource As Worksheet
Dim wksDestination As Worksheet
Dim rngSource As Range
Dim rngDestination As Range

'Set your source
Set wbkSource = ThisWorkbook
Set wksSource = wbkSource.Sheets("Sheet1")
Set rngSource = wksSource.Cells

'Set your destination
On Error GoTo OpenBook
Set wbkDestination = Workbooks("ThatBook.xls")
On Error GoTo 0
Set wksDestination = wbkDestination.Sheets("Sheet1")
Set rngDestination = wksDestination.Range("A1")

'You now have all of your souce and destination objects

rngSource.Copy rngDestination

Exit Sub

OpenBook:
Set wbkDestination = Workbooks.Open("C:\Thatbook.xls")
Resume Next
Exit Sub

End Sub

 

[Guest]

Thanks a lot for taking the time.

You are very close but you need to specify some worksheet and range
objects(as near as I can tell from your question) Try something like this...
Sub Test()
Dim wbkSource As Workbook
Dim wbkDestination As Workbook
Dim wksSource As Worksheet
Dim wksDestination As Worksheet
Dim rngSource As Range
Dim rngDestination As Range

'Set your source
Set wbkSource = ThisWorkbook
Set wksSource = wbkSource.Sheets("Sheet1")
Set rngSource = wksSource.Cells

'Set your destination
On Error GoTo OpenBook
Set wbkDestination = Workbooks("ThatBook.xls")
On Error GoTo 0
Set wksDestination = wbkDestination.Sheets("Sheet1")
Set rngDestination = wksDestination.Range("A1")

'You now have all of your souce and destination objects

rngSource.Copy rngDestination

Exit Sub

OpenBook:
Set wbkDestination = Workbooks.Open("C:\Thatbook.xls")
Resume Next
Exit Sub

End Sub