Posts by Dma212

Resolved thanks again ?

This worked perfectly thank you so much!

Hi there- i was wondering if anyone had code to save a selected email's attachments to folder P:\Coventry i have looked online but have struggled to see where to adapt code for my own use- many thanks in advance!

figured it out

code below:

Worksheets("Supplierdata").Range("P2:R200").Select

Worksheets("Supplierdata").Sort.SortFields.Clear

Worksheets("Supplierdata").Sort.SortFields.Add Key:=Range( _

"R3:R200"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _

xlSortNormal

With Worksheets("Supplierdata").Sort

.SetRange Range("P2:R200")

.Header = xlYes

.MatchCase = False

.Orientation = xlTopToBottom

.SortMethod = xlPinYin

.Apply

End With

Hi All, so i have an issue with my code. when i am on the tab 'supplier data' my code works fine. however when i insert this macro in to my overall macro that runs 8+ macros using 'call' it no longer works.

i suspect its because i'm using the 'select' function, but i'm not smart enough to figure out how to fix it!

many thanks as always for your assistance guys

...

Quote from Mumps

Place this macro in the Report workbook. Make sure that the two workbooks in question are the only two workbooks that are open.

Code

Sub Macro2()
    Dim wb As Workbook
    For Each wb In Workbooks
        If wb.Name <> ThisWorkbook.Name Then
            wb.Sheets("Export").Range("A2:D9").Copy
            Sheets("Data").Range("A2").PasteSpecial Paste:=xlPasteValues
        End If
    Next wb
    Application.CutCopyMode = False
End Sub

That's genius, thank you Mumps that's ideal.

Quote from royUK

What is the date part of the name referring to?

Hi Roy, that's the date i receive the updated data file, every monday.

so next week i would expect the file i need to copy & paste data from to have the name LS-GB_mbu_intl_rpt_20210517.xlsx

hope that answers your question

Hi Guys, hoping someone can help me.

i want to set a macro so that i copy a range from one excel file to another, however the source file name will change weekly, is there any way to have a macro that says something like, 'copy from file that begins with...' ? the current code i have is below:

Sub Macro2()

'

' Macro2 Macro

Workbooks("LS-GB_mbu_intl_rpt_20210510.xlsx").Worksheets("Export").Range("A2:D9").Copy

Workbooks("Reports.xlsm").Worksheets("Data").Range("A2").PasteSpecial Paste:=xlPasteValues

End Sub

name of the macro is macro2

source file is called LS-GB_mbu_intl_rpt_20210510.xlsx, however the numbers at the end will change every week.

destination file is called Reports.xlsm

many thanks in advance!

Quote from Carim

In message # 2 the suggestion is an Event Macro ....

An Event macro needs to be to stored in the Sheet Module :

1. Place your pointer on the tab (....where there is the sheet's name )

2. Right Click to open the Sub-Menu

3. Select the option View Code ( ... the fifth item in the list )

4. In the window which gets opened ... you can copy the Event macro

Now whenever you type in a given number number in column A ... your range will see its content cleared ....

Display More

I have typed in the number and it works perfectly- thank you again Carim

Please see attached file, i'm getting an error when i try to press 'run' on it

Quote from Carim

Hello again,

Do not know how familiar you are with Excel ...

In case of need, please attach a sample file ...

Hi Carim, very confident with formulas etc but am a total newbie to vba- trying to slowly teach myself !

Quote from Carim

Hello and Welcome to the Forum

You could test the following event macro

Code

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 1 Then Exit Sub
If Target.Row < 13 Then Exit Sub
If Not IsNumeric(Target) And Target < 1 Then Exit Sub
Range(Target.Offset(0, 5), Target.Offset(0, 5 + Target - 1)).ClearContents
End Sub

Hope this will help

thank you or your help Carim ?

at the moment I am getting an error message, it looks like something I’m doing wrong- for reference the name of the file is MacroLT and I tried to name the macro ‘clear’, my code looks like this:

When I try to run it I am getting the error ‘compile error: expected end sub’ I wonder it’s because I tried to name it ‘clear’ ? Thanks again for your help!

sub clear()
Private Sub MacroLT_Change(ByVal Target As Range)
If Target.Column <> 1 Then Exit Sub
If Target.Row < 13 Then Exit Sub
If Not IsNumeric(Target) And Target < 1 Then Exit Sub
Range(Target.Offset(0, 5), Target.Offset(0, 5 + Target - 1)).ClearContents

Hi guys, first time posting but have seen a lot of helpful advice with regards to vba. Was wondering if anyone can help me...

Basically what I want to do, is in range

F13:Be13

I want to clear the contents of the x number of cells, where x = a13

So basically, if a13= 6, I want to clear the first 6 columns of f13:be13 using code

In an ideal world I can then use it for every line to make this easier as there are 100’s of rows.

E.g. if A15= 4, then it clears the contents of the first 4 columns in f15:be15

thank you for any advice you can give!