VB Code to move data

R

rockyw

Well-known Member
Joined
Dec 26, 2010
Messages
1,196
Office Version
  1. 2010
Platform
  1. Windows
I have a workbook that when opened will update data from a differant workbook with the automatic update. Each time this is opened the data in several cells will change from the last time it was opened. Whats the best way to look at just one of those cells in Sheet1, say A10 and copy the data to Sheet 2, column A and fill from the top down. Can this code be easy to edit, and can I use several codes back to back to move data from other cells as well and edit as needed. The forma t may change over time and I could then change the code as needed. The cells data is copyed from will be filled by a formula like ='C:\[Book 1.xlsm]Sheet1'!$B$2

Thanks if you can help.
 
Sort by date Sort by votes
Sorry, I wiped everything out and tried it again and it works fine. Thank you so much, this will be great. And I can add and edit it. I love to learn this stuff. Have a good weekend.
 
Upvote 0

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
How can I edit this so it starts copying into the columns in ROW 3
Code: 
Private Sub Worksheet_Calculate()
Me.Range("A10").Copy
Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues
End Sub

Thanks
 
Upvote 0
Try

Code: 
Private Sub Worksheet_Calculate()
Dim NR As Long
NR = WorksheetFunction.Max(3, Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row + 1)
Me.Range("A10").Copy
Sheets("Sheet2").Range("A" & NR).PasteSpecial Paste:=xlPasteValues
End Sub
 
Upvote 0
VoG I edited this to copy Sheet1 A3 to Shhet9 Column B. I get an error, is this edited wrong? Thanks
Code: 
Private Sub Worksheet_Calculate()
Dim NR As Long
NR = WorksheetFunction.Max(3, Sheets("Sheet9").Range("B" & Rows.Count).End(xlUp).Row + 1)
Me.Range("A3").Copy
Sheets("Sheet9").Range("B" & NR).PasteSpecial Paste:=xlPasteValues
End Sub
 
Upvote 0
That works fine for me. What error do you get and which line of code is highlighted when you click Debug?
 
Upvote 0
That means that you do not have a sheet called Sheet9. Maybe its codename is Sheet9 but you renamed it to something else? You need to use the name displayed on the worksheet tab.
 
Upvote 0
I understand now. I thought it had to be the sheet number as shown in the view code area. When I added the next line I got an error so I took out (Dim NR As Long) from between the next code. Is that the right thing to do? I have
Code: 
Private Sub Worksheet_Calculate()
Dim NR As Long
NR = WorksheetFunction.Max(3, Sheets("FDR Data").Range("B" & Rows.Count).End(xlUp).Row + 1)
Me.Range("B2").Copy
Sheets("FDR Data").Range("B" & NR).PasteSpecial Paste:=xlPasteValues
NR = WorksheetFunction.Max(3, Sheets("FDR Data").Range("C" & Rows.Count).End(xlUp).Row + 1)
Me.Range("B3").Copy
Sheets("FDR Data").Range("C" & NR).PasteSpecial Paste:=xlPasteValues
End Sub
To look at the next cellB3. This look OK. Thanks I hope I have this now.
 
Last edited:
Upvote 0
That should be fine, Reusing variables (in this case NR) is generally to be deprecated but in this case where the use is so obvious it does not matter.
 
Upvote 0
OK Thanks. I tried it with that part in and I got an error. I took it out and it all worked. Thanks

PS I posted about changing a code I'm using, maybe you could take a look. Its under CAN THIS WORK WITH A,B ect. Thanks
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question Question
Two way updating of two different sheets in two different workbooks
Replies
9
Views
172
Trebor76
T
P
  • Question Question
TypeScript (or other solutions) for modifying given list of cells (and similar problems)
Replies
0
Views
256
Pingaware
P
M
  • Question Question
VBA Code required..
Replies
5
Views
248
bobsan42
bobsan42
D
  • Question Question
Modify a worksheet without registering it as a change
Replies
1
Views
109
Kaper
Kaper
J
  • Question Question
Pulling Data From ~300 Individual Sheets Onto a Master Sheet
Replies
1
Views
195
ranman256
ranman256

Forum statistics

Threads
1,224,599
Messages
6,179,827
Members
452,946
Latest member
JoseDavid

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