VBA to Copy/Paste Formula Into Next Cell (Column)

G

Gerwe1988

New Member
Joined
Mar 21, 2022
Messages
10
Office Version
  1. 2019
Platform
  1. Windows
Hello everyone,

I'm new to macros and need some assistance with establishing a code for my forecast file. The macro will scan row 3 for the current "reporting" month, to decide which column to update (AS). The macro will then copy & paste the prior months formula from one cell to next, into the current "reporting" month. The prior months formula will need to be hard coded. There are certain rows that need to be updated in this process (Rows 16,17, 27, 28). For example, row 16 "New patients", the prior months formula (Jan-22, AR16), will be copied and pasted into the next column (AS16). The prior months formula (AR16) will be hard coded. Thanks in advance!
 

Attachments

  • Example.JPG
    Example.JPG
    97.9 KB · Views: 21
Sort by date Sort by votes
play with the code below to know what you need
VBA Code: 
Sub Gerwe1988()
    
    Dim rFind As Range, iCharNum As Integer, LColChar As String
    Dim OldFormula As String
    
    With Range("A3:J3")
        Set rFind = .Find(What:="Reporting", LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)
        If Not rFind Is Nothing Then
            iCharNum = rFind.Column
            LColChar = Split(Cells(1, iCharNum).Address, "$")(1)
        End If
    End With
    
'Copy cell and paste the [ VALUE ] to the target cell
    Range(LColChar & "8").Copy
    Range("AR8").PasteSpecial Paste:=xlPasteValues
 
'Copy cell and paste the [ FORMULA ] to the target cell
    Range("AR12").Formula = Range(LColChar & "12").Formula
    
'Following Code [ SAVE ] the formula to the variable
    OldFormula = Range(LColChar & "8").Formula
    
'Paste the formula to the target cell from the old cell
    Range("AR3").Value = OldFormula
    
'Paste the [ VALUE ] to the original cell address
    Range(LColChar & "8").Copy
    Range("E8").PasteSpecial Paste:=xlPasteValues
    
'CLEARS the Copy Selection
    Application.CutCopyMode = False
    
End Sub     'End of Gerwe1988


also, to answer your question.

Q1 - How would you specify a sheet or tab within this code?
VBA Code: 
Dim WSCopy as worksheet
set WSCopy = worksheets("SheetName")
WSCopy.activate

Q2 - Is it possible to paste the code in the next column (month), rather than a specific location or cell?
A2 - yes, that depends on where you want to copy the formula/value

Q3 - Also, how would you copy, paste values (hardcode) the reporting month column so that the formula isn't active in two columns?
A3.1 - if what you mean by "hard coded" is the value you may use code below
VBA Code: 
        Range(LColChar & "8").Copy
        Range("AR8").PasteSpecial Paste:=xlPasteValue

A3.2 - but if you need to copy the formula use code below
VBA Code: 
Range("AR8").Formula = Range(LColChar & "8").Formula

A3.3 - But if you mean, copy the formula of that cell, paste the formula to another cell and then retain the value from the original cell (without formula)? then we need a string variable to hold the formula, then save/copy the formula of that cell to the string variable. Then we copy/paste the formula to the target cell. use the code below
VBA Code: 
    Dim OldFormula as string
    OldFormula = Range(LColChar & "8").Formula
    Range("AR3").Value = OldFormula
        
    Range(LColChar & "8").Copy
    Range("E8").PasteSpecial Paste:=xlPasteValues

Tell us if it helps....
 
Upvote 0

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
airforceone said:
play with the code below to know what you need
VBA Code: 
Sub Gerwe1988()
  
    Dim rFind As Range, iCharNum As Integer, LColChar As String
    Dim OldFormula As String
  
    With Range("A3:J3")
        Set rFind = .Find(What:="Reporting", LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)
        If Not rFind Is Nothing Then
            iCharNum = rFind.Column
            LColChar = Split(Cells(1, iCharNum).Address, "$")(1)
        End If
    End With
  
'Copy cell and paste the [ VALUE ] to the target cell
    Range(LColChar & "8").Copy
    Range("AR8").PasteSpecial Paste:=xlPasteValues
 
'Copy cell and paste the [ FORMULA ] to the target cell
    Range("AR12").Formula = Range(LColChar & "12").Formula
  
'Following Code [ SAVE ] the formula to the variable
    OldFormula = Range(LColChar & "8").Formula
  
'Paste the formula to the target cell from the old cell
    Range("AR3").Value = OldFormula
  
'Paste the [ VALUE ] to the original cell address
    Range(LColChar & "8").Copy
    Range("E8").PasteSpecial Paste:=xlPasteValues
  
'CLEARS the Copy Selection
    Application.CutCopyMode = False
  
