need to copy text(formula) to cells

BobtBuilder · Sep 1, 2023

Hi there,
I can't seem to figure these two things out. I have a dashboard that serves as temporary worksheet. In this area I pull data from other worksheets manipulate them and then copy them back to their original worksheet.
After that I would need to copy the original formulas that were in the cells so I can pull data from other sheets.
I found a long way around the copy but what I thought would be the easy part was the reincorporating the formulas.
I set up a variable "Sh" that gives me the worksheet name.
1) I would like to use that variable to shorten the script and
2) be able to copy the formulas back to the cells.
Here is the code I currently have

Private Sub UpdateButton_Click()
'Create and set variables for the Call Tracking & Call Log worksheets

Dim Sh As Range
Set Sh = Range("A2") ' A2 is the cell where the worksheet name is located it is a drop down menu

If Sh = "IPC_CAD" Then
Range("D12").Copy Worksheets("IPC_CAD").Range("N7")
Range("D13").Copy Worksheets("IPC_CAD").Range("N8")
Range("D14").Copy Worksheets("IPC_CAD").Range("N9")
Range("D15").Copy Worksheets("IPC_CAD").Range("N10")

' This is where I tried to copy the formulas back but kept getting errors
'Range("D12").Formula = "=INDIRECT("" & A2 & " ! " & "n8")

' Copy "=INDIRECT(""&A2&"!"&"n9")" ("D13")
' Copy "=INDIRECT(""&A2&"!"&"n10")" ("D14")
' Copy "=INDIRECT(""&A2&"!"&"n11")" ("D13")
End If

If Sh = "IPC_USD" Then
Range("D12").Copy Worksheets("IPC_USD").Range("N7")
Range("D13").Copy Worksheets("IPC_USD").Range("N8")
Range("D14").Copy Worksheets("IPC_USD").Range("N9")
Range("D15").Copy Worksheets("IPC_USd").Range("N10")
End If

If Sh = "Bcon_CAD" Then
Range("D12").Copy Worksheets("Bcon_CAD").Range("N7")
Range("D13").Copy Worksheets("Bcon_CAD").Range("N8")
Range("D14").Copy Worksheets("Bcon_CAD").Range("N9")
Range("D15").Copy Worksheets("Bcon_CAD").Range("N10")
End If

If Sh = "Bcon_USD" Then
Range("D12").Copy Worksheets("Bcon_USD").Range("N7")
Range("D13").Copy Worksheets("Bcon_USD").Range("N8")
Range("D14").Copy Worksheets("Bcon_USD").Range("N9")
Range("D15").Copy Worksheets("Bcon_USD").Range("N10")
End If

If Sh = "Best_Flex_CAD" Then
Range("D12").Copy Worksheets("Best_Flex_CAD").Range("N7")
Range("D13").Copy Worksheets("Best_flex_CAD").Range("N8")
Range("D14").Copy Worksheets("Best_flex_CAD").Range("N9")
Range("D15").Copy Worksheets("Best_flex_CAD").Range("N10")
End If

If Sh = "Best_Flex_USD" Then
Range("D12").Copy Worksheets("Best_Flex_USD").Range("N7")
Range("D13").Copy Worksheets("Best_flex_USD").Range("N8")
Range("D14").Copy Worksheets("Best_flex_USD").Range("N9")
Range("D15").Copy Worksheets("Best_flex_USD").Range("N10")
End If

If Sh = "Bc_chk" Then
Range("D12").Copy Worksheets("Bc_chk").Range("N7")
Range("D13").Copy Worksheets("Bc_chk").Range("N8")
Range("D14").Copy Worksheets("Bc_chk").Range("N9")
Range("D15").Copy Worksheets("Bc_chk").Range("N10")
End If

If Sh = "Bc_USD" Then
Range("D12").Copy Worksheets("Bc_usd").Range("N7")
Range("D13").Copy Worksheets("Bc_usd").Range("N8")
Range("D14").Copy Worksheets("Bc_usd").Range("N9")
Range("D15").Copy Worksheets("Bc_usd").Range("N10")
End If

