VBA to Select Worksheet and Column in Another Worksheet

[srdavisgb]

I have a worksheet named for a specific date. The date can also be found in cell A1. I want to copy a column of the data in this worksheet to a column in a summary worksheet with the same date (same workbook). I can find the column number of the column I want to select in the summary worksheet (i.e., D). But, I can't figure-out the code to select this column. This is what I have tried so far...

The reason I want to select the column is to be able to insert a column to do some additional calculations with the data.

Activesheet
A1 = 01/03/2019
A2 = D (column in the Summary worksheet I want to select)


Column = Activesheet.Cells(2, 1)
Sheets("Summary Worksheet").Select
Columns(""Column"&":""Column"").Select

Your suggestions are appreciated.

 

[dmt32]

Hi,
you should not need to select the worksheet or range to what you want

Dim Col As Variant
Dim rng As Range


Col = ActiveSheet.Cells(2, 1)


Set rng = Sheets("Summary Worksheet").Columns(Col)


rng.EntireColumn.Insert

Dave

 

[srdavisgb]

Hi,
you should not need to select the worksheet or range to what you want

Dim Col As Variant
Dim rng As Range


Col = ActiveSheet.Cells(2, 1)


Set rng = Sheets("Summary Worksheet").Columns(Col)


rng.EntireColumn.Insert

Dave[/QUOTE]

 

[srdavisgb]

Thanks Dave.

The first time I run the code, it works as expected. However, the second and subsequent times I run the code it does not work. Below is my code. What is the code I'm running. What am I doing wrong.

Dim Col As Variant
Dim rng As Range
Dim location As String






Col = ActiveSheet.Cells(38, 13)
location = ActiveSheet.Cells(39, 13)




'Copy Points Needed column and insert the copied column


Set rng = Sheets("Master Spreadsheet (srd)").Columns(Col)

Sheets("Master Spreadsheet (srd)").Select
Selection.Copy
Selection.Insert Shift:=xlToRight

Sheets("Master Spreadsheet (srd)").Activate
RangeArea = location
Range(RangeArea).Select


'Add Formula to Inserted Column


ActiveCell.FormulaR1C1 = "=IF(ISNUMBER(RC[1]),RC[1],RC[-1])"
RangeArea("Col").Select

Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.FillDown



Again, thanks for your assistance.