End Sub     'End of Gerwe1988


also, to answer your question.

Q1 - How would you specify a sheet or tab within this code?
VBA Code: 
Dim WSCopy as worksheet
set WSCopy = worksheets("SheetName")
WSCopy.activate

Q2 - Is it possible to paste the code in the next column (month), rather than a specific location or cell?
A2 - yes, that depends on where you want to copy the formula/value

Q3 - Also, how would you copy, paste values (hardcode) the reporting month column so that the formula isn't active in two columns?
A3.1 - if what you mean by "hard coded" is the value you may use code below
VBA Code: 
        Range(LColChar & "8").Copy
        Range("AR8").PasteSpecial Paste:=xlPasteValue

A3.2 - but if you need to copy the formula use code below
VBA Code: 
Range("AR8").Formula = Range(LColChar & "8").Formula

A3.3 - But if you mean, copy the formula of that cell, paste the formula to another cell and then retain the value from the original cell (without formula)? then we need a string variable to hold the formula, then save/copy the formula of that cell to the string variable. Then we copy/paste the formula to the target cell. use the code below
VBA Code: 
    Dim OldFormula as string
    OldFormula = Range(LColChar & "8").Formula
    Range("AR3").Value = OldFormula
      
    Range(LColChar & "8").Copy
    Range("E8").PasteSpecial Paste:=xlPasteValues

Tell us if it helps....
Click to expand...
So, the "Reporting" cell indicates which column the prior months formula will be pasted into. I would like to use it as an identifier, so I don't have to manually update the target cells each month/time I run the macro. In the xl2bb that I uploaded, the "Reporting" column is G. The "New Patients" row (16) will need to be updated for this specific tab and example. So, I need to copy the formula from the prior month (F16) and paste it into the current "Reporting" cell (G16). F16s formula needs to be hardcoded into a value after the formula has been pasted into the next cell. Basically, the macro is copying and pasting formulas into the next column/cell (left to right) each month. The rows will be static for each tab, with the columns changing/updating each month.
 
Upvote 0
See if below code satisfy your needs
VBA Code: 
Sub Gerwe1988()
    
    Dim rFind As Range, iCharNum As Integer
    Dim From_Col_Char As String, To_Col_Char As String
    Dim OldFormula(10) As String
    
    With Range("A3:J3")
        Set rFind = .Find(What:="Reporting", LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)
        If Not rFind Is Nothing Then
            iCharNum = rFind.Column
            From_Col_Char = Split(Cells(1, iCharNum).Address, "$")(1)
            To_Col_Char = Split(Cells(1, iCharNum + 1).Address, "$")(1)
        End If
    End With
        
    OldFormula(0) = Range(From_Col_Char & "7").Formula
    OldFormula(1) = Range(From_Col_Char & "8").Formula
    OldFormula(2) = Range(From_Col_Char & "12").Formula
    OldFormula(3) = Range(From_Col_Char & "13").Formula
    OldFormula(4) = Range(From_Col_Char & "15").Formula
    OldFormula(5) = Range(From_Col_Char & "16").Formula
    OldFormula(6) = Range(From_Col_Char & "17").Formula
    OldFormula(7) = Range(From_Col_Char & "18").Formula
    OldFormula(8) = Range(From_Col_Char & "27").Formula
    OldFormula(9) = Range(From_Col_Char & "28").Formula
    
    Range(To_Col_Char & "7").Value = OldFormula(0)
    Range(To_Col_Char & "8").Value = OldFormula(1)
    Range(To_Col_Char & "12").Value = OldFormula(2)
    Range(To_Col_Char & "13").Value = OldFormula(3)
    Range(To_Col_Char & "15").Value = OldFormula(4)
    Range(To_Col_Char & "16").Value = OldFormula(5)
    Range(To_Col_Char & "17").Value = OldFormula(6)
    Range(To_Col_Char & "18").Value = OldFormula(7)
    Range(To_Col_Char & "27").Value = OldFormula(8)
    Range(To_Col_Char & "28").Value = OldFormula(9)
    
    Range(From_Col_Char & "7").Copy
    Range(From_Col_Char & "7").PasteSpecial Paste:=xlPasteValues
    
    Range(From_Col_Char & "8").Copy
    Range(From_Col_Char & "8").PasteSpecial Paste:=xlPasteValues
    
    Range(From_Col_Char & "12").Copy
    Range(From_Col_Char & "12").PasteSpecial Paste:=xlPasteValues
    
    Range(From_Col_Char & "13").Copy
    Range(From_Col_Char & "13").PasteSpecial Paste:=xlPasteValues
    
    Range(From_Col_Char & "15").Copy
    Range(From_Col_Char & "15").PasteSpecial Paste:=xlPasteValues
    
    Range(From_Col_Char & "16").Copy
    Range(From_Col_Char & "16").PasteSpecial Paste:=xlPasteValues
    
    Range(From_Col_Char & "17").Copy
    Range(From_Col_Char & "17").PasteSpecial Paste:=xlPasteValues
    
    Range(From_Col_Char & "18").Copy
    Range(From_Col_Char & "18").PasteSpecial Paste:=xlPasteValues
    
    Range(From_Col_Char & "27").Copy
    Range(From_Col_Char & "27").PasteSpecial Paste:=xlPasteValues
    
    Range(From_Col_Char & "28").Copy
    Range(From_Col_Char & "28").PasteSpecial Paste:=xlPasteValues
    
