Paste Location of Cell with VBA?

theteerex

Board Regular
Joined
Mar 2, 2018
Messages
102
Here is how my code currently works:

On sheet 1 at location B5, the value of the cell is Dog. On sheet 2, C15, the paste location, the value is also Dog.

What I would like is for C15 to be =$B$5. This way, I can change B5 on just sheet 1 and C15 on sheet 2 changes as well.

I thought I could use a paste special but can't find any that would work since it's not really a paste function.

How would I go about modifying my code, highlighted in red to add this functionality?

Current code:
Code:
Sub FindProjectName() 
Dim LastRow As Long
Dim Newproject As Long
Dim MasterTemplate As Range
Dim Masterrow As Long


'MasterTemplate is the database entry template.
Masterrow = Worksheets("Database").Range("MasterTemplate").Rows.Count 
LastRow = Sheets("Projects").Cells(Rows.Count, "B").End(xlUp).Row 
Newproject = Sheets("Database").Cells(Rows.Count, "C").End(xlUp).Row 


[B][COLOR=#ff0000]Sheets("Projects").Cells(LastRow, "B").Copy Sheets("Database").Cells(Newproject - Masterrow + 1, "C")[/COLOR][/B] 


With Sheets("Database")
    .Range("DBASE").Rows(1).Copy 
    .Range("DBASE").Rows(Newproject - Masterrow).PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
End With
End Sub
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
That doesn't work because "MasterTemplate" is a named range that is copied and pasted.
Once that happens, this code then copies the name of the project and pastes it in the first cell of the template, at its new location.
Which is why I need to code it in somehow.
 
Upvote 0
.
Then .... in your existing macro, you can use this line of code :

Code:
Sheets("Sheet2").Range("C15").Value = "[COLOR=#333333]=Sheet1!B5"[/COLOR]
 
Last edited:
Upvote 0
.
Then .... in your existing macro, you can use this line of code :

Code:
Sheets("Sheet2").Range("C15").Value = "[COLOR=#333333]=Sheet1!B5"[/COLOR]
I already tried that and it didn't work.
I got a run-time error 1004
Application defined or object defined error.

Maybe it didn't store the value or something?
 
Upvote 0
Strange ... it worked here without error.

Is your macro in a Routine Module .... or the Sheet Level Module ?
 
Upvote 0
Could I maybe add this to my code?
Code:
Dim CellOne As Long
Dim CellTwo As Long


CellOne = Sheets("Projects").Cells(LastRow, "B")
CellTwo = Sheets("Database").Cells(Newproject - Masterrow + 1, "C")
CellTwo = CellOne.Address
 
Upvote 0
Strange ... it worked here without error.

Is your macro in a Routine Module .... or the Sheet Level Module ?
It is in a routine macro.

Sheets("Database").Range(Newproject - Masterrow + 1, "C").Value = "=Projects!(LastRow, "B")" is what I tried based on your response.
 
Last edited:
Upvote 0
Could I maybe add this to my code?

The answer is : Try it and see what happens. You won't break anything ...

You are receiving "run-time error 1004" ... I believe because it wants you to define "Newproject - Masterrow" and/or maybe something else in that line of code.

When the error occurs, it sometimes helps to go to the VB Editor window and place the cursor over each term in the yellow highlighted line of code. Usually a small
explanation window will appear (kinda like a comment) giving you some insight of what is happening.
 
Upvote 0
The answer is : Try it and see what happens. You won't break anything ...

You are receiving "run-time error 1004" ... I believe because it wants you to define "Newproject - Masterrow" and/or maybe something else in that line of code.

When the error occurs, it sometimes helps to go to the VB Editor window and place the cursor over each term in the yellow highlighted line of code. Usually a small
explanation window will appear (kinda like a comment) giving you some insight of what is happening.
I tried it and none of it worked.
I am trying to define CellOne and CellTwo properly and nothing has worked so far.
Code:
Dim CellOne As Range
Set CellOne = Worksheets("Projects").Range("Records").Cells(LastRow, "B").Address
did not work.

When I hover over Cellone, excel tells me it is nothing.
 
Upvote 0

Forum statistics

Threads
1,213,524
Messages
6,114,117
Members
448,549
Latest member
brianhfield

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