| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
code broken
I have the following code but it is not pasting and i am not sure why, can
anyone help please. Also In the file cells i am copying from there is a defined list, will this be updated when in the new file when i paste? Thank you Sub UpdateDensities() 'Copy this section changing fluid database name for open and close, ' Open the database. Workbooks.Open "G:\Drilling Fluids Technology\Fluids Database\FluidDatabaseTemplate.xls" 'Unlock folder denstities page Windows("FluidDatabaseTemplate.xls").Activate Sheets("Densities").Select ActiveSheet.Unprotect Password:="baker" 'Copy and Paste Section Windows("FluidDatabaseTemplate.xls").Activate Columns("H:M").Select Selection.EntireColumn.Hidden = False Windows("FluidDatabaseMM.xls").Activate Sheets("Densities").Select Columns("A:M").Select Selection.Copy Windows("FluidDatabaseTemplate.xls").Activate Columns("A:M").Select Selection.pasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Columns("H:M").Select Selection.EntireColumn.Hidden = True Windows("FluidDatabaseTemplate.xls").Activate Sheets("Densities").Select ActiveSheet.Protect Password:="baker" Windows("FluidDatabaseMM.xls").Activate Sheets("Extras").Select 'Close Workbook Application.DisplayAlerts = False Workbooks("FluidDatabaseTemplate.xls").Close Application.DisplayAlerts = True End Sub |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
code broken
Hi there,
For the code given i dont see any declarations of something being copied or pasted. You may want to look into that first and see what happens from there. If you need help with the copy segment then feel free to ask -- Thank you and Regards Garreth Lombard "Miree" wrote: I have the following code but it is not pasting and i am not sure why, can anyone help please. Also In the file cells i am copying from there is a defined list, will this be updated when in the new file when i paste? Thank you Sub UpdateDensities() 'Copy this section changing fluid database name for open and close, ' Open the database. Workbooks.Open "G:\Drilling Fluids Technology\Fluids Database\FluidDatabaseTemplate.xls" 'Unlock folder denstities page Windows("FluidDatabaseTemplate.xls").Activate Sheets("Densities").Select ActiveSheet.Unprotect Password:="baker" 'Copy and Paste Section Windows("FluidDatabaseTemplate.xls").Activate Columns("H:M").Select Selection.EntireColumn.Hidden = False Windows("FluidDatabaseMM.xls").Activate Sheets("Densities").Select Columns("A:M").Select Selection.Copy Windows("FluidDatabaseTemplate.xls").Activate Columns("A:M").Select Selection.pasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Columns("H:M").Select Selection.EntireColumn.Hidden = True Windows("FluidDatabaseTemplate.xls").Activate Sheets("Densities").Select ActiveSheet.Protect Password:="baker" Windows("FluidDatabaseMM.xls").Activate Sheets("Extras").Select 'Close Workbook Application.DisplayAlerts = False Workbooks("FluidDatabaseTemplate.xls").Close Application.DisplayAlerts = True End Sub |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
code broken
Oops found those statements. Im looking into the matter for you now
-- Thank you and Regards Garreth Lombard "Miree" wrote: I have the following code but it is not pasting and i am not sure why, can anyone help please. Also In the file cells i am copying from there is a defined list, will this be updated when in the new file when i paste? Thank you Sub UpdateDensities() 'Copy this section changing fluid database name for open and close, ' Open the database. Workbooks.Open "G:\Drilling Fluids Technology\Fluids Database\FluidDatabaseTemplate.xls" 'Unlock folder denstities page Windows("FluidDatabaseTemplate.xls").Activate Sheets("Densities").Select ActiveSheet.Unprotect Password:="baker" 'Copy and Paste Section Windows("FluidDatabaseTemplate.xls").Activate Columns("H:M").Select Selection.EntireColumn.Hidden = False Windows("FluidDatabaseMM.xls").Activate Sheets("Densities").Select Columns("A:M").Select Selection.Copy Windows("FluidDatabaseTemplate.xls").Activate Columns("A:M").Select Selection.pasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Columns("H:M").Select Selection.EntireColumn.Hidden = True Windows("FluidDatabaseTemplate.xls").Activate Sheets("Densities").Select ActiveSheet.Protect Password:="baker" Windows("FluidDatabaseMM.xls").Activate Sheets("Extras").Select 'Close Workbook Application.DisplayAlerts = False Workbooks("FluidDatabaseTemplate.xls").Close Application.DisplayAlerts = True End Sub |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
code broken
Figured it out i forgot to write to save the file before closing.
But still need to know if it is possible to update the size of defined lists from one source to another. "Miree" wrote: I have the following code but it is not pasting and i am not sure why, can anyone help please. Also In the file cells i am copying from there is a defined list, will this be updated when in the new file when i paste? Thank you Sub UpdateDensities() 'Copy this section changing fluid database name for open and close, ' Open the database. Workbooks.Open "G:\Drilling Fluids Technology\Fluids Database\FluidDatabaseTemplate.xls" 'Unlock folder denstities page Windows("FluidDatabaseTemplate.xls").Activate Sheets("Densities").Select ActiveSheet.Unprotect Password:="baker" 'Copy and Paste Section Windows("FluidDatabaseTemplate.xls").Activate Columns("H:M").Select Selection.EntireColumn.Hidden = False Windows("FluidDatabaseMM.xls").Activate Sheets("Densities").Select Columns("A:M").Select Selection.Copy Windows("FluidDatabaseTemplate.xls").Activate Columns("A:M").Select Selection.pasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Columns("H:M").Select Selection.EntireColumn.Hidden = True Windows("FluidDatabaseTemplate.xls").Activate Sheets("Densities").Select ActiveSheet.Protect Password:="baker" Windows("FluidDatabaseMM.xls").Activate Sheets("Extras").Select 'Close Workbook Application.DisplayAlerts = False Workbooks("FluidDatabaseTemplate.xls").Close Application.DisplayAlerts = True End Sub |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
code broken
Great,
Was going to ask that question next and maybe suggest you save with the below statement.. Activeworkbook.Save -- Thank you and Regards Garreth Lombard "Miree" wrote: Figured it out i forgot to write to save the file before closing. But still need to know if it is possible to update the size of defined lists from one source to another. "Miree" wrote: I have the following code but it is not pasting and i am not sure why, can anyone help please. Also In the file cells i am copying from there is a defined list, will this be updated when in the new file when i paste? Thank you Sub UpdateDensities() 'Copy this section changing fluid database name for open and close, ' Open the database. Workbooks.Open "G:\Drilling Fluids Technology\Fluids Database\FluidDatabaseTemplate.xls" 'Unlock folder denstities page Windows("FluidDatabaseTemplate.xls").Activate Sheets("Densities").Select ActiveSheet.Unprotect Password:="baker" 'Copy and Paste Section Windows("FluidDatabaseTemplate.xls").Activate Columns("H:M").Select Selection.EntireColumn.Hidden = False Windows("FluidDatabaseMM.xls").Activate Sheets("Densities").Select Columns("A:M").Select Selection.Copy Windows("FluidDatabaseTemplate.xls").Activate Columns("A:M").Select Selection.pasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Columns("H:M").Select Selection.EntireColumn.Hidden = True Windows("FluidDatabaseTemplate.xls").Activate Sheets("Densities").Select ActiveSheet.Protect Password:="baker" Windows("FluidDatabaseMM.xls").Activate Sheets("Extras").Select 'Close Workbook Application.DisplayAlerts = False Workbooks("FluidDatabaseTemplate.xls").Close Application.DisplayAlerts = True End Sub |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
code broken
Hi
I cleaned up your original code, as you have many steps not needed. BTW: I don't think it's nessecary to turn off alerts if you save the workbook before it's closed. Look at this: Sub UpdateDensities() Dim ThisWb As Workbook Dim dbWB As Workbook Set ThisWb = ActiveWorkbook 'Copy this section changing fluid database name for open and close, ' Open the database. Set dbWB = Workbooks.Open("G:\Drilling Fluids Technology\Fluids Database\FluidDatabaseTemplate.xls") 'Copy and Paste Section ThisWb.Sheets("Densities").Columns("A:M").Copy With dbWB.Sheets("Densities") .Unprotect Password:="baker" .Sheets("Densities").Columns("H:M").Hidden = False .Columns("A:M").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False .Columns("H:M").Hidden = True .Protect Password:="baker" End With ThisWb.Sheets("Extras").Select 'Only required if another sheet is active when this macro is started! 'Close Workbook Application.DisplayAlerts = False dbWB.SaveAs '<=== enter path and filename here dbWB.Close Application .DisplayAlerts = True End Sub HTH Per "Miree" skrev i meddelelsen ... I have the following code but it is not pasting and i am not sure why, can anyone help please. Also In the file cells i am copying from there is a defined list, will this be updated when in the new file when i paste? Thank you Sub UpdateDensities() 'Copy this section changing fluid database name for open and close, ' Open the database. Workbooks.Open "G:\Drilling Fluids Technology\Fluids Database\FluidDatabaseTemplate.xls" 'Unlock folder denstities page Windows("FluidDatabaseTemplate.xls").Activate Sheets("Densities").Select ActiveSheet.Unprotect Password:="baker" 'Copy and Paste Section Windows("FluidDatabaseTemplate.xls").Activate Columns("H:M").Select Selection.EntireColumn.Hidden = False Windows("FluidDatabaseMM.xls").Activate Sheets("Densities").Select Columns("A:M").Select Selection.Copy Windows("FluidDatabaseTemplate.xls").Activate Columns("A:M").Select Selection.pasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Columns("H:M").Select Selection.EntireColumn.Hidden = True Windows("FluidDatabaseTemplate.xls").Activate Sheets("Densities").Select ActiveSheet.Protect Password:="baker" Windows("FluidDatabaseMM.xls").Activate Sheets("Extras").Select 'Close Workbook Application.DisplayAlerts = False Workbooks("FluidDatabaseTemplate.xls").Close Application.DisplayAlerts = True End Sub |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
code broken
this approach may work:
Sub UpdateDensities() Dim TPFile As String Dim TPWB As Workbook Dim ThisWKB As Workbook Dim r1 As Range, r2 As Range Set ThisWKB = ThisWorkbook Application.ScreenUpdating = False TPFile = "G:\Drilling Fluids Technology\Fluids Database\FluidDatabaseTemplate.xls " ' Open the database. Set TPWB = Workbooks.Open(TPFile, ReadOnly:=False, Password:="") 'set 1st range to copy Set r1 = ThisWKB.Worksheets("Densities").Columns("A:M") With TPWB.Worksheets("Densities") .Unprotect Password:="baker" 'set 2nd range to paste to Set r2 = .Columns("A:M") 'copy & paste r1.Copy r2 .Columns("H:M").Hidden = True .Protect Password:="baker" End With 'close & save TPWB.Close True Application.ScreenUpdating = True End Sub -- jb "Miree" wrote: I have the following code but it is not pasting and i am not sure why, can anyone help please. Also In the file cells i am copying from there is a defined list, will this be updated when in the new file when i paste? Thank you Sub UpdateDensities() 'Copy this section changing fluid database name for open and close, ' Open the database. Workbooks.Open "G:\Drilling Fluids Technology\Fluids Database\FluidDatabaseTemplate.xls" 'Unlock folder denstities page Windows("FluidDatabaseTemplate.xls").Activate Sheets("Densities").Select ActiveSheet.Unprotect Password:="baker" 'Copy and Paste Section Windows("FluidDatabaseTemplate.xls").Activate Columns("H:M").Select Selection.EntireColumn.Hidden = False Windows("FluidDatabaseMM.xls").Activate Sheets("Densities").Select Columns("A:M").Select Selection.Copy Windows("FluidDatabaseTemplate.xls").Activate Columns("A:M").Select Selection.pasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Columns("H:M").Select Selection.EntireColumn.Hidden = True Windows("FluidDatabaseTemplate.xls").Activate Sheets("Densities").Select ActiveSheet.Protect Password:="baker" Windows("FluidDatabaseMM.xls").Activate Sheets("Extras").Select 'Close Workbook Application.DisplayAlerts = False Workbooks("FluidDatabaseTemplate.xls").Close Application.DisplayAlerts = True End Sub |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Trouble with code that is broken over multiple lines (space_) | Excel Programming | |||
| Hyperlinks broken | Excel Discussion (Misc queries) | |||
| Broken Limks | Excel Worksheet Functions | |||
| i think its broken | Excel Discussion (Misc queries) | |||
| Broken References | Excel Programming | |||
Linear Mode
