Copy and paste Column Data into new worksheet

[J7House1984]

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:

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.

 

[Fluff]

Hi & welcome to MrExcel.
Will the columns be the same size as each other when you run the code?

 

[J7House1984]

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.

 

[Fluff]

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]

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 Name	Initial	Last Name	City	State	Zip Code	Email
Valery	F	Barnes	Hamilton	MI	49419​	Valery.Barnes@fake_email.com
Dalary	V	Phillips	South Haven	MI	49090​
Rachel	D	Campbell	New Haven	MI	48050​	Rachel.Campbell@fake_email.com
Braxton	R	Clark	Monroe			Braxton.Clark@fake_email.com
Lewis	B	Lopez		MI	48875​	Lewis.Lopez@fake_email.com
Estella		Bell	Lansing	MI	48951​	Estella.Bell@fake_email.com
Cooper	E	Hughes	Ann Arbor	MI	48105​	Cooper.Hughes@fake_email.com
Julian	C	Scott	New Baltimore	MI	48047​	Julian.Scott@fake_email.com
Liberty	J	Williams	Sterling	MI	48659​	Liberty.Williams@fake_email.com
Oaklee	L	Ward	Sawyer	MI	49125​	Oaklee.Ward@fake_email.com

Does this make sense?

 

[Fluff]

Ok, how about

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

 

[J7House1984]

Ok, how about

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?

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]

That's right.

 

[J7House1984]

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]

If the problem is adapting the code to cover the other columns, then post back here, otherwise start a new thread.