'CLEARS the Copy Selection
    Application.CutCopyMode = False
        
End Sub     'End of Gerwe1988
 
Upvote 0
airforceone said:
See if below code satisfy your needs
VBA Code: 
Sub Gerwe1988()
  
    Dim rFind As Range, iCharNum As Integer
    Dim From_Col_Char As String, To_Col_Char As String
    Dim OldFormula(10) As String
  
    With Range("A3:J3")
        Set rFind = .Find(What:="Reporting", LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)
        If Not rFind Is Nothing Then
            iCharNum = rFind.Column
            From_Col_Char = Split(Cells(1, iCharNum).Address, "$")(1)
            To_Col_Char = Split(Cells(1, iCharNum + 1).Address, "$")(1)
        End If
    End With
      
    OldFormula(0) = Range(From_Col_Char & "7").Formula
    OldFormula(1) = Range(From_Col_Char & "8").Formula
    OldFormula(2) = Range(From_Col_Char & "12").Formula
    OldFormula(3) = Range(From_Col_Char & "13").Formula
    OldFormula(4) = Range(From_Col_Char & "15").Formula
    OldFormula(5) = Range(From_Col_Char & "16").Formula
    OldFormula(6) = Range(From_Col_Char & "17").Formula
    OldFormula(7) = Range(From_Col_Char & "18").Formula
    OldFormula(8) = Range(From_Col_Char & "27").Formula
    OldFormula(9) = Range(From_Col_Char & "28").Formula
  
    Range(To_Col_Char & "7").Value = OldFormula(0)
    Range(To_Col_Char & "8").Value = OldFormula(1)
    Range(To_Col_Char & "12").Value = OldFormula(2)
    Range(To_Col_Char & "13").Value = OldFormula(3)
    Range(To_Col_Char & "15").Value = OldFormula(4)
    Range(To_Col_Char & "16").Value = OldFormula(5)
    Range(To_Col_Char & "17").Value = OldFormula(6)
    Range(To_Col_Char & "18").Value = OldFormula(7)
    Range(To_Col_Char & "27").Value = OldFormula(8)
    Range(To_Col_Char & "28").Value = OldFormula(9)
  
    Range(From_Col_Char & "7").Copy
    Range(From_Col_Char & "7").PasteSpecial Paste:=xlPasteValues
  
    Range(From_Col_Char & "8").Copy
    Range(From_Col_Char & "8").PasteSpecial Paste:=xlPasteValues
  
    Range(From_Col_Char & "12").Copy
    Range(From_Col_Char & "12").PasteSpecial Paste:=xlPasteValues
  
    Range(From_Col_Char & "13").Copy
    Range(From_Col_Char & "13").PasteSpecial Paste:=xlPasteValues
  
    Range(From_Col_Char & "15").Copy
    Range(From_Col_Char & "15").PasteSpecial Paste:=xlPasteValues
  
    Range(From_Col_Char & "16").Copy
    Range(From_Col_Char & "16").PasteSpecial Paste:=xlPasteValues
  
    Range(From_Col_Char & "17").Copy
    Range(From_Col_Char & "17").PasteSpecial Paste:=xlPasteValues
  
    Range(From_Col_Char & "18").Copy
    Range(From_Col_Char & "18").PasteSpecial Paste:=xlPasteValues
  
    Range(From_Col_Char & "27").Copy
    Range(From_Col_Char & "27").PasteSpecial Paste:=xlPasteValues
  
    Range(From_Col_Char & "28").Copy
    Range(From_Col_Char & "28").PasteSpecial Paste:=xlPasteValues
  
'CLEARS the Copy Selection
    Application.CutCopyMode = False
      
End Sub     'End of Gerwe1988
Click to expand...
Thank you so much for your time and assistance with this! The code works like a charm on the sample file, however when I try to implement the code into my forecast file, I come across the following error (See .png) . My Vba code so far is posted below:

Sub RUNN()


