Posts by jamesparker_1

Re: Worksheet Name To Variable

Hi Guys!

That's a real shame I cant do that. I can work around it but It would have tidyed things up in my code.

Thanks for trying though!

James

Hi All

I am copying a sheet to paste into a new workbook. The Sheet contains formula and numbers so I figured that a xlPasteFormulas should work. However the number data gets pasted across fine but the formula get pasted along with their path.

I.e. ='Add Detail Client Delivery-Bmth'!C6

is copied an becomes:

='C:\Documents and Settings\JP\Desktop\Test\[Weekly Metric Template 04_02_2007 Bournemouth.xls]Add Detail Client Delivery-Main'!$C$6

Here is the basic code i'm using:

Set Nws = Sheets(shtName)
     
    Set WwB = Workbooks.Open(Filename:=SiteFile & Day_Str$ & Sitenamefull & ".xls")
     
    Set Ws = WwB.Worksheets(14)
     
    With Ws.Cells.Copy
    End With
     
    Nws.Cells.PasteSpecial xlPasteFormulas

How can I make it copy the formula without the file path?

Thanks

James

Re: Worksheet Name To Variable

Hi Guys,

Unfortunately, due to the nature of the program. It is likely that people will try and rename the sheet tabs. So therefore I try to work with codenames to avoid errors when they change something. So I think i need to do it this way.

Any suggestions?

Thanks

James

Re: Worksheet Name To Variable

That doesn't seem to work i'm afraid.

Here is the total code as maybe that will make things clearer:

Public Sub CopyOver()
   
   With Application
        .ScreenUpdating = False
        .EnableEvents = False
        .DisplayAlerts = False
    End With
     
    Dim WwB As Workbook
    Dim Ws, Nws, sSht As Worksheet
    Dim Shtname As String
    Dim dteDate As Date
    
    'Select Site
    Sitenamefull = " " & "Bournemouth"
    Sitenameshort = "_Bmth"
        
    
    'Open Site File**************************************
    SavePath = Weekly_Main.Range("J1").Value
    SiteFile = SavePath & "\Weekly Metric Template for ppt_EMEA  "
    
    dteDate = Weekly_Main.Range("I1")
    Day_Str$ = Format$(dteDate, "dd_mm_yyyy")
    'Set WwB = Workbooks.Open(Filename:=SiteFile & Day_Str$ & " Bournemouth" & ".xls")
     
    
    'Weekly Sheet Update*************************************************
    shtName1 = "Weekly" & Sitenameshort
    'shtName = ShtName1.Name         'Error!!!!!!!!!!!!!!!!!!!!!!!!!11
        
    Set Nws = Sheets(Shtname)
     
    Set WwB = Workbooks.Open(Filename:=SiteFile & Day_Str$ & Sitenamefull & ".xls")
     
    Set Ws = WwB.Worksheets(4)
     
    With Ws.Cells.Copy
    End With
     
    Nws.Cells.PasteSpecial xlPasteAll
     
    Set Nws = Nothing
    Set Ws = Nothing
    'Rec Sheet Update*************************************************
    
    
    
    
    'Close Site sheet *******************************************
    WwB.Close False
    Set WwB = Nothing
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
        .DisplayAlerts = True
    End With
     
End Sub

Re: Worksheet Name To Variable

Hi guys

Sorry, I probably didnt make myself clear.

ShtName1 is the codename (i.e. Weekly_Bmth)

therefore:

shtName = ShtName1.Name is because I want shtName to be whatever the tab name (i.e. the .name) is of the worksheet with the codename above.

James

Hi Sorry, I feel like this is an obvious one and I should know it but why wont this work?

Sitenameshort = "_Bmth"


    ShtName1 = "Weekly" & Sitenameshort
   
    shtName = ShtName1.Name 'This line doesnt work
    
    Set Nws = Sheets(shtName)

It Errors on the marked line.

Thanks

James

I think if possible i'd prefer to do it using the workbooks(1) method as I will be expanding this macro to use other workbooks and so I could just copy the code over rather than editing the workbook name.

But is this what's causing the problem with the selection of the sheet by its codename?

Hi All

I have a main workbook that uses a macro to open a workbook provided to me from another department. This workbook contains a sheet that is basically a copy of the sheet in the main document and has the same sheetname and same codename.

The macro should copy the sheet data from the site workbook to the sheet in the main workbook. However, when i tell the workbook to select the sheet in the site file using its codename it errors.

I cant understand it. Does anyone have any ideas?

