Posts by michibahn

Hi All,

Anyone here can help on a VBA script to copy 1st and 2nd sheet from a closed workbook to currently opened workbook overwriting and contents of sheet 2 and 3?

The 1st and 2nd sheet names of the closed workbook changes dynamically so please don't rely on the sheet1 and sheet2 names.

The 1st sheet of current opened workbook that will be copied onto is static and contains all the formulas and calculations based from sheets 2 and 3 on the same opened workbook. The sheets 2 and 3 contains the contents from sheet 1 and 2 from the closed workbook.

Appreciate your help in advance.

Thanks!

EDIT: cross posting it also here to get a solution >> https://www.mrexcel.com/forum/…ntly-opened-workbook.html

Re: Modify my code to copy ranges to email

Quote from ashu1990;745975

this codes copy charts into email body, hope you will be able to ammend it into yours for your better learning.

Code

Sub CopyAndPasteToMailBody()
    Set mailApp = CreateObject("Outlook.Application")
    Set mail = mailApp.CreateItem(olMailItem)
    mail.Display
    Set wEditor = mailApp.ActiveInspector.wordEditor
    ActiveChart.ChartArea.Copy
    wEditor.Application.Selection.Paste
End Sub

thanks ashu... I have actually tried that but unfortunately if I insert it to my existing code, it opens up 2 new emails.. 1 for my original code and 1 for the chart i'm really not good in VBA and i'm hoping someone can help me out..

Hi Guys,

I have the code below to email the results of whatever data is on A3 and B3 if I typed "Y" in M3. It will open up an email body using the Subject line on M2 and using the email address on M1

I'm planning to add a chart which I will place somewhere in A20 as part of the email. I realized it will be easier for me if the code will just copy whatever is on column A upto J and rows 1 to 100 including charts, pictures etc...

Is this doable? Thanks in advance!

Private Sub Worksheet_Change(ByVal Target As Range)Dim tRow As Integer
Dim i As Integer
If Target.Column >= 17 Or Target.Column <= 12 Then Exit Sub
If Target.Count > 1 Then Exit Sub
If Target = "" Then Exit Sub
If Target = "Y" Or Target = "y" Then








tRow = Target.Row
i = Target.Column








Dim temp As String
Dim ws As Worksheet
Dim rowNr As Integer
Dim strTo As String
Dim Acc As String








strTo = ""








Set ws = ActiveSheet
rowNr = tRow








 If ws.Cells(rowNr, i).Value = "Y" Or ws.Cells(rowNr, i).Value = "y" Then
        temp = ws.Cells(2, i).Value
        strTo = ws.Cells(1, i).Value
        Acc = ws.Cells(rowNr, 1).Value
        BCC = ws.Cells(rowNr, 2).Value
                  




'Declare and establish the Object variables for Outlook.
Dim objOutlook As Object
Dim objNameSpace As Object
Dim objInbox As Object
Dim objMailItem As Object
Set objOutlook = CreateObject("Outlook.Application")
Set objNameSpace = objOutlook.GetNamespace("MAPI")
Set objInbox = objNameSpace.Folders(1)
Set objMailItem = objOutlook.CreateItem(0)
        




'Display the email message
With objMailItem
.to = strTo
.Subject = temp
.Body = "**** Summary of Study ****" & Chr(10) & vbLf & _
"No. of Subjects Dosed = " & Acc & Chr(10) & _
"No. of Failures = " & BCC & Chr(10) & vbLf & _
"**** End of Summary ****" & Chr(10) & vbLf & _
"Thank You!" & Chr(10) & _
"BioPharma Services Inc." & Chr(10) & _
"4000 Weston Rd." & Chr(10) & _
"Toronto, Ontario M9L 3A2" & Chr(10) & _
"www.biopharmaservices.com"




    
'Change to .Send if you want to just send it.
.Display
End With








'Release object variables from system memory.
Set objOutlook = Nothing
Set objNameSpace = Nothing
Set objInbox = Nothing
Set objMailItem = Nothing








End If
End If
End Sub

Re: Copy Formula and Output from 2 different excel file

Thanks for your usual help. I tried replacing the myForm but end up getting error on.. myRange.Formula = myForm & "*" & myMult

Any ideas?

Quote from Luke M;726395

You need to change the myForm variable then.

Code

