Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy two sheets
Hello. I have the following sub doing exactly what I need but have just had a request to pull two sheets from the same workbook and save them both in the same newly created workbook. 'copy sheet into new workbook Sheets("??????????").Copy 'copy the funky colours from the global report workbook ActiveWorkbook.Colors = Workbooks(currentReportFile).Colors 'make the lookup section into values Range("AM2:AX185").Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Range("A1").Select 'rename sheet Sheets("???????????").Name = "????????????? " & Format(Date, "yyyymmdd") 'break links ActiveWorkbook.BreakLink Name:= _ "G:\????????.xls", Type:=xlExcelLinks 'save in archive folder ReportFilename = _ "G:\?????????? " & _ Format(Date, "yyyymmdd") & ".xls" ActiveWorkbook.SaveAs Filename:=ReportFilename 'close file ActiveWorkbook.Close End Sub I am stumped as to how to copy both sheets across at tha same time and put them in the same workbook. All help greatly appreciated. Kristan -- Kstalker ------------------------------------------------------------------------ Kstalker's Profile: http://www.excelforum.com/member.php...o&userid=24699 View this thread: http://www.excelforum.com/showthread...hreadid=466141 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy two sheets
Hi Kristan,
Adapting your pseudo code, try something like: Sub Tester() Dim srcWB As Workbook Dim destWB As Workbook Set srcWB = ActiveWorkbook 'copy sheet into new workbook srcWB.Sheets("??????????").Copy Set destWB = ActiveWorkbook 'copy the funky colours from the global report workbook destWB.Colors = Workbooks(currentReportFile).Colors 'make the lookup section into values With ActiveSheet.Range("AM2:AX185") .Value = .Value End With destWB.Range("A1").Select 'rename sheet ActiveSheet.Name = "????????????? " & Format(Date, "yyyymmdd") srcWB.Sheets("SecondSheet").Copy _ After:=destWB.Sheets(destWB.Sheets.Count) ActiveSheet.Name = "????????????? " & Format(Date, "yyyymmdd") 'break links ActiveWorkbook.BreakLink Name:= _ "G:\????????.xls", Type:=xlExcelLinks 'save in archive folder ReportFilename = _ "G:\?????????? " & _ Format(Date, "yyyymmdd") & ".xls" destWB.SaveAs Filename:=ReportFilename 'close file destWB.Close End Sub '<<=========================== --- Regards, Norman "Kstalker" wrote in message ... Hello. I have the following sub doing exactly what I need but have just had a request to pull two sheets from the same workbook and save them both in the same newly created workbook. 'copy sheet into new workbook Sheets("??????????").Copy 'copy the funky colours from the global report workbook ActiveWorkbook.Colors = Workbooks(currentReportFile).Colors 'make the lookup section into values Range("AM2:AX185").Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Range("A1").Select 'rename sheet Sheets("???????????").Name = "????????????? " & Format(Date, "yyyymmdd") 'break links ActiveWorkbook.BreakLink Name:= _ "G:\????????.xls", Type:=xlExcelLinks 'save in archive folder ReportFilename = _ "G:\?????????? " & _ Format(Date, "yyyymmdd") & ".xls" ActiveWorkbook.SaveAs Filename:=ReportFilename 'close file ActiveWorkbook.Close End Sub I am stumped as to how to copy both sheets across at tha same time and put them in the same workbook. All help greatly appreciated. Kristan -- Kstalker ------------------------------------------------------------------------ Kstalker's Profile: http://www.excelforum.com/member.php...o&userid=24699 View this thread: http://www.excelforum.com/showthread...hreadid=466141 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy two sheets
Cheers Norman. Have adapted etc and can't seem to get past the "Set destWB = ActiveWorkbook". Unsure why this is occouring.. Dim srcWB As Workbook Dim destWB As Workbook Set srcWB = ActiveWorkbook 'copy sheet into new workbook srcWB.Sheets("CSB_Daily_Summary_cust_dmd_ver").Cop y Set destWB = ActiveWorkbook 'copy the funky colours from the global report workbook destWB.Colors = Workbooks(currentGlobalReportFile).Colors 'make the lookup section into values With ActiveSheet.Range("AM2:AX185") ..Value = .Value End With 'Selection.Copy 'Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ ' :=False, Transpose:=False destWB.Range("A1").Select 'rename sheet ActiveSheet.Name = "CSB_Daily_Summary " & Format(Date, "yyyymmdd") srcWB.Sheets("Site Stats").Copy _ After:=destWB.Sheets(destWB.Sheets.Count) ActiveSheet.Name = "Site Stats" & Format(Date, "yyyymmdd") 'break links ActiveWorkbook.BreakLink Name:= _ "G:\ch\shared04\queue stats\Kristan Stalker\Global Report\Global Report draft v3.0.xls", Type:=xlExcelLinks 'save in archive folder ReportFilename = _ "G:\ch\shared04\queue stats\Kristan Stalker\Global Report " & _ Format(Date, "yyyymmdd") & ".xls" destWB.SaveAs Filename:=ReportFilename 'close file destWB.Close End Sub Thanks Again -- Kstalker ------------------------------------------------------------------------ Kstalker's Profile: http://www.excelforum.com/member.php...o&userid=24699 View this thread: http://www.excelforum.com/showthread...hreadid=466141 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy two sheets
Hi Kristan,
Have adapted etc and can't seem to get past the "Set destWB = ActiveWorkbook". (1) What error are you getting and what is highlighted when the error occurs? (2) Where is 'currentGlobalReportFile' declared and how is it dimmed? --- Regards, Norman "Kstalker" wrote in message ... Cheers Norman. Have adapted etc and can't seem to get past the "Set destWB = ActiveWorkbook". Unsure why this is occouring.. Dim srcWB As Workbook Dim destWB As Workbook Set srcWB = ActiveWorkbook 'copy sheet into new workbook srcWB.Sheets("CSB_Daily_Summary_cust_dmd_ver").Cop y Set destWB = ActiveWorkbook 'copy the funky colours from the global report workbook destWB.Colors = Workbooks(currentGlobalReportFile).Colors 'make the lookup section into values With ActiveSheet.Range("AM2:AX185") Value = .Value End With 'Selection.Copy 'Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ ' :=False, Transpose:=False destWB.Range("A1").Select 'rename sheet ActiveSheet.Name = "CSB_Daily_Summary " & Format(Date, "yyyymmdd") srcWB.Sheets("Site Stats").Copy _ After:=destWB.Sheets(destWB.Sheets.Count) ActiveSheet.Name = "Site Stats" & Format(Date, "yyyymmdd") 'break links ActiveWorkbook.BreakLink Name:= _ "G:\ch\shared04\queue stats\Kristan Stalker\Global Report\Global Report draft v3.0.xls", Type:=xlExcelLinks 'save in archive folder ReportFilename = _ "G:\ch\shared04\queue stats\Kristan Stalker\Global Report " & _ Format(Date, "yyyymmdd") & ".xls" destWB.SaveAs Filename:=ReportFilename 'close file destWB.Close End Sub Thanks Again -- Kstalker ------------------------------------------------------------------------ Kstalker's Profile: http://www.excelforum.com/member.php...o&userid=24699 View this thread: http://www.excelforum.com/showthread...hreadid=466141 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy two sheets
Norman Neither declared nor dimmed.... which I imagine is the issue. Am unsur how to add. The error is "runtime error 438 object dosen't support thi property or method" Krista -- Kstalke ----------------------------------------------------------------------- Kstalker's Profile: http://www.excelforum.com/member.php...fo&userid=2469 View this thread: http://www.excelforum.com/showthread.php?threadid=46614 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy two sheets
Hi Kristan,
(1) Which code line is highlighted when the error occurs? (2) What value have you assigned to: 'currentReportFile'? BTW, I would stronly suggest that you always put the statement: Option Explicit at the head of each module. Better still, in the VBE: Tools | Options | Editor Tab | Check 'Require Variable Declaration This will automatically insert the Option Explicit declaration into each module. For a detailed rationale of this suggestion, see Chip Pearson's: ' Using Variables (Properly) In VBA' http://www.cpearson.com/excel/variables.htm --- Regards, Norman "Kstalker" wrote in message ... Norman Neither declared nor dimmed.... which I imagine is the issue. Am unsure how to add. The error is "runtime error 438 object dosen't support this property or method" Kristan -- Kstalker ------------------------------------------------------------------------ Kstalker's Profile: http://www.excelforum.com/member.php...o&userid=24699 View this thread: http://www.excelforum.com/showthread...hreadid=466141 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy two sheets
Hi Kristan,
Change: destWB.Range("A1").Select to Activesheet.Range("A1").Select --- Regards, Norman "Kstalker" wrote in message ... Norman Neither declared nor dimmed.... which I imagine is the issue. Am unsure how to add. The error is "runtime error 438 object dosen't support this property or method" Kristan -- Kstalker ------------------------------------------------------------------------ Kstalker's Profile: http://www.excelforum.com/member.php...o&userid=24699 View this thread: http://www.excelforum.com/showthread...hreadid=466141 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy two sheets
Norman Works a treat, thanks for the input. Read the link to Chip Pearsons site and it is valuable information. The copy is working well and there is one more thing I need it to do, the 'Site Stats' sheet that is being copied contains links to other data sources so I need to paste the values while maintaining the colours and formatting. Can you shed any light on my novice coding???? srcWB.Sheets("Site Stats").Range("a1:az300").Copy _ After:=destWB.Sheets(destWB.Sheets.Count) Range("A1").PasteSpecial Paste:=xlPasteValues, Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Application.CutCopyMode = False thanks Kristan Have tried several Public Sub testing() Dim srcWB As Workbook Dim destWB As Workbook Set srcWB = ActiveWorkbook srcWB.Sheets("CSB_Daily_Summary_cust_dmd_ver").Cop y Set destWB = ActiveWorkbook destWB.Colors = srcWB.Colors With ActiveSheet.Range("AM2:AX185") End With ActiveSheet.Range("A1").Select ActiveSheet.Name = "CSB_Daily_Summary " & Format(Date, "yyyymmdd") srcWB.Sheets("Site Stats").Copy _ After:=destWB.Sheets(destWB.Sheets.Count) ActiveSheet.Name = "Site Stats" & Format(Date, "yyyymmdd") ActiveWorkbook.BreakLink Name:= _ "G:\ch\shared04\queue stats\Kristan Stalker\Global Report\Global Report draft v3.0.xls", Type:=xlExcelLinks ReportFilename = _ "G:\ch\shared04\queue stats\Kristan Stalker\Global Report " & _ Format(Date, "yyyymmdd") & ".xls" destWB.SaveAs Filename:=ReportFilename destWB.Close End Sub -- Kstalker ------------------------------------------------------------------------ Kstalker's Profile: http://www.excelforum.com/member.php...o&userid=24699 View this thread: http://www.excelforum.com/showthread...hreadid=466141 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy two sheets
Hi Kristan,
Try replacing: ActiveSheet.Name = "Site Stats" & Format(Date, "yyyymmdd") with: With ActiveSheet .Name = "Site Stats" & Format(Date, "yyyymmdd") .UsedRange.Value = .UsedRange.Value End With --- Regards, Norman "Kstalker" wrote in message ... Norman Works a treat, thanks for the input. Read the link to Chip Pearsons site and it is valuable information. The copy is working well and there is one more thing I need it to do, the 'Site Stats' sheet that is being copied contains links to other data sources so I need to paste the values while maintaining the colours and formatting. Can you shed any light on my novice coding???? srcWB.Sheets("Site Stats").Range("a1:az300").Copy _ After:=destWB.Sheets(destWB.Sheets.Count) Range("A1").PasteSpecial Paste:=xlPasteValues, Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Application.CutCopyMode = False thanks Kristan Have tried several Public Sub testing() Dim srcWB As Workbook Dim destWB As Workbook Set srcWB = ActiveWorkbook srcWB.Sheets("CSB_Daily_Summary_cust_dmd_ver").Cop y Set destWB = ActiveWorkbook destWB.Colors = srcWB.Colors With ActiveSheet.Range("AM2:AX185") End With ActiveSheet.Range("A1").Select ActiveSheet.Name = "CSB_Daily_Summary " & Format(Date, "yyyymmdd") srcWB.Sheets("Site Stats").Copy _ After:=destWB.Sheets(destWB.Sheets.Count) ActiveSheet.Name = "Site Stats" & Format(Date, "yyyymmdd") ActiveWorkbook.BreakLink Name:= _ "G:\ch\shared04\queue stats\Kristan Stalker\Global Report\Global Report draft v3.0.xls", Type:=xlExcelLinks ReportFilename = _ "G:\ch\shared04\queue stats\Kristan Stalker\Global Report " & _ Format(Date, "yyyymmdd") & ".xls" destWB.SaveAs Filename:=ReportFilename destWB.Close End Sub -- Kstalker ------------------------------------------------------------------------ Kstalker's Profile: http://www.excelforum.com/member.php...o&userid=24699 View this thread: http://www.excelforum.com/showthread...hreadid=466141 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy two sheets
Morning Norman That replacement works, but I still am not capturing the values in th new Site Stats sheet just "#NAME?" for each entry. Hmmmm, I have trie rearranging sequence etc but keep arriving at the same conclusion which is that the values are being pasted before the link to othe data locations within the source workbook is cut.... Will continue reading through forums, any advice greatly appreciated. Cheers Kristan Public Sub testing() Dim srcWB As Workbook Dim destWB As Workbook Set srcWB = ActiveWorkbook 'copy sheet into new workbook srcWB.Sheets("CSB_Daily_Summary_cust_dmd_ver").Cop y Set destWB = ActiveWorkbook 'copy the funky colours from the global report workbook destWB.Colors = srcWB.Colors 'make the lookup section into values With ActiveSheet.Range("AM2:AX185") .Value = .Value End With ActiveSheet.Range("A1").Select 'rename sheet ActiveSheet.Name = "CSB_Daily_Summary " & Format(Date, "yyyymmdd") srcWB.Sheets("Site Stats").Copy _ After:=destWB.Sheets(destWB.Sheets.Count) ActiveSheet.Name = "Site Stats" & Format(Date, "yyyymmdd") With ActiveSheet .Name = "Site Stats" & Format(Date, "yyyymmdd") .UsedRange.Value = .UsedRange.Value End With 'break links ActiveWorkbook.BreakLink Name:= _ "G:\ch\shared04\queue stats\Kristan Stalker\Global Report\Global Repor draft v3.0.xls", Type:=xlExcelLinks 'save in archive folder ReportFilename = _ "G:\ch\shared04\queue stats\Kristan Stalker\Global Report " & _ Format(Date, "yyyymmdd") & ".xls" destWB.SaveAs Filename:=ReportFilename 'close file destWB.Close End Su -- Kstalke ----------------------------------------------------------------------- Kstalker's Profile: http://www.excelforum.com/member.php...fo&userid=2469 View this thread: http://www.excelforum.com/showthread.php?threadid=46614 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy two sheets
Hi Kristan,
I could not replicate your experience. I note that you speak of the new Site Stats sheet. What about the new CSB_Daily_Summary sheet, which is the first of the two sheets in the new workbook? Are values correctly shown in this first sheet? Both sheets in the new workbook are created in analagous manner: an entire worksheet is copied and then the copied data is converted to values. Given this, why do you need to break links? Data converted to its values equvalent has no links. It would appear that I am missing something fundamental to your situation. For testing purposes, I set up a source book with a Daily Summary sheet and a Site Stat sheet. Both sheets included formulae and links to a another workbook. I ran the code in this test environment without your observed problems. I excluded the break links code line because of Excel version problems (my test machine was set up for xl2k) and because I deemed this unnecessary - see my links comments earlier. --- Regards, Norman "Kstalker" wrote in message ... Morning Norman That replacement works, but I still am not capturing the values in the new Site Stats sheet just "#NAME?" for each entry. Hmmmm, I have tried rearranging sequence etc but keep arriving at the same conclusion, which is that the values are being pasted before the link to other data locations within the source workbook is cut.... Will continue reading through forums, any advice greatly appreciated. Cheers Kristan Public Sub testing() Dim srcWB As Workbook Dim destWB As Workbook Set srcWB = ActiveWorkbook 'copy sheet into new workbook srcWB.Sheets("CSB_Daily_Summary_cust_dmd_ver").Cop y Set destWB = ActiveWorkbook 'copy the funky colours from the global report workbook destWB.Colors = srcWB.Colors 'make the lookup section into values With ActiveSheet.Range("AM2:AX185") Value = .Value End With ActiveSheet.Range("A1").Select 'rename sheet ActiveSheet.Name = "CSB_Daily_Summary " & Format(Date, "yyyymmdd") srcWB.Sheets("Site Stats").Copy _ After:=destWB.Sheets(destWB.Sheets.Count) ActiveSheet.Name = "Site Stats" & Format(Date, "yyyymmdd") With ActiveSheet Name = "Site Stats" & Format(Date, "yyyymmdd") UsedRange.Value = .UsedRange.Value End With 'break links ActiveWorkbook.BreakLink Name:= _ "G:\ch\shared04\queue stats\Kristan Stalker\Global Report\Global Report draft v3.0.xls", Type:=xlExcelLinks 'save in archive folder ReportFilename = _ "G:\ch\shared04\queue stats\Kristan Stalker\Global Report " & _ Format(Date, "yyyymmdd") & ".xls" destWB.SaveAs Filename:=ReportFilename 'close file destWB.Close End Sub -- Kstalker ------------------------------------------------------------------------ Kstalker's Profile: http://www.excelforum.com/member.php...o&userid=24699 View this thread: http://www.excelforum.com/showthread...hreadid=466141 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy two sheets
Thanks in advance for your thoughts Norman. After looking into the areas you have highlighted I have one area tha may be causing the issue. The CSB_Daily_Summary sheet has copied no problem as has the Site Stat excluding the values. The values are sourced form formulas and othe sheets for the CSB_Daily_Summary sheet wheras they are sourced directl from a seperate pivot table in the workbook for the Site Stats sheet. Would this prevent the values copying? I have noticed that the initia copy is not brining values across just the "NAME#" error with th formula embedded. Re the breaklink, I ran the code minus the breaklink and still the sam outcome, although the size of the new workbook was larger. So this doe not seem to be the issue -- Kstalke ----------------------------------------------------------------------- Kstalker's Profile: http://www.excelforum.com/member.php...fo&userid=2469 View this thread: http://www.excelforum.com/showthread.php?threadid=46614 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy two sheets
Hi Kristan
Try replacing: With ActiveSheet .Name = "Site Stats" & Format(Date, "yyyymmdd") .UsedRange.Value = .UsedRange.Value End With with: With ActiveSheet .Name = "Site Stats" & Format(Date, "yyyymmdd") .UsedRange.Copy .UsedRange.PasteSpecial Paste:=xlValues End With --- Regards, Norman "Kstalker" wrote in message ... Thanks in advance for your thoughts Norman. After looking into the areas you have highlighted I have one area that may be causing the issue. The CSB_Daily_Summary sheet has copied no problem as has the Site Stats excluding the values. The values are sourced form formulas and other sheets for the CSB_Daily_Summary sheet wheras they are sourced directly from a seperate pivot table in the workbook for the Site Stats sheet. Would this prevent the values copying? I have noticed that the initial copy is not brining values across just the "NAME#" error with the formula embedded. Re the breaklink, I ran the code minus the breaklink and still the same outcome, although the size of the new workbook was larger. So this does not seem to be the issue. -- Kstalker ------------------------------------------------------------------------ Kstalker's Profile: http://www.excelforum.com/member.php...o&userid=24699 View this thread: http://www.excelforum.com/showthread...hreadid=466141 |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy two sheets
Thanks Norman. I have found the root of the problem.... I have a public function running on every cell in the site stats sheet correcting error fields. "Public Function derror(v_input As Variant) As Variant If Application.WorksheetFunction.IsError(v_input) = True Then derror = 0 Else derror = v_input End If End Function" When removed the data and values copy correctly. Thanks for your help. Kristan -- Kstalker ------------------------------------------------------------------------ Kstalker's Profile: http://www.excelforum.com/member.php...o&userid=24699 View this thread: http://www.excelforum.com/showthread...hreadid=466141 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copy cell info to other sheets, other sheets dont contain all row. | Excel Worksheet Functions | |||
move or copy sheets doesn't copy format | Excel Worksheet Functions | |||
Copy Sheets | Excel Discussion (Misc queries) | |||
in VBA Sheets("mysheet").Copy Befo=Sheets(1) how do i get a reference to the newly created copy of this sheet? | Excel Worksheet Functions | |||
copy sheets | Excel Programming |