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

[glen4u2c]

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

 

[glen4u2c]

In addition, when after i tried to delete the contents that i copied in the DEWAX123Summ and run the vba code that has no error, it does not copy the contents again the site legend

 

[MARK858]

I'll try and explain what you are saying in your code first..

Sheets("DEWAX123Summ").Range("B16:B31" & Sheets("DEWAX123Summ").Range("B" & Rows.Count).End(xlUp).Row).ClearContents

.Range("B" & Rows.Count).End(xlUp).Row)

gets the row number for the last cell in Column B that isn't blank and returns the row number. So lets say that number is 100.

What you are saying in the first line is Sheets("DEWAX123Summ") and range B6 to B31 and 100. Obviously having the 100 on the end makes no sense.

All you need for a fixed range is

Sheets("DEWAX123Summ").Range("B16:B31").ClearContents

and the same for the second part so your code as far as I can see would just be.

Sub testit()
Sheets("DEWAX123Summ").Range("B16:B31").ClearContents
Sheets("SiteLegend").Range("B6:B21").Copy Sheets("DEWAX123Summ").Range("B16")
End Sub

 

[glen4u2c]

Thanks for the last code.. it work.. only problem that i noticed that. I tried to simulate it by adding some sites and modify some existing site name and then click the update vba button that i created but unfortunately it didnt update the Dewax123Summ

How can we fix this?

 

[glen4u2c]

In additional assistance is that. What I plan on the Dewax123Summ is that I will create a summary table per monthly. So is it possible that when I click the update vba button it will copy the contents on the SiteLegend worksheet, let say copies B16:B31 to Dewax123Summ on certain cell range like -- B6:B21 /
B51:B71 / B96:B111 / B:136:B151 and so on

Would this be possible if I will just gonna add ranges if every I will add additional month

Thanks
Glen

 

[MARK858]

No because your destination ranges are uneven.

B21 to B71 is a difference of 50
B71 to B111 is a difference of 40
B111 to B151 is a difference of 40

By the way your question is way off now from where you started.

 

[glen4u2c]

Hi Mark858,

Sorry about that.... do you have a suggestion as to why the last code you sent and I tried it and really works, however when i tried to modify the content of the SiteLegend by adding more sites or editing the existing site name. When I click the command button to update. It didn't reflect the modification that I made. Would there be additional codes that needs to add in order to make that happen

Thank you for your patience

Cheers
Glen

 

[MARK858]

when i tried to modify the content of the SiteLegend by adding more sites or editing the existing site name. When I click the command button to update. It didn't reflect the modification that I made.

Not really the code is quite simple, if the data is in Range("B6:B21") of Sheets("SiteLegend") it will copy it, if it isn't then it won't. It doesn't care what the data is.

Going back to post #14. If the spacing is even then you can do something like

Sub testit()
    If Sheets("DEWAX123Summ").Range("B6") = "" Then
        Sheets("SiteLegend").Range("B16:B31").Copy Sheets("DEWAX123Summ").Range("B6")
    Else
        Sheets("DEWAX123Summ").Range("B" & Rows.Count).End(xlUp).Offset([COLOR="#FF0000"]30[/COLOR]).Resize(15).Value = Sheets("SiteLegend").Range("B16:B31").Value
    End If
End Sub

You do need to have data in cell B31 of Sheets("SiteLegend"), the spacing is the red 30 which is 30 from the last cell with data in Sheets("DEWAX123Summ").

It won't work if there is other data in between.

I must say though it will make future manipulation of your data a lot harder if you have different Months in the same column, personally I would have a good think about the layout.