VBA Copy data from multiple sheets and paste in active sheet

K

Kra

Board Regular
Joined
Jul 4, 2022
Messages
160
Office Version
  1. 365
Platform
  1. Windows
Hi all,

I have a huge dump of data in multiple sheets. All of them are placed after sheet called "PTO".

I want to create a macro button which will copy data from each sheet after "PTO", from columns A-N, starting from row 10 (length of data is variable, but it always end with an empty row).
Then it will paste them as values in active sheet in cell A3 (so it will cover columns A-N).

After it will be done with pasting data, it would be awesome if it could drag some data as well. In columns O-BB, rows 3-8 i have fixed values and formulas. Can it be dragged to the end of data in columns A-N?
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Sort by date Sort by votes
Here is a mini sheet where data needs to be pasted and dragged


Book2
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAUAVAWAXAYAZBABB
1
2
3formulaformulaformulaformulaformulaformulaformulaFIXFIXformulaformulaFIXformulaformulaformulaFIXformulaformulaformulaformula0formula0001190031129999formula
4formulaformulaformulaformulaformulaformulaformulaFIXFIXformulaformulaFIXformulaformulaformulaFIXformulaformulaformulaformula0formula0001190031129999formula
5formulaformulaformulaformulaformulaformulaformulaFIXFIXformulaformulaFIXformulaformulaformulaFIXformulaformulaformulaformula0formula0001190031129999formula
6formulaformulaformulaformulaformulaformulaformulaFIXFIXformulaformulaFIXformulaformulaformulaFIXformulaformulaformulaformula0formula0001190031129999formula
7formulaformulaformulaformulaformulaformulaformulaFIXFIXformulaformulaFIXformulaformulaformulaFIXformulaformulaformulaformula0formula0001190031129999formula
8formulaformulaformulaformulaformulaformulaformulaFIXFIXformulaformulaFIXformulaformulaformulaFIXformulaformulaformulaformula0formula0001190031129999formula
9
10
11
12
13
14
15
16
17
18
19
20
21
Main
Cell Formulas
RangeFormula
AX3:AX8AX3=A3
AY3:AY8AY3=O3
AZ3:AZ8AZ3=M3
S3:S8S3=CONCAT(A3,B3,Q3)
BB3:BB8BB3=T3
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Question
VBA code to cut and paste multiple filtered rows based on multiple criteria
Replies
2
Views
463
Alex Blakenburg
A
G
  • Question
Copy specific columns from multiple sheets to another sheet
Replies
8
Views
382
Joe4
Joe4
tabbytomo
  • Solved
Copy entire row if cell has specific value, looking at multiple columns and worksheets.
Replies
2
Views
281
NateSC
NateSC
R
  • Question
Copy/Paste Shape to active sheet
Replies
3
Views
159
Micron
M
P
  • Question
copy data from one column and paste to another as values for multiple worksheet
Replies
0
Views
296
priisha
P

Forum statistics

Threads
1,214,653
Messages
6,120,751
Members
448,989
Latest member
mariah3

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