Automating copying of columns within same sheet in excel using VBA.

pk970

New Member
Joined
Apr 2, 2024
Messages
7
Office Version
  1. 365
Platform
  1. Windows
Hi All,

I am very new in VBA and I have came across a situation in my project where I need to use VBA to copy columns within the sheet and then replace the same columns with other data. I will try to explain the situation as much as I can in description.
I have an excel workbook which consists of approximately 25 tabs of different business units and all tabs have approximately more than 40 columns.
Lets assume 1 sheet for this case and name it as "Sheet1". Sheet1 has data from columns A-G which is of current week, H-O which has data from last week and Q-V which has comparison between current week and last week.
The column header is same for A-G and H-O
Now every week when we publish the report, we need to follow few steps.

1) We need to copy data from A-G and paste as value in H-O. Pasting as value is very important.

2) Value A-G is based on formulas which is already there. There is another sheet lets say "Sheet2" in which we need to copy data from a different worksheet and as I copy data in "Sheet2" then based on formulas column A-G will get updated in "Sheet1".

3) Now the issue is that since we have copied data from column A-G and pasted as value in column H-O, then when step 2 is applied will the data in column A-G will become equal to data in column H-O??

Also when need a solution to copy data into "Sheet2" from a different workbook assuming all the data headers remain same.
Any help will be appreciated. Its a client project and data is really sensitive so I cannot even upload the screenshot of the file and I have never worked in VBA but this is immediate requirement.
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
Understanding the sensitivity of the data, please create "dummy" data workbook and upload via dropbox. You can randomize numbers in it. This will be helpful in generating a solution.
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,103
Members
452,302
Latest member
TaMere

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