Sub Consolidate_Bmth() 
 '
     
    Sitefile = "\Weekly File  " & _ 
    Format(Weekly_Main.Range("I1").Value, "dd_mm_yyyy") & " Site.xls" 
     
    Workbooks.Open Filename:=Sitefile 
     
    Workbooks(2).Activate 'Copy from Sitefile
    Weekly_Bmth.Select     'WHY Wont it select this sheet?*************
    Cells.Select 
    Selection.Copy 
     
    Workbooks(1).Activate 'Paste to Main File
    Weekly_Bmth.Select 
    Cells.Select 
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _ 
    SkipBlanks:=False, Transpose:=False 
    Application.CutCopyMode = False 
    Range("B4:I4").Select 
     
    Workbooks(2).Activate 
     
     
End Sub

Is this caused by the codenames being the same in both workbooks(1) and (2)? I don't really understand because if i'm in the newly opened workbook, why does it error when I select the sheet i want?

James

Re: Overwrite Sheet In Workbook With Sheet Of Same Name

Hi RoyUK,

Unfortunately that doesn't work for me cause I have formula pointed at the sheet i would delete so my formula then go =ref!#.

I have tried the suggestion of copying the sheets across but this errors at the point marked in the code:

Sub Consolidate_Bmth()
'




    Sitefile = "\Weekly File  " & _
    Format(Weekly_Main.Range("I1").Value, "dd_mm_yyyy") & " Site.xls"


    Workbooks.Open Filename:=Sitefile
      
    Workbooks(2).Activate 'Copy from Site file
    Weekly_Bmth.Select 'WHY Wont it work??*****************
    Cells.Select
    Selection.Copy


    Workbooks(1).Activate 'Paste to Main File
    Weekly_Bmth.Select
    Cells.Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    Range("B4:I4").Select
            
    Workbooks(2).Activate
    
    
End Sub

Is this caused by the codenames being the same in both workbooks(1) and (2)? I don't really understand because if i'm in the newly opened workbook, why cant does it error when I select a sheet?

James

Re: Overwrite Sheet In Workbook With Sheet Of Same Name

Hi Guys!

Thanks for the suggestions. I feel that this is the way i'm gonna have to go...:)

But just to totally close off the way i wanted to do it: So there is no way to move the sheet from the seperated file back into the main file to overwrite the existing sheet without screwing up the formulas that point at this sheet?

Thanks

James

Hi all.

I have a problem that I dont even know where to start on.

I have created a workbook with several subsheets for different departments in my company to fill in there respective data that feeds a main sheet that colates all the info.

I've designed a macro that basically breaks the department sheets into seperate workbooks so that i can send them to the different people to complete. When the files return, I could sit there pasting the data back into the main workbook but really I would rather have a macro that overwrites the sheet in the main work book with the completed sheet of the same name.

Maybe I havent done this correctly, but when i try to move the sheet from the department workbook back to the main workbook it just creates a (2) copy. I want it to replace the sheet rather than create a copy.

Any ideas how i can do this?

Thanks

James

Re: Picture In A Picture

Ah yes, that works now!

Thanks Andy, A great solution to the problem! Many thanks.

James

Re: Picture In A Picture

Hi Andy,

Yeah, I'm sort of following it. Having a few problems converting it across to my larger workbook as i have a few more items on that sheet than the one i sent you as it was too big for the work book.

So 2 Questions if i may:

1/How did you shrink the picture file size? was it using a paint program or something outside excel.

2/I've copied the series across to my new spreadsheet but they are still pointing at the file you made. I.e the series is showing:

=SERIES('C:\DOCUME~1\ParkerJ\LOCALS~1\Temp\Temporary Directory 2 for Capacity2.zip\[Capacity2.xls]Summary'!$R$5,'C:\DOCUME~1\ParkerJ\LOCALS~1\Temp\Temporary Directory 2 for Capacity2.zip\[Capacity2.xls]Summary'!$R$7:$R$8,'C:\DOCUME~1\ParkerJ\LOCALS~1\Temp\Temporary Directory 2 for Capacity2.zip\[Capacity2.xls]Summary'!$U$7:$U$8,1)

How do i change the series to use my new spreadsheet (i.e. which pictures are you using to creat the pictures in the series as i cant see any on the summary sheet.)

Thanks

James

Re: Picture In A Picture

Hi Guys,

Ok, here is a simplified version of my sheet.

As you can see, I have 3 clipart pictures (i could only include 2 to keep the file size down) in the key on the control panel. Now I want it so that:

