Macro to format pasted data

wzthxj

New Member
Joined
Mar 29, 2021
Messages
12
Office Version
  1. 365
Platform
  1. Windows
I’ve been given an assignment to create a workbook template. It consists primarily of data from other spreadsheets that is copied and pasted manually into the template onto blank sheets. The number of columns will vary by user, and on one sheet, I need to be able to add 2 custom columns with headers at the far right without knowing how many columns there will be. The cells in one of the columns will also need a drop down menu without knowing how many rows there will be. I currently have a single macro created to auto-size the columns, add borders, etc. but I have no idea how to control where these new columns would be created. Any help would be greatly appreciated! Thank you in advance.
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,771
Since its an assignment.


Generally in Excel we are dealing with a single block of data. Wether it be a formal table or otherwise.

Finding the last row or last column is something that needs to happen often. The link supplied shows a few methods.

This is used I'd say most often for last row.

VBA Code:
'Ctrl + Shift + End
  LastRow = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row

The key here is selecting a column of data that is fully populated. In the above it looks in Column A.
 

wzthxj

New Member
Joined
Mar 29, 2021
Messages
12
Office Version
  1. 365
Platform
  1. Windows
Since its an assignment.


Generally in Excel we are dealing with a single block of data. Wether it be a formal table or otherwise.

Finding the last row or last column is something that needs to happen often. The link supplied shows a few methods.

This is used I'd say most often for last row.

VBA Code:
'Ctrl + Shift + End
  LastRow = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row

The key here is selecting a column of data that is fully populated. In the above it looks in Column A.
Thank you for your reply, but unfortunately I don’t know how to use the information. Appreciate your time though.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,445
Messages
5,636,322
Members
416,912
Latest member
danluk12

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