Dim rFind As Range, iCharNum As Integer
Dim From_Col_Char As String, To_Col_Char As String
Dim OldFormula(0) As String
Dim WS As Worksheet
Set WS = ThisWorkbook.Worksheets("Libre CGM")
Set WX = ThisWorkbook.Worksheets("Libre Ship")



With WS.Range("A3:DA3")
Set rFind = .Find(What:="Prior", LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)
If Not rFind Is Nothing Then
iCharNum = rFind.Column
From_WS.Range.Col_Char = Split(Cells(1, iCharNum).Address, "$")(1)
To_WS.Range.Col_Char = Split(Cells(1, iCharNum + 1).Address, "$")(1)
End If
End With

OldFormula(0) = WS.Range(From_Col_Char & "16").Formula
OldFormula(1) = WX.Range(From_Col_Char & "16").Formula


WS.Range(To_Col_Char & "16").Value = OldFormula(0)
WX.Range(To_Col_Char & "16").Value = OldFormula(1)



WS.Range(From_Col_Char & "16").Copy
WS.Range(From_Col_Char & "16").PasteSpecial Paste:=xlPasteValues
WX.Range(From_Col_Char & "16").Copy
WX.Range(From_Col_Char & "16").PasteSpecial Paste:=xlPasteValues



'CLEARS the Copy Selection
Application.CutCopyMode = False

End Sub
 

Attachments

  • 1649207866955.png
    1649207866955.png
    23.2 KB · Views: 3
Upvote 0
are the variable
WX
To_WS.Range.Col_Char
From_WS.Range.Col_Char

VBA Code: 
Set WX = ThisWorkbook.Worksheets("Libre Ship")
From_WS.Range.Col_Char = Split(Cells(1, iCharNum).Address, "$")(1)
To_WS.Range.Col_Char = Split(Cells(1, iCharNum + 1).Address, "$")(1)
declared somewhere?
can you highlight what line is the problem?
 
Last edited:
Upvote 0
airforceone said:
are the variable
WX
To_WS.Range.Col_Char
From_WS.Range.Col_Char

VBA Code: 
Set WX = ThisWorkbook.Worksheets("Libre Ship")
From_WS.Range.Col_Char = Split(Cells(1, iCharNum).Address, "$")(1)
To_WS.Range.Col_Char = Split(Cells(1, iCharNum + 1).Address, "$")(1)
declared somewhere?
can you highlight what line is the problem?
Click to expand...

Line with the error: "OldFormula(0) = WS.Range(From_Col_Char & "16").Formula", also in colored text below. I will be updating multiple tabs in my workbook with the same copy/paste process that you outlined and we discussed. I'm not sure if I have to break it up into different subs for each tab.



Sub RUNN()


Dim rFind As Range, iCharNum As Integer
Dim From_Col_Char As String, To_Col_Char As String
Dim OldFormula(0) As String
Dim WS As Worksheet
Dim WX As Worksheet
Set WS = ThisWorkbook.Worksheets("Libre CGM")
Set WX = ThisWorkbook.Worksheets("Sheet1")


With WS.Range("A3:DA3")
Set rFind = .Find(What:="Prior", LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)
If Not rFind Is Nothing Then
iCharNum = rFind.Column
From_WS.Range.Col_Char = Split(Cells(1, iCharNum).Address, "$")(1)
To_WS.Range.Col_Char = Split(Cells(1, iCharNum + 1).Address, "$")(1)
End If
End With

With WX.Range("A3:DA3")
Set rFind = .Find(What:="Prior", LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)
If Not rFind Is Nothing Then
iCharNum = rFind.Column
From_WX.Range.Col_Char = Split(Cells(1, iCharNum).Address, "$")(1)
To_WX.Range.Col_Char = Split(Cells(1, iCharNum + 1).Address, "$")(1)
End If
End With

OldFormula(0) = WS.Range(From_Col_Char & "16").Formula
OldFormula(1) = WX.Range(From_Col_Char & "16").Formula


WS.Range(To_Col_Char & "16").Value = OldFormula(0)
WX.Range(To_Col_Char & "16").Value = OldFormula(1)



WS.Range(From_Col_Char & "16").Copy
WS.Range(From_Col_Char & "16").PasteSpecial Paste:=xlPasteValues
WX.Range(From_Col_Char & "16").Copy
WX.Range(From_Col_Char & "16").PasteSpecial Paste:=xlPasteValues



'CLEARS the Copy Selection
Application.CutCopyMode = False

End Sub
 
Upvote 0
Gerwe1988 said:
Line with the error: "OldFormula(0) = WS.Range(From_Col_Char & "16").Formula", also in colored text below. I will be updating multiple tabs in my workbook with the same copy/paste process that you outlined and we discussed. I'm not sure if I have to break it up into different subs for each tab.



Sub RUNN()