If Sh = "Bc_esave" Then
Range("D12").Copy Worksheets("Bc_esave").Range("N7")
Range("D13").Copy Worksheets("Bc_esave").Range("N8")
Range("D14").Copy Worksheets("Bc_esave").Range("N9")
Range("D15").Copy Worksheets("Bc_esave").Range("N10")
End If

End Sub

Thank you

kevin9999 · Sep 2, 2023

Could you provide a copy of your sheet with the data you're copying using the XL2BB add in as I'm struggling to understand exactly what formula you're trying to "copy back". In the meantime, here's a shortened script using the sh variable.

VBA Code:

Option Explicit
Sub BobtBuilder()
    Dim ws As Worksheet, sh As String, i As Long, exists As Boolean
    Set ws = Worksheets("Sheet1")       '<-- *** Change name to sheet with original data ***
    sh = ws.Range("A2").Value2
    
    For i = 1 To Worksheets.Count
        If Worksheets(i).Name = sh Then exists = True
    Next i
    If Not exists Then MsgBox sh & " does not exist in this workbook": Exit Sub
    
    ws.Range("D12:D15").Copy Worksheets(sh).Range("N7")
End Sub

BobtBuilder · Sep 2, 2023

i

kevin9999 said:
Could you provide a copy of your sheet with the data you're copying using the XL2BB add in as I'm struggling to understand exactly what formula you're trying to "copy back". In the meantime, here's a shortened script using the sh variable.

VBA Code:

Option Explicit Sub BobtBuilder() Dim ws As Worksheet, sh As String, i As Long, exists As Boolean Set ws = Worksheets("Sheet1") '<-- *** Change name to sheet with original data *** sh = ws.Range("A2").Value2 For i = 1 To Worksheets.Count If Worksheets(i).Name = sh Then exists = True Next i If Not exists Then MsgBox sh & " does not exist in this workbook": Exit Sub ws.Range("D12:D15").Copy Worksheets(sh).Range("N7") End Sub

in essence i am using "A2" as a drop down menu that shows the worksheets, when chosen i have 5 cells that pick up information from that sheet. I then change some ofthe cells with information. from there , when i hit a button, i need that new information returned to its prospective sheet and copy the indirect formula back to the cells i had manually overridden so i can once again choose new info from another sheet.

kevin9999 · Sep 2, 2023

DELETED

kevin9999 · Sep 2, 2023

Try the following on a copy of your workbook:

VBA Code:

Option Explicit
Sub BobtBuilder2()
    Dim ws As Worksheet, sh As String, i As Long, exists As Boolean
    Set ws = Worksheets("Sheet1")       '<-- *** Change name to sheet with original data ***
    sh = ws.Range("A2").Value2
    For i = 1 To Worksheets.Count
        If Worksheets(i).Name = sh Then exists = True
    Next i
    If Not exists Then MsgBox sh & " does not exist in this workbook": Exit Sub
    
    With ws
        .Range("D12:D15").Copy
        Worksheets(sh).Range("N7").PasteSpecial xlPasteValues
        Application.CutCopyMode = False
        .Range("D12").Formula2R1C1 = "=INDIRECT(TEXT(R2C1,""'@'!"")&""N7"")"
        .Range("D13").Formula2R1C1 = "=INDIRECT(TEXT(R2C1,""'@'!"")&""N8"")"
        .Range("D14").Formula2R1C1 = "=INDIRECT(TEXT(R2C1,""'@'!"")&""N9"")"
        .Range("D15").Formula2R1C1 = "=INDIRECT(TEXT(R2C1,""'@'!"")&""N10"")"
    End With
End Sub

BobtBuilder · Sep 5, 2023

kevin9999 said:

Try the following on a copy of your workbook:

VBA Code:

