Creating a new column and pasting data into that new column all with VBA-CodePudding

I am attempting to write a macro that creates a new column on a different sheet and then copies some data and pastes that data into the newly created column. I am fairly new to VBA and would love some direction on how to do this. Below is the code I have written at the moment. The first sub is a new column to the left and the second sub is the column to the right. I have only really started with the first macro. Thank you so much.

Few things to note, I have the insert column part working. I hid a column and have a cell in there as a named range which I used to select in my macro. The data I want to copy is on the Input sheet and is named InputData.

'''

Dim sourceSheet As Worksheet
Set sourceSheet = ActiveSheet
Sheets("Data").Activate
Sheets("Data").Range("DividerColumn").Select
Selection.EntireColumn.Offset(0, 0).Insert Shift:=xlToLeft
'Sheets("Input").Activate
'Range("InputData").Copy
'Sheets("Data").Activate
'ActiveCell offset maybe?
'Range().PasteSpecial xlPasteValues
Call sourceSheet.Activate

End Sub

'''

Dim sourceSheet As Worksheet

Set sourceSheet = ActiveSheet

Sheets("Data").Activate

Sheets("Data").Range("DividerColumn").Select

Selection.EntireColumn.Offset(0, 1).Insert Shift:=xlToRight

Call sourceSheet.Activate

End Sub

'''

CodePudding user response：

Oh I didn't see your copy range. In that case this could probably work. I see you just got the answer, but this would be a good way to avoid select.

Sub copyToLeft()
   Call doTheCopy(False)
End Sub
Sub CopyToRight()
   Call doTheCopy(True)
End Sub

Private Sub doTheCopy(goRightIsTrue As Boolean)
   With Sheets("Data").Range("DividerColumn").EntireColumn.Offset(0, IIf(goRightIsTrue, 1, 0))
      .Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
      .Offset(0, -1).Value = Sheets("Input").Range("InputData").EntireColumn.Value
   End With
End Sub

CodePudding user response：

I found the solution by using an offset function. Below is my code. Hope this helps someone with a similar situation.

   Dim sourceSheet As Worksheet
   Set sourceSheet = ActiveSheet
   Sheets("Data").Activate
   Sheets("Data").Range("DividerColumn").Select
   Selection.EntireColumn.Offset(0, 0).Insert 
   Shift:=xlToLeft
   Sheets("Input").Activate
   Range("InputData").Copy
   Sheets("Data").Activate
   Range("DividerColumn").Select
   ActiveCell.Offset(0, -1).PasteSpecial 
   xlPasteValues
   Call sourceSheet.Activate

   End Sub

-1 in the offset function moves your active cell to the left one cell and then 1 moves it to the right. So once the column is created, either right or left, my macro goes and copies the information and then goes back to the sheet I want it to and selects my named range again and then it gets moved to the left and pastes my data.