How to handle bookname changes in macro?

[Kirk^]

Hi folks,

I have a macro that takes info from my workbook [Client-Record.xlsm]DATA STORAGE' sheet and pastes it into my Master Books 2011 - 2012 Q1 Workbook.
However I have two issues I need help with.

Issue 1 .. [Client-Record.xlsm]DATA STORAGE doesn't get saved as Client-Record.xlsm. Therefore the macro won't work if it has been saved with a new name. I need the request for the current work book name to be flexible and check for and insert the name of the workbook.

Issue 2 ... If my Master Books 2011 - 2012 Q1 is already open my macro fails bringing up a this workbook is already open ..... error.

Can some one show me how to modify my code to allow for these two issues.

I am using excel 2010 and the macro code is below.

 Range("F14").Select
    Workbooks.Open Filename:="F:\Master Books 2011 - 2012 Q1.xlsm"
    Windows.Arrange ArrangeStyle:=xlHorizontal
    Windows("Client-Record.xlsm").Activate
    Sheets("EDIT CLIENT").Select
    Sheets("DATA STORAGE").Visible = True
    Windows("Master Books 2011 - 2012 Q1.xlsm").Activate
    Rows("2:2").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrBelow
    Range("A2").Select
    ActiveCell.FormulaR1C1 = "='[Client-Record.xlsm]DATA STORAGE'!R3C1"
    Range("A2").Select
    ActiveCell.FormulaR1C1 = "='[Client-Record.xlsm]DATA STORAGE'!R3C"
    Range("A2").Select
    Selection.AutoFill Destination:=Range("A2:AH2"), Type:=xlFillDefault
    Range("A2:AH2").Select
    ActiveWorkbook.Save
    ActiveWindow.Close
    Sheets("DATA STORAGE").Select
    ActiveWindow.SelectedSheets.Visible = False
    Range("B3").Select

Hoping someone can enlighten me.

Thanks in advance
Cheers
kirk

 

[AlphaFrog]

Give this a try...

Sub test()

    Dim wbMaster As Workbook
       
    On Error Resume Next
        Set wbMaster = Workbooks("Master Books 2011 - 2012 Q1.xlsm")
    On Error GoTo 0
    If wbMaster Is Nothing Then
        Set wbMaster = Workbooks.Open(Filename:="F:\Master Books 2011 - 2012 Q1.xlsm")
    End If
    
    With wbMaster.ActiveSheet
        .Rows("2:2").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrBelow
        .Range("A2:AH2").FormulaR1C1 = "='[" & ThisWorkbook.Name & "]DATA STORAGE'!R3C"
    End With
    wbMaster.Close SaveChanges:=True
    
    Range("B3").Select
    
End Sub

 

[Kirk^]

Wow that's impressive AlphaFrog ! Works a treat. many thanks

There was one thing I didn't think of though and if I can indulge you one more time i'd appreciate your input.

Sometimes the Master Books are not saved on the sheet that the info is pasted into. This sheet (which is constant) is called CURRENT CLIENTS in the Master Books 2011 - 2012 workbook.

Can you show me how to add that to the current code you have written?

Thanks again
Cheers
Kirk^

 

[AlphaFrog]

You're welcome.

Change this...
With wbMaster.ActiveSheet

To something like this...
With wbMaster.Sheets("CURRENT CLIENTS")

 

[Kirk^]

AlphaFrog you have saved me many hours of trail and error in just minutes !!!. I will learn much from your post.

Thank you so much

Regards
Kirk^

 

[Kirk^]

Hi Alpha Frog I need one more tweak if I may? I havent been able to work this one out.

How do I retain the formatting of the cells that are being pasted? They should retain their formatting as per their set up in the Data Storage work sheet.
This code is working great apart from the above issue

Sub test()

    Dim wbMaster As Workbook
       
    On Error Resume Next
        Set wbMaster = Workbooks("Master Books 2011 - 2012 Q1.xlsm")
    On Error GoTo 0
    If wbMaster Is Nothing Then
        Set wbMaster = Workbooks.Open(Filename:="F:\Master Books 2011 - 2012 Q1.xlsm")
    End If
    
    With wbMaster.ActiveSheet
        .Rows("2:2").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrBelow
        .Range("A2:AH2").FormulaR1C1 = "='[" & ThisWorkbook.Name & "]DATA STORAGE'!R3C"
    End With
    wbMaster.Close SaveChanges:=True
    
    Range("B3").Select
    
End Sub

Look forward to a suggestion for this.

Thanks so much
Regards
Kirk.

 

[AlphaFrog]

The code currently inserts a new row and then sets a formula that links the cells from Data Storage row 3. It doesn't really copy the data. My question is; would you rather copy the Values and Format from Data Storage or would you like to keep the current formulas and copy just the format?

Copy Values and Format:

    With wbMaster.Sheets("CURRENT CLIENTS")
        .Rows("2:2").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrBelow
        ThisWorkbook.Sheets("Data Storage").Range("A3:AH3").Copy Destination:=.Range("A2")  [COLOR="Green"]' Copy Values, Formulas, Formats[/COLOR]
        .Range("A2:AH2").Value = ThisWorkbook.Sheets("Data Storage").Range("A3:AH3").Value  [COLOR="Green"]' Replace any copied formulas with their values[/COLOR]
    End With

Link formulas and paste formats:

    With wbMaster.Sheets("CURRENT CLIENTS")
        .Rows("2:2").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrBelow
        .Range("A2:AH2").FormulaR1C1 = "='[" & ThisWorkbook.Name & "]DATA STORAGE'!R3C"
            [COLOR="Green"]' Copy-Paste formats only[/COLOR]
            ThisWorkbook.Sheets("Data Storage").Range("A3:AH3").Copy
            .Range("A2").PasteSpecial xlPasteFormats, xlPasteSpecialOperationNone, False, False
            Application.CutCopyMode = True
    End With

 

[Kirk^]

Hi AlphaFrog

After a quick test run the latter code appears to have resolved my reqquest.

The DATA STORAGE sheet stores all the info of a customers engagement with us and it has 3 stages of input.

Stage 1 Book The job
Stage 2 Bill the job
Stage 3 Monitor Payment of The job.

Each new entry goes into the customers record with us and is recorded on their individaul datastorage sheets.
These entries and the job progress are monitored by myself and my wife on the CURRENT CLIENTS worksheet so that we know where we are at on a daily basis.

Once again you have impressed me with the tiniest amount of code to do what I need .

Many thanks
Regards
Kirk