Dim rFind As Range, iCharNum As Integer
Dim From_Col_Char As String, To_Col_Char As String
Dim OldFormula(0) As String
Dim WS As Worksheet
Dim WX As Worksheet
Set WS = ThisWorkbook.Worksheets("Libre CGM")
Set WX = ThisWorkbook.Worksheets("Sheet1")


With WS.Range("A3:DA3")
Set rFind = .Find(What:="Prior", LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)
If Not rFind Is Nothing Then
iCharNum = rFind.Column
From_WS.Range.Col_Char = Split(Cells(1, iCharNum).Address, "$")(1)
To_WS.Range.Col_Char = Split(Cells(1, iCharNum + 1).Address, "$")(1)
End If
End With

With WX.Range("A3:DA3")
Set rFind = .Find(What:="Prior", LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)
If Not rFind Is Nothing Then
iCharNum = rFind.Column
From_WX.Range.Col_Char = Split(Cells(1, iCharNum).Address, "$")(1)
To_WX.Range.Col_Char = Split(Cells(1, iCharNum + 1).Address, "$")(1)
End If
End With

OldFormula(0) = WS.Range(From_Col_Char & "16").Formula
OldFormula(1) = WX.Range(From_Col_Char & "16").Formula


WS.Range(To_Col_Char & "16").Value = OldFormula(0)
WX.Range(To_Col_Char & "16").Value = OldFormula(1)



WS.Range(From_Col_Char & "16").Copy
WS.Range(From_Col_Char & "16").PasteSpecial Paste:=xlPasteValues
WX.Range(From_Col_Char & "16").Copy
WX.Range(From_Col_Char & "16").PasteSpecial Paste:=xlPasteValues



'CLEARS the Copy Selection
Application.CutCopyMode = False

End Sub
Click to expand...
Sorry mate but I'm at lost with the code above!
I don't know how you edited my code to your needs but running the code above generates quite a few error especially in the variable used
VBA Code: 
From_WX.Range.Col_Char
VBA Code: 
To_WX.Range.Col_Cha
what do you need the entry above? what do you want to achieve from it? it seems more error popup after the last code given...
kindly help us, help you... also would you be kind enough to use the code tag when pasting your code
 
Last edited:
Upvote 0
airforceone said:
Sorry mate but I'm at lost with the code above!
I don't know how you edited my code to your needs but running the code above generates quite a few error especially in the variable used
VBA Code: 
From_WX.Range.Col_Char
VBA Code: 
To_WX.Range.Col_Cha
what do you need the entry above? what do you want to achieve from it? it seems more error popup after the last code given...
kindly help us, help you... also would you be kind enough to use the code tag when pasting your code
Click to expand...
There are certain tabs in my file that will need to be updated with the vba. In the example above "sheet1" and "Libre CGM" are the tabs that need to undergo the macro formula change. I need assistance with updating the code that you provided before (below) to include multiple tabs. Could you provide an example of the following code addressing multiple tabs?


VBA Code: 
Sub Gerwe1988()
  
    Dim rFind As Range, iCharNum As Integer
    Dim From_Col_Char As String, To_Col_Char As String
    Dim OldFormula(10) As String
  
    With Range("A3:J3")
        Set rFind = .Find(What:="Reporting", LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)
        If Not rFind Is Nothing Then
            iCharNum = rFind.Column
            From_Col_Char = Split(Cells(1, iCharNum).Address, "$")(1)
            To_Col_Char = Split(Cells(1, iCharNum + 1).Address, "$")(1)
        End If
    End With
      
    OldFormula(0) = Range(From_Col_Char & "7").Formula
    OldFormula(1) = Range(From_Col_Char & "8").Formula
    OldFormula(2) = Range(From_Col_Char & "12").Formula
    OldFormula(3) = Range(From_Col_Char & "13").Formula
    OldFormula(4) = Range(From_Col_Char & "15").Formula
    OldFormula(5) = Range(From_Col_Char & "16").Formula
    OldFormula(6) = Range(From_Col_Char & "17").Formula
    OldFormula(7) = Range(From_Col_Char & "18").Formula
    OldFormula(8) = Range(From_Col_Char & "27").Formula
    OldFormula(9) = Range(From_Col_Char & "28").Formula
  
    Range(To_Col_Char & "7").Value = OldFormula(0)
    Range(To_Col_Char & "8").Value = OldFormula(1)
    Range(To_Col_Char & "12").Value = OldFormula(2)
    Range(To_Col_Char & "13").Value = OldFormula(3)
    Range(To_Col_Char & "15").Value = OldFormula(4)
    Range(To_Col_Char & "16").Value = OldFormula(5)
    Range(To_Col_Char & "17").Value = OldFormula(6)
    Range(To_Col_Char & "18").Value = OldFormula(7)
    Range(To_Col_Char & "27").Value = OldFormula(8)
    Range(To_Col_Char & "28").Value = OldFormula(9)
  
    Range(From_Col_Char & "7").Copy
    Range(From_Col_Char & "7").PasteSpecial Paste:=xlPasteValues
  
    Range(From_Col_Char & "8").Copy
    Range(From_Col_Char & "8").PasteSpecial Paste:=xlPasteValues
  
    Range(From_Col_Char & "12").Copy
    Range(From_Col_Char & "12").PasteSpecial Paste:=xlPasteValues
  
    Range(From_Col_Char & "13").Copy
    Range(From_Col_Char & "13").PasteSpecial Paste:=xlPasteValues
  
    Range(From_Col_Char & "15").Copy
    Range(From_Col_Char & "15").PasteSpecial Paste:=xlPasteValues
  
    Range(From_Col_Char & "16").Copy
    Range(From_Col_Char & "16").PasteSpecial Paste:=xlPasteValues
  
    Range(From_Col_Char & "17").Copy
    Range(From_Col_Char & "17").PasteSpecial Paste:=xlPasteValues
  
    Range(From_Col_Char & "18").Copy
    Range(From_Col_Char & "18").PasteSpecial Paste:=xlPasteValues
  
    Range(From_Col_Char & "27").Copy
    Range(From_Col_Char & "27").PasteSpecial Paste:=xlPasteValues
  
    Range(From_Col_Char & "28").Copy
    Range(From_Col_Char & "28").PasteSpecial Paste:=xlPasteValues
  
