Copy and paste Column Data into new worksheet

J7House1984

New Member
Joined
Oct 30, 2020
Messages
11
Office Version
  1. 365
Platform
  1. Windows
Hello,

I am at a very beginner level of knowledge everything I've done in VBA has been self-taught.

I am working on a project that I need some assistance with. I have been tasked with automating a simple copy and paste of multiple columns into a new worksheet template.

I have a data sheet that will be copy and paste into the "InquickerData" Tab. The data will be of various lengths some times hundreds of entries sometimes thousands or more. Each month will potentially be different. I have a basic script that I think can work but I am having some error issues with it. Also, if there is a better way to do this I would greatly appreciate any and all input.

Below is the Script I am working on:

VBA Code:
Sub MM1()
'Copy "Registration Date" Data
  Worksheets("InquickerData").Range("M2:M1048576").Copy

'PasteSpecial Values Only
  Worksheets("UploadTemplate").Range("M8:M1048576").PasteSpecial Paste:=xlPasteValues

'Copy "First Name" Data
  Worksheets("InquickerData").Range("S2:S1048576").Copy

'PasteSpecial Values Only
  Worksheets("UploadTemplate").Range("S8:S1048576").PasteSpecial Paste:=xlPasteValues
  
  'Copy "Middle Initial" Data
  Worksheets("InquickerData").Range("T2:T1048576").Copy

'PasteSpecial Values Only
  Worksheets("UploadTemplate").Range("T8:1048576").PasteSpecial Paste:=xlPasteValues

'Clear Clipboard (removes data around your original data set)
  Application.CutCopyMode = False
  
End Sub

I can also send an example workbook for reference if that helps.
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,544
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
Will the columns be the same size as each other when you run the code?
 

J7House1984

New Member
Joined
Oct 30, 2020
Messages
11
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
Will the columns be the same size as each other when you run the code?
No, I think they will most likely be different sizes.

I have attached screenshots for reference, if that helps.
 

Attachments

  • Inquiker Data sheet.png
    Inquiker Data sheet.png
    151.5 KB · Views: 7
  • Upload Template.png
    Upload Template.png
    18.7 KB · Views: 7

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,544
Office Version
  1. 365
Platform
  1. Windows
So when you run the code, you could have 200 rows in col M, whilst col S might have 50 rows & T 250 rows?
 

J7House1984

New Member
Joined
Oct 30, 2020
Messages
11
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

So when you run the code, you could have 200 rows in col M, whilst col S might have 50 rows & T 250 rows?
I apologize I think I misunderstood you initial quest. There will be the same amount of rows for each column. There could be some instances when John Doe doesn't put in their Email or City, State, Zip. but I still want to bring over all available data in their respective columns and row.

for example:
First NameInitialLast NameCityStateZip CodeEmail
ValeryFBarnesHamiltonMI
49419​
Valery.Barnes@fake_email.com
DalaryVPhillipsSouth HavenMI
49090​
RachelDCampbellNew HavenMI
48050​
Rachel.Campbell@fake_email.com
BraxtonRClarkMonroeBraxton.Clark@fake_email.com
LewisBLopezMI
48875​
Lewis.Lopez@fake_email.com
EstellaBellLansingMI
48951​
Estella.Bell@fake_email.com
CooperEHughesAnn ArborMI
48105​
Cooper.Hughes@fake_email.com
JulianCScottNew BaltimoreMI
48047​
Julian.Scott@fake_email.com
LibertyJWilliamsSterlingMI
48659​
Liberty.Williams@fake_email.com
OakleeLWardSawyerMI
49125​
Oaklee.Ward@fake_email.com


Does this make sense?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,544
Office Version
  1. 365
Platform
  1. Windows
Ok, how about
VBA Code:
Sub J7House()
   Dim UsdRws As Long
   
   With Worksheets("InquickerData")
      UsdRws = .Range("M:T").find("*", , xlValues, , xlByRows, xlPrevious, , , False).Row
      Worksheets("UploadTemplate").Range("M8").Resize(UsdRws - 1).Value = .Range("M2:M" & UsdRws).Value
      Worksheets("UploadTemplate").Range("S8").Resize(UsdRws - 1).Value = .Range("S2:T" & UsdRws).Value
   End With
End Sub
 
Solution

J7House1984

New Member
Joined
Oct 30, 2020
Messages
11
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Ok, how about
VBA Code:
Sub J7House()
   Dim UsdRws As Long
  
   With Worksheets("InquickerData")
      UsdRws = .Range("M:T").find("*", , xlValues, , xlByRows, xlPrevious, , , False).Row
      Worksheets("UploadTemplate").Range("M8").Resize(UsdRws - 1).Value = .Range("M2:M" & UsdRws).Value
      Worksheets("UploadTemplate").Range("S8").Resize(UsdRws - 1).Value = .Range("S2:T" & UsdRws).Value
   End With
End Sub
This works really well. There are a few tweaks I have to do to use on my live document.

I have to copy 11 different columns from the InQuicker tab and place them in the so would it be appropriate to assume that I would just copy the following snippets and adjusting it for the needed columns. Would this be correct?

VBA Code:
Worksheets("UploadTemplate").Range("M8").Resize(UsdRws - 1).Value = .Range("M2:M" & UsdRws).Value
      Worksheets("UploadTemplate").Range("S8").Resize(UsdRws - 1).Value = .Range("S2:T" & UsdRws).Value
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,544
Office Version
  1. 365
Platform
  1. Windows
That's right. :)
 

J7House1984

New Member
Joined
Oct 30, 2020
Messages
11
Office Version
  1. 365
Platform
  1. Windows
That's right. :)
Ok, awesome. I will add to the script you provided. Thank you so much. If I need anymore help on this thread do I just add to this one or make a new thread and reference this particular thread?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,544
Office Version
  1. 365
Platform
  1. Windows
If the problem is adapting the code to cover the other columns, then post back here, otherwise start a new thread.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,513
Messages
5,636,781
Members
416,940
Latest member
JohanT

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