How to create vba code on copy and paste cell range from sheet1 to sheet 2

glen4u2c

New Member
Joined
Jul 27, 2013
Messages
11
Hi to All,

Am just newbie in this forum. I seek assistance on creating a vba code that will enable me to copy certain range of data from sheet 1 to sheet 2. I would humbly appreciate to give me a vba code. I can understand a little bit of codes and understand modifying it. hope you can help!!!

Source Worksheet - 'Legend'
Target Worksheet - ' Summary'

Source Worksheet Content:

A1 B1
Item code Site Codes
01 USA
02 Canada
03 Russia

My objective is that I will create a command button that will quickly copy the contents in B1 in source sheet and automatically paste in target worksheet in a certain cell range.

In event that additional site codes will be added/deleted, i would just click the command button and will update the target worksheet.

Hoping for your kind assistance regarding this matter

Thank you
Glen
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
Hi Glen, you are very vague about your destination (and whether it is a form command button or an ActiveX command button) but see if this gets you started...

Code:
Sheets("Legend").Range("B1").Copy Sheets("Summary").Range("A" & Rows.Count).End(xlUp).Offset(1)
Please note also that I haven't allowed for the space you put at the start of ' Summary' (add it if it should be there)
 
Upvote 0
Hi Mark858,

Thanks for the warm assistance. I tried now the vba code you sent and it work. The only problem I noticed is that.

1. when I tried to click the copy again, it creates another copy unto the next row.
2. When I tried to remove some date in the source and click the command button. it did not clear the existing list the summary but instead it override the existing data. Would there be a way that if ever I remove or add additional site codes. It will clear first the content of the target worksheet and paste the updated.

Hoping for our continued assistance.

Regards,
Glen
 
Upvote 0
Hi Mark858,

Below is the code that I used to run the vba

Private Sub UpdateList_Click()
Sheets("Legend").Range("B5:B14").Copy Sheets("Summary").Range("B" & Rows.Count).End(xlUp).Offset(1)
End Sub
 
Upvote 0
You are still not being clear about the destination.
At the moment...
We know you want the range B5:B14 being copied (we don't know if that is dynamic or not)
We don't know where the first destination cell is.
We know you want the previous results cleared (again we don't know if it is dynamic or not)

Can you do a step by step by my data is in this range and I want it to go to this range. When I get the next set of data I want it to do this...?

I am going out for a few hours and will have a look when I get back in to the see where the thread is
 
Upvote 0
Hi Mark858

Below is my objective of this vba code

On the Legend Worksheet (Source of Data)

  1. The site codes is dynamic but I think I might go up to 50 sites but won’t go over 100. So on the legend worksheet I made count number from 1 to 30 cuz the current total sites is only 18. But like I say it might increment but not frequently, that’s why I put additional 12 empty row slots.
  2. I only used to columns, A5:A30 for the Item Number Count and B5:B34 for the Site Codes. So basically only columns B5:B34 is only being modified for the add and remove sites.
  3. So If ever I add or remove a certain sites in that range, I just want to click the vba button I created and it will automatically copied the information in the B5:B34 on the Summary Worksheet.

On the Summary Worksheet (Destination Data)


  1. This is the worksheet that I created for the summary of all the sites and the other columns are just number figures that being computed
  2. In this worksheet, I also used the same column and row for the destination sites so as it will not be too complicated in the vba code
  3. In this worksheet, If ever a certain updates made on the legend worksheet and clicks on the update button, I want it to clear out first the information in the B5:B34 then it updates the new list

Really appreciate all the help on this

Regards
Glen
 
Upvote 0
In addition, can you tell me how to understand this vba code below

Sheets("Summary").Range("B" & Rows.Count).End(xlUp).Offset(1)

This part of the code you made, I try to add range in the "B" but end up having run time error, I tried changing the B to C and it works but it started to paste the information on C2? Dont know why

thanks
 
Upvote 0
In addition, can you tell me how to understand this vba code below

We will try and get a working code first then worry about explaining it. Try the code below on a copy of your data and pass back any comments.

Code:
Sub Test()
Sheets("Summary").Range("B5:B" & Sheets("Summary").Range("B" & Rows.Count).End(xlUp).Row).ClearContents
Sheets("Legend").Range("B5:B" & Sheets("Legend").Range("B" & Rows.Count).End(xlUp).Row).Copy Sheets("Summary").Range("B5")
End Sub
 
Upvote 0
Hi Mark858,

I tried the vba code you sent and its work. I tried removing some site code nad click the command button and it erase and update the list based on the Legend worksheet. I will try to arrange the format and give you feed back on the results

It is awesome!. Thanks for the help and guidance

Will Keep you posted

Cheers!
Glen
 
Upvote 0
Hi Mark858,

Having some problems after i tried to modify the worksheet name and cell range in the vba code

First I run this vba code below
---------------------------------------------------------------------------------------
Private Sub UpdateList_Click()
Sheets("DEWAX123Summ").Range("B16:B31" & Sheets("DEWAX123Summ").Range("B" & Rows.Count).End(xlUp).Row).ClearContents
Sheets("SiteLegend").Range("B6:B21" & Sheets("SiteLegend").Range("B" & Rows.Count).End(xlUp).Row).Copy Sheets("DEWAX123Summ").Range("B16")
End Sub
------------------------------------------------------------------------------------

The code run, but the result is it copied all the data in the SiteLegend worksheet cell 'B' into the DEWAX123Summ worksheet which overrides the rest of the cell underneath that.

Then i tried to modify the range and which I would liketo copy a selected cell B range in SiteLegend and paste it to specific range in the DEWAX123Summ.. See the code i modify

------------------------------------------------------------------------------------------------------

Private Sub UpdateList_Click()
Sheets("DEWAX123Summ").Range("B16:B31" & Sheets("DEWAX123Summ").Range("B" & Rows.Count).End(xlUp).Row).ClearContents
Sheets("SiteLegend").Range("B6:B21" & Sheets("SiteLegend").Range("B6:B21" & Rows.Count).End(xlUp).Row).Copy Sheets("DEWAX123Summ").Range("B16")
End Sub

------------------------------------------------------------------------------------------

The result was I am having Run time error 1004 -'Application-defined or object-define error

Cannot find which part of the code am giving that error

Please advice thanks

glen
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,972
Members
448,537
Latest member
Et_Cetera

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