'CLEARS the Copy Selection
    Application.CutCopyMode = False
      
End Sub     'End of Gerwe1988
 
Upvote 0
update current code to the code below (UNTESTED) new lines are commented
VBA Code: 
Sub Gerwe1988()
 
    Dim rFind As Range, iCharNum As Integer
    Dim From_Col_Char As String, To_Col_Char As String
    Dim OldFormula(10) As String
    Dim LoopSHT as worksheets 'Declare variable as sheet for the loop

For Each loopSHT in Worksheets 'if all sheet have same format MUST NOT BE A PROBLEM
 
    With Range("A3:J3")
        Set rFind = .Find(What:="Reporting", LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)
        If Not rFind Is Nothing Then
            iCharNum = rFind.Column
            From_Col_Char = Split(Cells(1, iCharNum).Address, "$")(1)
            To_Col_Char = Split(Cells(1, iCharNum + 1).Address, "$")(1)
        End If
    End With
     
    OldFormula(0) = Range(From_Col_Char & "7").Formula
    OldFormula(1) = Range(From_Col_Char & "8").Formula
    OldFormula(2) = Range(From_Col_Char & "12").Formula
    OldFormula(3) = Range(From_Col_Char & "13").Formula
    OldFormula(4) = Range(From_Col_Char & "15").Formula
    OldFormula(5) = Range(From_Col_Char & "16").Formula
    OldFormula(6) = Range(From_Col_Char & "17").Formula
    OldFormula(7) = Range(From_Col_Char & "18").Formula
    OldFormula(8) = Range(From_Col_Char & "27").Formula
    OldFormula(9) = Range(From_Col_Char & "28").Formula
 
    Range(To_Col_Char & "7").Value = OldFormula(0)
    Range(To_Col_Char & "8").Value = OldFormula(1)
    Range(To_Col_Char & "12").Value = OldFormula(2)
    Range(To_Col_Char & "13").Value = OldFormula(3)
    Range(To_Col_Char & "15").Value = OldFormula(4)
    Range(To_Col_Char & "16").Value = OldFormula(5)
    Range(To_Col_Char & "17").Value = OldFormula(6)
    Range(To_Col_Char & "18").Value = OldFormula(7)
    Range(To_Col_Char & "27").Value = OldFormula(8)
    Range(To_Col_Char & "28").Value = OldFormula(9)
 
    Range(From_Col_Char & "7").Copy
    Range(From_Col_Char & "7").PasteSpecial Paste:=xlPasteValues
 
    Range(From_Col_Char & "8").Copy
    Range(From_Col_Char & "8").PasteSpecial Paste:=xlPasteValues
 
    Range(From_Col_Char & "12").Copy
    Range(From_Col_Char & "12").PasteSpecial Paste:=xlPasteValues
 
    Range(From_Col_Char & "13").Copy
    Range(From_Col_Char & "13").PasteSpecial Paste:=xlPasteValues
 
    Range(From_Col_Char & "15").Copy
    Range(From_Col_Char & "15").PasteSpecial Paste:=xlPasteValues
 
    Range(From_Col_Char & "16").Copy
    Range(From_Col_Char & "16").PasteSpecial Paste:=xlPasteValues
 
    Range(From_Col_Char & "17").Copy
    Range(From_Col_Char & "17").PasteSpecial Paste:=xlPasteValues
 
    Range(From_Col_Char & "18").Copy
    Range(From_Col_Char & "18").PasteSpecial Paste:=xlPasteValues
 
    Range(From_Col_Char & "27").Copy
    Range(From_Col_Char & "27").PasteSpecial Paste:=xlPasteValues
 
    Range(From_Col_Char & "28").Copy
    Range(From_Col_Char & "28").PasteSpecial Paste:=xlPasteValues
 