myForm = "=IFERROR('[Book1.xls]Concentration Table'!C8+'[Book2.xls]Concentration Table'!C8,"""",)"

Re: Copy Formula and Output from 2 different excel file

Hey guys! Need your help again.

How do I insert the IFERROR in my formula below? I want to get rid of the #VALUE! in some cells and put a blank space instead?

TIA!

Sub ne()
Dim myForm As String
Dim myRange As Range
Dim myMult As Double
myForm = "='[Book1.xls]Concentration Table'!C8+'[Book2.xls]Concentration Table'!C8"

'Used Application.InputBox so user can select cells with mouse, rather than typing
Set myRange = Application.InputBox("What cells do you want calculations in?", "Where to go", , , , , ,
'Used Evaluate so user can type fractions, like 4/6
myMult = Evaluate(InputBox("What should we multiply Book2's number by?", "Multiplier", 1))
myRange.Formula = myForm & "*" & myMult
End Sub

Re: Copy Formula and Output from 2 different excel file

Thank you so much! will test the results now.. appreciate your help

Re: Copy Formula and Output from 2 different excel file

Thanks for the reply... when I tried the macro it errors in this line myRange.Formula = myForm * myMult

Re: Copy Formula and Output from 2 different excel file

Hi,

Just reviving this thread to get some inputs in adding a pop up screen to enter a multiplier or expression in my macro. Basically, the macro below which adds the contents of cell C8 in Book1 and Book2. I need to add a multiplier in cell C8 of Book2 where in the pop screen will let me enter the multiplier or an expression.

So the new formula will be C8+C8(any number or expression eg. 4/6)

Any ideas?

Sub ne()
Dim myForm As String
Dim myRange As String
myForm = "='[Book1.xls]Concentration Table'!C8+'[Book2.xls]Concentration Table'!C8"

myRange = InputBox("What cells do you want calculations in?")
ThisWorkbook.Worksheets("Sheet1").Range(myRange).Formula = myForm
End Sub

Thanks!

Re: Copy Formula and Output from 2 different excel file

It worked great Luke thank you so much!

Re: Copy Formula and Output from 2 different excel file

Quote from Luke M;720727

I'd probably just write some formulas, or have the macro write the formulas to do the sum. Easiest way would be to open both file, write a formula like:
='[Book1.xls]Sheet 1'!A1+'[Book2.xls]Sheet 1'!A1
Then close both the source workbooks. Formula in your 3rd workbook will now have full file paths. This is what you need to use either manually, or have the macro write. Then, if desired, do a Copy, Paste Specials - Values.

Thank you so much Luke! However, is it possible to have an option that I can enter the Cell #s ranges where I want to copy formula in my new excel file?

Hi Guys,

I'm trying to create a new Excel Output File that contains the calculation taken from 2 different excel files.

Basically, I want to output the result of the following:

Excel File1 (A1) + Excel File 2 (A1) = Output File (A1)
Excel File1 (A2) + Excel File 2 (A2) = Output File (A2)
Excel File1 (B1) + Excel File 2 (B1) = Output File (B1
and so on...

Appreciate if you have any ideas on how to create a macro for this scenario to call Excel File1 and Enter A1 then Call Excel File2 and Enter A1 then Output in in the new Output file.

Thanks!

Re: Custom Label and Count Duplicates

@Fotis.. added the sample output in the first post..

@NVBC.. thanks a lot it worked!

Hi,

Is is possible to insert a custom label in duplicates after counting? I want to achieve Column D in the spreadsheet below:

[ATTACH=CONFIG]51879[/ATTACH]

A B C D
[TABLE="width: 693"]

Shipping Name

Address

City

Count

Black Badger (The)

55 Water St North

Cambridge

1 of 2

Black Badger (The)

55 Water St North

Cambridge

2 of 2

Charaka Kithulegoda c/o ING

3389 Steeles Avenue East, Suite 1100

Thornhill

1 of 1

County Bistro (Blu)

92 King Street West

Cobourg

1 of 3

County Bistro (Blu)

92 King Street West

Cobourg

2 of 3

County Bistro (Blu)

92 King Street West

Cobourg

3 of 3

[/TABLE]

TIA!

Re: Search Macro to display all possible details

Quote from PCI;639469

A popup window is not to small to make the complete display?

ok.. I just made a userform that when they exit, it will display all contents again thanks a lot for your help!

Re: Search Macro to display all possible details

Quote from PCI;639391

What about next macro
In the file attached use Control + G to launch it

Code

Sub In_Msg()
Dim MyInfo As Variant
    MyInfo = Application.InputBox("Enter a reference to find")
    If (ActiveSheet.AutoFilterMode) Then ActiveSheet.AutoFilterMode = False '  REMOVE  AUTOFILTER  IF  EXIST
    ActiveSheet.Range("$A$1:$C$7").AutoFilter Field:=1, Criteria1:=MyInfo & "*", Operator:=xlFilterValues
End Sub

thanks a lot! the macro works but can the output be a popup window? also can the headers (Name data1 data2) be included the in output? thanks a lot again!

Re: Search Macro to display all possible details

appreciate if someone can help me..

Re: Search Macro to display all possible details

Quote from PCI;639256

Did you ever try AutoFilter and use Text Filters with Tek content

I can use the auto filter but I want something easier for a not so techie user. that's why I thought of having a button search macro and popup output. Thanks!

Hi,

Can you please give me an idea how to create a macro that will display the contents of the entire row in reference to column A?

Basically, in the data below, if I search for "tek" it will display all instances of the word "tek" from column a including column b and c.

A B C
Name data1 data2
Tek 1 2
Tekx 3 3
Tekc 3 4
Gup 3 4
Gaz 3 3
Gazc 5 5

So my output popup would be like below. It should show the Column labels not just the contents
Name data1 data2
Tek 1 2
Tekx 3 3
Tekc 3 4

Thanks in advance!

Re: Call a formula in a cell and use it on the next cell

Quote from NBVC;632446

You will need VBA to do that and it is out of my scope here...

Here is a link that you might be able to use:

http://answers.microsoft.com/e…5a-e011-8dfc-68b599b31bf5

this is hard!

Re: Call a formula in a cell and use it on the next cell

Quote from royUK;632435

I don't think there's a way round that. I've tried various options before protecting the sheet, none worked.

hhmm... too bad..