Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 09-21-2018, 06:34 AM
rsrasc rsrasc is offline Concatenate Macro VBA Windows 10 Concatenate Macro VBA Office 2013
Competent Performer
Concatenate Macro VBA
 
Join Date: Mar 2014
Location: Germany
Posts: 141
rsrasc is on a distinguished road
Default Concatenate Macro VBA

Hi all,



I need help with a macro. In cell A1, I have the following number: 025

I have 15 different SHEETS with different numbers in cell A1.



In cell B10 (in all 15 sheets), I have the following information:

43610 General Advertising-Full Run


In cell A10, I have the following formula:


="NF-"&A$1&"-"&LEFT(B10,5)&"-"&MID(B10,7,45)&"--Budget 2019"


The result in cell A10, is the following:

NF-025-43610- General Advertising-Full Run--Budget 2019

So, the formula is working fine.


My goal is for the macro to be able to run the formula in cell A10 to 15 different sheets without having to do copy and paste.

Hope I can get some help.

Thank you in advance for your assistance and cooperation.

Cheers!





Reply With Quote
  #2  
Old 09-21-2018, 07:11 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Concatenate Macro VBA Windows 7 64bit Concatenate Macro VBA Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,422
Pecoflyer is a name known to allPecoflyer is a name known to allPecoflyer is a name known to allPecoflyer is a name known to allPecoflyer is a name known to allPecoflyer is a name known to all
Default

You don't really need a macro for this Just group the sheets ( while holding down the Shift key select the first and the last sheet tab)
Enter the formula in the first sheet
Automatically all other sheets will have the formula in the same position
Record a macro to automate the process if required
Reply With Quote
  #3  
Old 09-21-2018, 07:27 AM
rsrasc rsrasc is offline Concatenate Macro VBA Windows 10 Concatenate Macro VBA Office 2013
Competent Performer
Concatenate Macro VBA
 
Join Date: Mar 2014
Location: Germany
Posts: 141
rsrasc is on a distinguished road
Default

Quote:
Originally Posted by Pecoflyer View Post
You don't really need a macro for this Just group the sheets ( while holding down the Shift key select the first and the last sheet tab)
Enter the formula in the first sheet
Automatically all other sheets will have the formula in the same position
Record a macro to automate the process if required

I agree that I don't need a macro to accomplish this.

Seems to be really easy.

My intention is to be able to do it with a macro. That way I would like to be able to automate what I'm trying to do. The main purpose is that I can combine it with other macros and run it automatically.


I have tried the below code but does not work so I'm getting a Run-time error "1004". Application-define or object-defined error.

If someone can help me with this, I will appreciate it.


Will keep trying...


Code:
Sub Main()
  Dim ws As Worksheet, c As Long, s$
  For Each ws In Worksheets
    With ws
      c = .Cells(.Rows.Count, "B").End(xlUp).Row
      s = "=NF" & "-" & "A1" & Left(B10, 5) & "-" & Mid(B10, 7, 45) & "--Budget 2019"
      'Debug.Print s
      .Range("A10").Formula = s
      .Range("A10").Copy .Range(.Range("A10"), .Cells(c, "A"))
    End With
  Next ws
End Sub

Thanks all.
Reply With Quote
  #4  
Old 09-21-2018, 11:39 AM
NoSparks NoSparks is offline Concatenate Macro VBA Windows 7 64bit Concatenate Macro VBA Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 779
NoSparks has a spectacular aura aboutNoSparks has a spectacular aura aboutNoSparks has a spectacular aura about
Default

Code:
s = "=""NF-"" & A$1 & ""-"" & Left(B10, 5) & ""-"" & Mid(B10, 7, 45) & ""--Budget 2019"""
Reply With Quote
  #5  
Old 09-21-2018, 12:42 PM
rsrasc rsrasc is offline Concatenate Macro VBA Windows 10 Concatenate Macro VBA Office 2013
Competent Performer
Concatenate Macro VBA
 
Join Date: Mar 2014
Location: Germany
Posts: 141
rsrasc is on a distinguished road
Default

Quote:
Originally Posted by NoSparks View Post
Code:
s = "=""NF-"" & A$1 & ""-"" & Left(B10, 5) & ""-"" & Mid(B10, 7, 45) & ""--Budget 2019"""
Hi NoSparks,

You are great.

Thank you for taking your time with this.

It's working great!

Much appreciated.

Regards,
rsrasc
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Concatenate Macro VBA RIGHT Function With Concatenate (?) VBA-Need Help with Macro rsrasc Excel Programming 4 08-22-2018 12:17 PM
Concatenate Macro VBA Concatenate b16 Excel 1 01-09-2017 04:51 PM
Concatenate Macro VBA concatenate the currency too isasa74 Excel Programming 24 12-20-2015 06:01 AM
Concatenate Macro VBA Concatenate with @ symbol anickles Excel 2 02-06-2015 09:39 AM
If, move and concatenate in VBA devcon Excel Programming 0 07-04-2011 12:44 AM

Other Forums: Access Forums - Senior Forums

All times are GMT -7. The time now is 09:59 AM.


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