Range of data to be copied in another sheet

Tushark

New Member
Joined
Mar 2, 2021
Messages
8
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
  5. 2011
  6. 2010
  7. 2007
Platform
  1. Windows
Hi,

I have this code which helps me in saving data for two cells in different sheet.

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = Range("M2").Address Then
Dim intLastRow As Long
intLastRow = Sheet2.Cells(Sheet2.Rows.Count, "A").End(xlUp).Row
Sheet2.Cells(intLastRow + 1, "B") = Target.Value

ElseIf Target.Address = Range("M3").Address Then
Dim intLastRow2 As Long
intLastRow2 = Sheet2.Cells(Sheet2.Rows.Count, "B").End(xlUp).Row
Sheet2.Cells(intLastRow2 + 1, "B") = Target.Value
End If
End Sub

With this code working i want to save the data for the range P2:P7 and AC2:AC7 in different sheet. They should be saved in different columns (Like P2 data goes to C, P3 data goes to D so on) so that i can do comparison later.

Thanks in Advance.
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,544
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
Which sheet should those ranges be saved to, is it the same sheet as M2 & M3?
 

Tushark

New Member
Joined
Mar 2, 2021
Messages
8
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
  5. 2011
  6. 2010
  7. 2007
Platform
  1. Windows
Hi & welcome to MrExcel.
Which sheet should those ranges be saved to, is it the same sheet as M2 & M3?
Yes fluff in sheet 2 starting from column C.
 

Tushark

New Member
Joined
Mar 2, 2021
Messages
8
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
  5. 2011
  6. 2010
  7. 2007
Platform
  1. Windows
Yes fluff in sheet 2 starting from column C.
Also can we do one more thing as the M2, M3 and these ranges will be saving a lot of data so it might make the excel sheet heavy is their any possibility we should just record the values till 5-10 records and then it keeps rewriting on those records.
 

Tushark

New Member
Joined
Mar 2, 2021
Messages
8
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
  5. 2011
  6. 2010
  7. 2007
Platform
  1. Windows

ADVERTISEMENT

One more problem that came up is the data in M2 and M3 stops pasting in another sheet not sure why might be when i tested it, M2 and M3 was taking data from the same and now they are pulling up the data from another sheet.

Let me attach the sheet with the code.

I am using this code

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = Range("M2").Address Then
Dim intLastRow As Long
intLastRow = Sheet2.Cells(Sheet2.Rows.Count, "A").End(xlUp).Row
Sheet2.Cells(intLastRow + 1, "A") = Target.Value

ElseIf Target.Address = Range("M3").Address Then
Dim intLastRow2 As Long
intLastRow2 = Sheet2.Cells(Sheet2.Rows.Count, "B").End(xlUp).Row
Sheet2.Cells(intLastRow2 + 1, "B") = Target.Value
End If
End Sub


Excel.png
 

Tushark

New Member
Joined
Mar 2, 2021
Messages
8
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
  5. 2011
  6. 2010
  7. 2007
Platform
  1. Windows
I have notice one thing it records the first value in sheet 2 when connecting but after that it does not.
 

Tushark

New Member
Joined
Mar 2, 2021
Messages
8
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
  5. 2011
  6. 2010
  7. 2007
Platform
  1. Windows

ADVERTISEMENT

Also when ever i press F2 and enter on column C2 it shows the value in sheet 2 not sure how to make it working continuously without doing it.
 

Tushark

New Member
Joined
Mar 2, 2021
Messages
8
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
  5. 2011
  6. 2010
  7. 2007
Platform
  1. Windows
Just a small update the code i have gave in the first post in place of M2 it's C2 and M3 will be C3 as per the excel image that i have sent.
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,696
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Range of data to be copied in another sheet
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.
 

Tushark

New Member
Joined
Mar 2, 2021
Messages
8
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
  5. 2011
  6. 2010
  7. 2007
Platform
  1. Windows
Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Range of data to be copied in another sheet
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.
Apologies for that i have posted this in two places

 

Watch MrExcel Video

Forum statistics

Threads
1,129,515
Messages
5,636,788
Members
416,941
Latest member
shazzaxyz

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
Top