next 'End the Sheet Loops

'CLEARS the Copy Selection
Application.CutCopyMode = False
     
End Sub     'End of Gerwe1988
 
Upvote 0
airforceone said:
update current code to the code below (UNTESTED) new lines are commented
VBA Code: 
Sub Gerwe1988()
 
    Dim rFind As Range, iCharNum As Integer
    Dim From_Col_Char As String, To_Col_Char As String
    Dim OldFormula(10) As String
    Dim LoopSHT as worksheets 'Declare variable as sheet for the loop

For Each loopSHT in Worksheets 'if all sheet have same format MUST NOT BE A PROBLEM
 
    With Range("A3:J3")
        Set rFind = .Find(What:="Reporting", LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)
        If Not rFind Is Nothing Then
            iCharNum = rFind.Column
            From_Col_Char = Split(Cells(1, iCharNum).Address, "$")(1)
            To_Col_Char = Split(Cells(1, iCharNum + 1).Address, "$")(1)
        End If
    End With
   
    OldFormula(0) = Range(From_Col_Char & "7").Formula
    OldFormula(1) = Range(From_Col_Char & "8").Formula
    OldFormula(2) = Range(From_Col_Char & "12").Formula
    OldFormula(3) = Range(From_Col_Char & "13").Formula
    OldFormula(4) = Range(From_Col_Char & "15").Formula
    OldFormula(5) = Range(From_Col_Char & "16").Formula
    OldFormula(6) = Range(From_Col_Char & "17").Formula
    OldFormula(7) = Range(From_Col_Char & "18").Formula
    OldFormula(8) = Range(From_Col_Char & "27").Formula
    OldFormula(9) = Range(From_Col_Char & "28").Formula
 
    Range(To_Col_Char & "7").Value = OldFormula(0)
    Range(To_Col_Char & "8").Value = OldFormula(1)
    Range(To_Col_Char & "12").Value = OldFormula(2)
    Range(To_Col_Char & "13").Value = OldFormula(3)
    Range(To_Col_Char & "15").Value = OldFormula(4)
    Range(To_Col_Char & "16").Value = OldFormula(5)
    Range(To_Col_Char & "17").Value = OldFormula(6)
    Range(To_Col_Char & "18").Value = OldFormula(7)
    Range(To_Col_Char & "27").Value = OldFormula(8)
    Range(To_Col_Char & "28").Value = OldFormula(9)
 
    Range(From_Col_Char & "7").Copy
    Range(From_Col_Char & "7").PasteSpecial Paste:=xlPasteValues
 
    Range(From_Col_Char & "8").Copy
    Range(From_Col_Char & "8").PasteSpecial Paste:=xlPasteValues
 
    Range(From_Col_Char & "12").Copy
    Range(From_Col_Char & "12").PasteSpecial Paste:=xlPasteValues
 
    Range(From_Col_Char & "13").Copy
    Range(From_Col_Char & "13").PasteSpecial Paste:=xlPasteValues
 
    Range(From_Col_Char & "15").Copy
    Range(From_Col_Char & "15").PasteSpecial Paste:=xlPasteValues
 
    Range(From_Col_Char & "16").Copy
    Range(From_Col_Char & "16").PasteSpecial Paste:=xlPasteValues
 
    Range(From_Col_Char & "17").Copy
    Range(From_Col_Char & "17").PasteSpecial Paste:=xlPasteValues
 
    Range(From_Col_Char & "18").Copy
    Range(From_Col_Char & "18").PasteSpecial Paste:=xlPasteValues
 
    Range(From_Col_Char & "27").Copy
    Range(From_Col_Char & "27").PasteSpecial Paste:=xlPasteValues
 
    Range(From_Col_Char & "28").Copy
    Range(From_Col_Char & "28").PasteSpecial Paste:=xlPasteValues
 
next 'End the Sheet Loops

'CLEARS the Copy Selection
Application.CutCopyMode = False
   
End Sub     'End of Gerwe1988[/CODE
[/QUOTE]
Click to expand...