Option Explicit
Sub BobtBuilder2()
    Dim ws As Worksheet, sh As String, i As Long, exists As Boolean
    Set ws = Worksheets("Sheet1")       '<-- *** Change name to sheet with original data ***
    sh = ws.Range("A2").Value2
    For i = 1 To Worksheets.Count
        If Worksheets(i).Name = sh Then exists = True
    Next i
    If Not exists Then MsgBox sh & " does not exist in this workbook": Exit Sub
   
    With ws
        .Range("D12:D15").Copy
        Worksheets(sh).Range("N7").PasteSpecial xlPasteValues
        Application.CutCopyMode = False
        .Range("D12").Formula2R1C1 = "=INDIRECT(TEXT(R2C1,""'@'!"")&""N7"")"
        .Range("D13").Formula2R1C1 = "=INDIRECT(TEXT(R2C1,""'@'!"")&""N8"")"
        .Range("D14").Formula2R1C1 = "=INDIRECT(TEXT(R2C1,""'@'!"")&""N9"")"
        .Range("D15").Formula2R1C1 = "=INDIRECT(TEXT(R2C1,""'@'!"")&""N10"")"
    End With
End Sub

I pasted that code into the worksheet, and the =INDIRECT(TEXT(R2C1,""'@'!"")&""N7"") was pasted, but I am not sure what the text was for, maybe I was unclear, i simply wanted to copy the indirect statement ( =INDIRECT(""&A2&"!"&"n7") ) into the cells (A2 being R2C1), but kept getting a error on the "!"

kevin9999 · Sep 5, 2023

BobtBuilder said:
but kept getting a error on the "!"

The code works fine for me. This is what I get when I run the code (ignore the values - look at the formulas)

BobtBuilder.xlsm

A

C

D

2

Bcon_CAD

3

4

5

6

7

8

9

10

11

12

13

14

15

Sheet1

Cell Formulas
Range	Formula
D12	D12	=INDIRECT(TEXT($A$2,"'@'!")&"N7")
D13	D13	=INDIRECT(TEXT($A$2,"'@'!")&"N8")
D14	D14	=INDIRECT(TEXT($A$2,"'@'!")&"N9")
D15	D15	=INDIRECT(TEXT($A$2,"'@'!")&"N10")

Cells with Data Validation
Cell	Allow	Criteria
A2	List	=$B$2:$B$10

This is what is on the Bcon_CAD sheet:

BobtBuilder.xlsm

N

7

1

8

2

9

3

10

4

Bcon_CAD

If you provide a copy of your sheet using the XL2BB add in, or share your file via Google Drive, Dropbox or similar file sharing platform, then I'll be happy to look at it again - otherwise I'll step back and hope other volunteers will step forward and assist you with this issue.

BobtBuilder · Sep 5, 2023

kevin9999 said:
The code works fine for me. This is what I get when I run the code (ignore the values - look at the formulas)

BobtBuilder.xlsm
A C D
2 Bcon_CAD
3
4
5
6
7
8
9
10
11
12 1
13 2
14 3
15 4
Sheet1
Cell Formulas
Range Formula
D12 D12 =INDIRECT(TEXT($A$2,"'@'!")&"N7")
D13 D13 =INDIRECT(TEXT($A$2,"'@'!")&"N8")
D14 D14 =INDIRECT(TEXT($A$2,"'@'!")&"N9")
D15 D15 =INDIRECT(TEXT($A$2,"'@'!")&"N10")
Cells with Data Validation
Cell Allow Criteria
A2 List =$B$2:$B$10

This is what is on the Bcon_CAD sheet:

BobtBuilder.xlsm
N
7 1
8 2
9 3
10 4
Bcon_CAD

If you provide a copy of your sheet using the XL2BB add in, or share your file via Google Drive, Dropbox or similar file sharing platform, then I'll be happy to look at it again - otherwise I'll step back and hope other volunteers will step forward and assist you with this issue.

Thank you, it was an excel issue, but it works much appreciated

kevin9999 · Sep 5, 2023

BobtBuilder said:
Thank you, it was an excel issue, but it works much appreciated

Glad you got it to work, and thanks for the feedback

need to copy text(formula) to cells

BobtBuilder

New Member

Excel Facts

kevin9999

Well-known Member

BobtBuilder

New Member

kevin9999

Well-known Member

kevin9999

Well-known Member

BobtBuilder

New Member

kevin9999

Well-known Member

BobtBuilder

New Member

kevin9999

Well-known Member

Similar threads

Forum statistics

Share this page

need to copy text(formula) to cells

New Member

Excel Facts

Well-known Member

New Member

Well-known Member

Well-known Member

New Member

Well-known Member

New Member

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock