Copying Column from one spreadsheet to anothe

TheOddGirl · May 29, 2019

Hi,

This seems a really easy problem but just cannot figure it out and hoping for some help [FONT=&quot]?[/FONT]

I have a spreadsheet with specific names in the columns that I want to copy and paste into a new spreadsheet using a VBA macro. The column positions are not fixed and they can change from day to day.

If I can get it to work with one column, I’ll be able to get the rest to work. The macro needs to be able to find a specific named column and then copy and paste that column to another spreadsheet. Hoping someone can help me [FONT=&quot]?

Cheers.
[/FONT]

alansidman · May 29, 2019

Code:

Option Explicit


Sub OddGirl()
    Dim s1 As Worksheet, s2 As Worksheet
    Set s1 = Sheets("Sheet1")
    Set s2 = Sheets("Sheet2")
    Dim crit As String
    Dim lr As Long, i As Long
    Dim lc As Long
    crit = InputBox("What is column header to search")
    lc = s1.Cells(1, Columns.Count).End(xlToLeft).Column
    For i = 1 To lc
        If Cells(1, i) = crit Then
            lr = s1.Cells(Rows.Count, i).End(xlUp).Row
            s1.Range(Cells(1, i), Cells(lr, i)).Copy
            s2.Range("A1").PasteSpecial xlPasteValues
        End If
    Next i
End Sub

TheOddGirl · May 29, 2019

Hi Alan,

That's great many many thanks for this - worked fine and I can modify it to suit my needs now as I can see how it works. Thanks again for taking the time to write this

Cheers..

Copying Column from one spreadsheet to anothe

TheOddGirl

New Member

Excel Facts

alansidman

Well-known Member

TheOddGirl

New Member

Similar threads

Forum statistics

Share this page

Copying Column from one spreadsheet to anothe

TheOddGirl

New Member

Excel Facts

alansidman

Well-known Member

TheOddGirl

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock