Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 09-18-2019, 04:59 AM
rsrasc rsrasc is offline Need Help Creating a Macro Windows 10 Need Help Creating a Macro Office 2013
Competent Performer
Need Help Creating a Macro
 
Join Date: Mar 2014
Location: Germany
Posts: 148
rsrasc is on a distinguished road
Default Need Help Creating a Macro


Hi all,

I have a file with 26 sheets. On every sheet, I have a date in cell "A4.''

Every sheets has about 450 rows with information. Of course, some rows don't have any info on it.

In column B on every sheet there is some information on it.


I would like to have a macro that will copy the value of cell A4 all the way down in column C assuming there is a value in Column B.

See attached sample file with two tabs-name it "Before" and "After".

If anyone has a question, please let me know so I can clarify it.

Thank you in advance for your support.

Cheers!
Attached Files
File Type: xlsx Sample Macro.xlsx (48.5 KB, 12 views)
Reply With Quote
  #2  
Old 09-19-2019, 08:41 AM
NoSparks NoSparks is offline Need Help Creating a Macro Windows 7 64bit Need Help Creating a Macro Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 831
NoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really nice
Default

Give this a shot, if the coloring of the date cells was just for emphasis and not needed,
remove the variable from the declarations and the two color lines in the macro.
Code:
Sub rsrasc()
    Dim i As Long, ws As Worksheet
    Dim rng As Range, cel As Range
    Dim lr As Long, dte As Date, colour As String
    
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

For i = 1 To Sheets.Count
    With Sheets(i)
        dte = .Range("A4").Value
        colour = .Range("A4").Interior.Color
        lr = .Range("B" & .Rows.Count).End(xlUp).Row
        For Each cel In .Range("C6:C" & lr)
            If cel.Offset(, -1) <> "" Then
                With cel
                    .NumberFormat = "d-mmm-yy"
                    .Interior.Color = colour
                    .Value = dte
                End With
            End If
        Next cel
    End With
Next i

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

End Sub
Reply With Quote
  #3  
Old 09-20-2019, 01:39 AM
rsrasc rsrasc is offline Need Help Creating a Macro Windows 10 Need Help Creating a Macro Office 2013
Competent Performer
Need Help Creating a Macro
 
Join Date: Mar 2014
Location: Germany
Posts: 148
rsrasc is on a distinguished road
Default

Hello NoSparks,

Honestly, I was losing my faith on not getting a reply, and you came to my rescue.

Your macro is working great. I love it.

As always, thank you for your support.

Great job! Much appreciated.

Sure this macro will help me in the future for other things.

Sincerely,
rsrasc
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Need Help Creating a Macro help creating macro tingker Word VBA 4 01-08-2015 08:58 PM
Need Help Creating a Macro Need Help Creating Macro rsrasc Word VBA 1 10-22-2014 03:09 PM
Need Help Creating a Macro Need Help Creating Macro rsrasc Word VBA 4 03-28-2014 01:31 PM
Creating a MACRO Nikb3522 Word VBA 0 10-21-2010 05:55 PM
creating macro steveb Word VBA 0 08-14-2010 01:29 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 03:35 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft