Code doesn't work when switching tabs

[WilliamA]

Hello,

I have VBA code which copies information from one spreadsheet to another. The original spreadsheet has many tabs, and when I use the code in it's current form, it throws this error when I try to copy over the columns from a tab that's different to the one that the spreadsheet opens on.

Is it possible to update the code to allow users to select columns from whichever tab.

Sub copy_data()

Dim data_wb As Workbook
Dim target_wb As Workbook
Dim file_name As Variant
Dim header_range(100) As Range
Dim last_row As Long
Dim col_number As Long
Dim col_letter As String
Dim counter As Long
Dim quantity As Long

'select workbook
file_name = Application.GetOpenFilename(Title:="Choose a target workbook")

If file_name <> False Then

    'create a new target workbook
    Set target_wb = Application.Workbooks.Add

    'open workbook with the data
    Set data_wb = Application.Workbooks.Open(file_name)
        
    'get quantity to create loop
    quantity = _
    InputBox("How many columns do you want to copy?")
    
    'loop
    For counter = 1 To quantity
    
        'select header range
        Set header_range(counter) = _
        Application.InputBox("Select the " & counter & "º column you want to copy", Type:=8)
        
        'get last row and col letter
        col_number = header_range(counter).Column
        last_row = Cells(Rows.Count, col_number).End(xlUp).Row
        col_letter = Split(Cells(1, col_number).Address(True, False), "$")(0)

        'copy from data_wb
        Range(header_range(counter), Range(col_letter & last_row)).Copy
        
        'paste in target_wb
        target_wb.Sheets("Sheet1").Cells(1, counter).PasteSpecial xlPasteValues
        
        
    Next counter
    
    data_wb.Close
    
    If Not target_wb.Saved Then
        If MsgBox("Do you want to save the file?", vbYesNo, "Save?") = vbYes Then
        Rows("1:9").Delete
        target_wb.Save
        End If
    End If

End If

target_wb.Close

End Sub

 

[ouvay]

whichever tab

Hi, quick question ... by tab you mean worksheet?

 

[StephenCrump]

Your problem is unqualified references like this:

Range(header_range(counter))

Range refers to the ActiveSheet. So if header_range(counter) is in another worksheet, you'll get a run time error 1004.

I think this is what you're wanting to do?

For counter = 1 To quantity
    Set header_range(counter) = Application.InputBox("Select the " & counter & "º column you want to copy", Type:=8)
    With header_range(counter)
        last_row = .Parent.Cells(Rows.Count, .Column).End(xlUp).Row
        .Resize(last_row - .Row + 1).Copy
        Sheets("Sheet1").Cells(1, counter).PasteSpecial xlPasteValues
    End With
Next counter

 

[WilliamA]

Hi, quick question ... by tab you mean worksheet?

Yes, worksheet

 

[WilliamA]

Your problem is unqualified references like this:

Range(header_range(counter))

Range refers to the ActiveSheet. So if header_range(counter) is in another worksheet, you'll get a run time error 1004.

I think this is what you're wanting to do?

For counter = 1 To quantity
    Set header_range(counter) = Application.InputBox("Select the " & counter & "º column you want to copy", Type:=8)
    With header_range(counter)
        last_row = .Parent.Cells(Rows.Count, .Column).End(xlUp).Row
        .Resize(last_row - .Row + 1).Copy
        Sheets("Sheet1").Cells(1, counter).PasteSpecial xlPasteValues
    End With
Next counter

Hi,

Thanks for looking into this.

I replaced the code, and it allows me to select columns from another worksheet without returning the error, but it stopped copying over the columns to the new spreadsheet.

Sub copy_data()

Dim data_wb As Workbook
Dim target_wb As Workbook
Dim file_name As Variant
Dim header_range(100) As Range
Dim last_row As Long
Dim col_number As Long
Dim col_letter As String
Dim counter As Long
Dim quantity As Long

'select workbook
file_name = Application.GetOpenFilename(Title:="Choose a target workbook")

If file_name <> False Then

    'create a new target workbook
    Set target_wb = Application.Workbooks.Add

    'open workbook with the data
    Set data_wb = Application.Workbooks.Open(file_name)
        
    'get quantity to create loop
    quantity = _
    InputBox("How many columns do you want to copy?")
    
    'loop
    For counter = 1 To quantity
    
        'select header range
        Set header_range(counter) = _
        Application.InputBox("Select the " & counter & "º column you want to copy", Type:=8)
        
        'get last row and col letter
        With header_range(counter)
            last_row = .Parent.Cells(Rows.Count, .Column).End(xlUp).Row

        'copy from data_wb
        .Resize(last_row - .Row + 1).Copy
        
        'paste in target_wb
        Sheets("Sheet1").Cells(1, counter).PasteSpecial xlPasteValues

    End With
    
    Next counter
    
    data_wb.Close
    
    If Not target_wb.Saved Then
        If MsgBox("Do you want to save the file?", vbYesNo, "Save?") = vbYes Then
        Rows("1:9").Delete
        target_wb.Save
        End If
    End If

End If

target_wb.Close

End Sub

 

[StephenCrump]

Just a quick guess ...

'paste in target_wb
target_wb.Sheets("Sheet1").Cells(1, counter).PasteSpecial xlPasteValues

 

[WilliamA]

Just a quick guess ...

'paste in target_wb
target_wb.Sheets("Sheet1").Cells(1, counter).PasteSpecial xlPasteValues

Thank you, I really appreciate it.