column count

[Dummy Excel]

Hi,
I am trying to create a simple macro where it copies columns from one worksheet to another, although the VB code needs to be dynamic as the number of columns changes on a weekly basis.

So basically I need the macro to determine how many columns there are, then copy each column into another workbook...why create a macro to do this instead of using range("A1").currentregion.copy I hear you say well basically when I paste it into the other worksheet I need to paste them in a specific place which I will put the VB code together.

can anyone help to start me off??

cheers
Sam

 

[bertie]

Use the Cells() function to get the number of rows and columns;

         'get the column count
         With wsSource
            'using row one to count the number of columns
            colEnd = .Cells(1, .Columns.Count).End(xlToLeft).Column
            
            'using column one to count the number of rows
            rowEnd = .Cells(.Rows.Count, 1).End(xlUp).Row

Then use the variables to copy and paste the range/
NB adjust for copying individual columns.

            '
            'copy and paste
            .Range(.Cells(1, 1), .Cells(rowEnd, colEnd)).Copy _
               Destination:=wsTarget.Range("A" & rowStart)

 

[L. Howard]

Give this a try, where you run the code, enter a column letter of the column want to copy, and enter a column letter for the column you want to paste to in the other workbook

Regards,
Howard

Option Explicit
Sub CopyBookToBook2()
' With help by Claus

' The workbook "Copy To WKBook.xlsm" must be a saved workbook.  Name your own and save it.

Dim ColRngFrm As String
Dim ColRngTo As String
Dim LRow As Long

LRow = Cells(Rows.count, 1).End(xlUp).Row

ColRngFrm = Application.InputBox(Prompt:="Enter a Column Letter to copy.", _
Title:="Column Letter From", Type:=2)
If ColRngFrm = "" Or ColRngFrm = "False" Then Exit Sub

ColRngTo = Application.InputBox(Prompt:="Enter a Column Letter to paste to.", _
Title:="Column Letter To", Type:=2)
If ColRngTo = "" Or ColRngTo = "False" Then Exit Sub

Range(Cells(1, ColRngFrm), Cells(LRow, ColRngFrm)).Copy _
Workbooks("Copy To WKBook").Sheets("Sheet1").Cells(1, ColRngTo)
    
End Sub