For the left box that says "Picture to change here!"

If any of the boxes on the summary sheet lines 7&8 are red then the Red cross shows if there is no comment in G15 or G17, or the green tick if there are comments in G15 and G17. If lines 7&8 are not red but comments are in G15&17 then show the amber cross.

For the Right box that says "Picture to change here!"

If any of the boxes on the summary sheet lines 10&11 are red then the Red cross shows if there is no comment in G20 or G22, or the green tick if there are comments in G20 and G22. If lines 10&11 are not red but comments are in G15&17 then show the amber cross.

Hope that makes sense.

James

Re: Picture In A Picture

Hi Andy.

Great picviewer by the way! Unfortunately I dont think that will work because I an using a graph sheet as the title page where I have a load of macros buttons as well as the summary information that i talked about above. Your example is a graph on a worksheet so i guess i cant apply that to having an xy graph inbedded in another graph sheet.

Not sure if this explains things or not. (I will try and shrink my file to attach it as an example later if i can).

But what about Zimitry's idea of hiding/Showing the clipart pictures? Could i just overlay the 3 pictures and hide 2 if a cell on another tab says red and show the other picture then swap it around if the cell says amber or green?

Hi,

I have several pictures on a graph sheet (i.e. imported cliparts) which I am using as a key to describe outcomes. I also have a blank picture box which I want to show each of the key pictures depending on a certain value in a cell on another sheet.

How can i do this?

So to simplify :

In Picture1 i want :

fx = If(sheet1!A1 = "Red",Picture2,Picture3)

How can i do this?

Thanks

James

Hi guys

I use the below Macro to Protect my Sheets and I also use a sheet called 'UnprotectAll' which works in the same way to unprotect my sheets.

As you can see, the password to unprotect sheets is clearly visible to anyone who loads up my VBA code.

Unfortunately I cannot use the Protection properties option of VBA to Hide all my code as I use another macro that renames sheet Codenames and this won't work if the VBA code is protected.

Does anyone have any ideas to hide Hide/protect these two macros to make them a bit harder for someone snooping about to find what the password is or even run these two macros.

The Protect macro is as follows:

Sub ProtectAll()


    Dim ws As Worksheet


    sSheet = Control.Name
    sSheet1 =  TestSheet1.Name
    sSheet2 = TestSheet2.Name


    For Each ws In ThisWorkbook.Worksheets
        Select Case ws.Name
        Case sSheet1, sSheet2
        Case Else
            Application.DisplayAlerts = False
            ws.Protect Password:="Password", userinterfaceonly:=True
            ws.EnableOutlining = True


            Application.DisplayAlerts = True
        End Select
    Next ws


End Sub

Re: Code Protection Stops Re-Naming Codename

Hi guys

I had another thought about this problem.

Regarding the original Problem, I was wanting to find away to rename the sheet "codename" when the VBA code was protected. As you guys have shown, this looks like it is not possible.

So instead, Is there away to hide VBA code rather than protect it? Cause that could work. Basically, The main reason I wanted the code protected was so that the user could not find out the Password for the sheet protection which I use in a Protect/Unprotect Macro (As shown below). If I could just 'Hide these two macros' to make them a bit harder for someone snooping about to find then that would solve the whole problem of the Code Protection Stops Re-Naming Codename.

The Protect macro is as follows:

Sub ProtectAll()


    Dim ws As Worksheet


    sSheet = Control.Name
    sSheet1 =  TestSheet1.Name
    sSheet2 = TestSheet2.Name


    For Each ws In ThisWorkbook.Worksheets
        Select Case ws.Name
        Case sSheet1, sSheet2
        Case Else
            Application.DisplayAlerts = False
            ws.Protect Password:="*****", userinterfaceonly:=True
            ws.EnableOutlining = True


            Application.DisplayAlerts = True
        End Select
    Next ws


End Sub

Re: Stop Creating Ref# When I Delete A Linked Sheet

Yeah, that is my plan if nobody has a solution.

I'm just trying to keep my work looking tidy so i'd prefer to be able to delete sheets if they arent required and create them if they are required.

Hi All

I have a Main sheet that has many formula on it that link to several Data sheets. Now sometime I want to delete the data sheets and recreate them by copying a Data template sheet and remaming it to the same name as whatever my data sheet was called.

I see that this basically causes a Ref#! error in all the formula on my main sheet so when the data sheet is recreated it wont recognise it.

How can i stop it changing the formula path to Ref#!?

Thanks

James