code broken

[Miree]

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

 

[Garreth Lombard]

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

 

[Garreth Lombard]

Oops found those statements. Im looking into the matter for you now
--


Thank you and Regards

Garreth Lombard

 

[Miree]

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.

 

[Garreth Lombard]

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

 

[Per Jessen]

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

 

[john]

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