airforceone said:
update current code to the code below (UNTESTED) new lines are commented
VBA Code: 
Sub Gerwe1988()
 
    Dim rFind As Range, iCharNum As Integer
    Dim From_Col_Char As String, To_Col_Char As String
    Dim OldFormula(10) As String
    Dim LoopSHT as worksheets 'Declare variable as sheet for the loop

For Each loopSHT in Worksheets 'if all sheet have same format MUST NOT BE A PROBLEM
 
    With Range("A3:J3")
        Set rFind = .Find(What:="Reporting", LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)
        If Not rFind Is Nothing Then
            iCharNum = rFind.Column
            From_Col_Char = Split(Cells(1, iCharNum).Address, "$")(1)
            To_Col_Char = Split(Cells(1, iCharNum + 1).Address, "$")(1)
        End If
    End With
    
    OldFormula(0) = Range(From_Col_Char & "7").Formula
    OldFormula(1) = Range(From_Col_Char & "8").Formula
    OldFormula(2) = Range(From_Col_Char & "12").Formula
    OldFormula(3) = Range(From_Col_Char & "13").Formula
    OldFormula(4) = Range(From_Col_Char & "15").Formula
    OldFormula(5) = Range(From_Col_Char & "16").Formula
    OldFormula(6) = Range(From_Col_Char & "17").Formula
    OldFormula(7) = Range(From_Col_Char & "18").Formula
    OldFormula(8) = Range(From_Col_Char & "27").Formula
    OldFormula(9) = Range(From_Col_Char & "28").Formula
 
    Range(To_Col_Char & "7").Value = OldFormula(0)
    Range(To_Col_Char & "8").Value = OldFormula(1)
    Range(To_Col_Char & "12").Value = OldFormula(2)
    Range(To_Col_Char & "13").Value = OldFormula(3)
    Range(To_Col_Char & "15").Value = OldFormula(4)
    Range(To_Col_Char & "16").Value = OldFormula(5)
    Range(To_Col_Char & "17").Value = OldFormula(6)
    Range(To_Col_Char & "18").Value = OldFormula(7)
    Range(To_Col_Char & "27").Value = OldFormula(8)
    Range(To_Col_Char & "28").Value = OldFormula(9)
 
    Range(From_Col_Char & "7").Copy
    Range(From_Col_Char & "7").PasteSpecial Paste:=xlPasteValues
 
    Range(From_Col_Char & "8").Copy
    Range(From_Col_Char & "8").PasteSpecial Paste:=xlPasteValues
 
    Range(From_Col_Char & "12").Copy
    Range(From_Col_Char & "12").PasteSpecial Paste:=xlPasteValues
 
    Range(From_Col_Char & "13").Copy
    Range(From_Col_Char & "13").PasteSpecial Paste:=xlPasteValues
 
    Range(From_Col_Char & "15").Copy
    Range(From_Col_Char & "15").PasteSpecial Paste:=xlPasteValues
 
    Range(From_Col_Char & "16").Copy
    Range(From_Col_Char & "16").PasteSpecial Paste:=xlPasteValues
 
    Range(From_Col_Char & "17").Copy
    Range(From_Col_Char & "17").PasteSpecial Paste:=xlPasteValues
 
    Range(From_Col_Char & "18").Copy
    Range(From_Col_Char & "18").PasteSpecial Paste:=xlPasteValues
 
    Range(From_Col_Char & "27").Copy
    Range(From_Col_Char & "27").PasteSpecial Paste:=xlPasteValues
 
    Range(From_Col_Char & "28").Copy
    Range(From_Col_Char & "28").PasteSpecial Paste:=xlPasteValues
 
next 'End the Sheet Loops

'CLEARS the Copy Selection
Application.CutCopyMode = False
    
End Sub     'End of Gerwe1988
Click to expand...
Sorry, each tab/worksheet has formulas in different columns and rows. Could you provide an example on how the macro above would be modified to accompany different range/columns? The "reporting" identifier is static and on row 3 on all tabs.
 
Upvote 0
You must log in or register to reply here.

Similar threads

H
  • Question
Adjust an existing formula based on most recently added column and prior month end date.
Replies
1
Views
96
healeydm
H
P
  • Question
Need VBA to automatically copy paste rows based on dates of current month.
2
Replies
11
Views
208
pk970
P
T
  • Question
VBA Code - Copy last column & Insert Copied
Replies
0
Views
56
TheSetBack
T
T
  • Question
VBA- Copy & Paste Range into Outlook as a Picture
Replies
1
Views
110
Domenic
D
K
  • Question
Excel script using if statement to copy/paste special values for specific rows
Replies
2
Views
177
kcampbell675
K

Forum statistics

Threads
1,214,833
Messages
6,121,864
Members
449,052
Latest member
Fuddy_Duddy

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top