Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy between variable workbook names
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy between variable workbook names
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 -- HTH... Jim Thomlinson "AdP" wrote: 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy between variable workbook names
Thanks a lot for taking the time.
"Jim Thomlinson" wrote: 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 -- HTH... Jim Thomlinson "AdP" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy worksheet containing names to other workbook w/o problems? | Excel Discussion (Misc queries) | |||
Copy worksheet with named ranges to new workbook and keep names | Excel Worksheet Functions | |||
how do I find names in a workbook full of names | Excel Discussion (Misc queries) | |||
copy a set of names(constants) from 1 workbook to another | Excel Discussion (Misc queries) | |||
How can I Copy cell names from one workbook to another? | Excel Discussion (Misc queries) |