Copy Columns from one Workbook into another

S

Sherifa

New Member
Joined
Oct 23, 2017
Messages
45
Copy Columns from one Workbook into another, looping through sheets 6 to the last in the worksheet.

Error message
Compile Error:
Duplicate declaration in current scope

Sub CopyaColumnToWorkbook()


Dim SourceWkb As Excel.Workbook
Dim TargetWkb As Excel.Workbook




Set SourceWkb = Workbooks.Open("I:\Documents\Transactional FX\Michael Kyte Projects\VBA.xlsm")
Set TargetWkb = Workbooks.Open("I:\Documents\Transactional FX\Michael Kyte Projects\VBA1.xlsm")


Dim SourceColumn As Range
Dim TargetColumn As Range


Set SourceColumn = SourceWkb.Worksheets(s).Columns("e:g")
Set TargetColumn = TargetWkb.Worksheets(t).Columns("e:g")


Dim ws_sourcecount As Integer
Dim ws_targetcount As Integer





ws_sourcecount = SourceWkb.Worksheets.Count
ws_targetcount = TargetWkb.Worksheets.Count

Dim s As Integer
Dim t As Integer

'Begin loop
For s = 7 To ws_sourcecount
For t = 7 To ws_targetcount


SourceWkb.Worksheets(s).Select 'just select the sheet
'Copy from first workbook

SourceColumn.Copy


TargetWkb.Worksheets(t).Select 'just select the sheet
'Paste formulas into second workbook
'Offset Paste (Offset 3 cell to the right)

TargetColumn.Offset(0, 3).PasteSpecial Paste:=xlPasteFormulas

Next s
Next t


End Sub
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Sort by date Sort by votes
Cart before the horse don't work. Try this.
Code: 
Sub CopyaColumnToWorkbook()
 Dim SourceWkb As Excel.Workbook
 Dim TargetWkb As Excel.Workbook
 Set SourceWkb = Workbooks.Open("I:\Documents\Transactional FX\Michael Kyte Projects\VBA.xlsm")
 Set TargetWkb = Workbooks.Open("I:\Documents\Transactional FX\Michael Kyte Projects\VBA1.xlsm")
 Dim SourceColumn As Range
 Dim TargetColumn As Range
 Dim ws_sourcecount As Integer
 Dim ws_targetcount As Integer
 ws_sourcecount = SourceWkb.Worksheets.Count
 ws_targetcount = TargetWkb.Worksheets.Count
 Dim s As Integer
 Dim t As Integer
 'Begin loop
    For s = 7 To ws_sourcecount
        For t = 7 To ws_targetcount
            S[COLOR=#DAA520]et SourceColumn = SourceWkb.Worksheets(s).Columns("e:g")
            Set TargetColumn = TargetWkb.Worksheets(t).Columns("e:g")[/COLOR]
            'SourceWkb.Worksheets(s).Select 'just select the sheet
            'Copy from first workbook
            SourceColumn.Copy
            'TargetWkb.Worksheets(t).Select 'just select the sheet
            'Paste formulas into second workbook
            'Offset Paste (Offset 3 cell to the right)
            TargetColumn.Offset(0, 3).PasteSpecial Paste:=xlPasteFormulas
        Next
    Next
End Sub
 
Upvote 0
It doesn't quite work, when I see it looping through Excel, it looks like it works, but the final product is data from only the last worksheet of the source.
When inserting the new columns how can I get vba to insert new rows for the cells and pushing existing columns to the right.
I thought the offset did that, but it overlaps existing data

Code:
Sub CopyaColumnToWorkbook()
Dim SourceWkb As Excel.Workbook
Dim TargetWkb As Excel.Workbook

Set SourceWkb = Workbooks("VBA.xlsm")
Set TargetWkb = Workbooks("VBA1.xlsm")

Dim SourceColumn As Range
Dim TargetColumn As Range

Dim ws_sourcecount As Integer

Dim ws_targetcount As Integer
ws_sourcecount = SourceWkb.Worksheets.Count
ws_targetcount = TargetWkb.Worksheets.Count

Dim s As Integer
Dim t As Integer

'Begin loop
For s = 7 To ws_sourcecount
For t = 7 To ws_targetcount
Set SourceColumn = SourceWkb.Worksheets(s).Columns("e:g")
Set TargetColumn = TargetWkb.Worksheets(t).Columns("b:d")
'Copy from first workbook
SourceColumn.Copy
'Paste formulas into second workbook
'Offset Paste (Offset 3 cell to the right)
TargetColumn.Offset(0, 3).PasteSpecial Paste:=xlPasteFormulas
Next
Next
End Sub
 
Upvote 0
Code: 
Sub CopyaColumnsToWorkbook()
 Dim SourceWkb As Excel.Workbook
 Dim TargetWkb As Excel.Workbook
 
 Set SourceWkb = Workbooks("VBA.xlsm")
 Set TargetWkb = Workbooks("VBA1.xlsm")
 
 Dim SourceColumn As Range
 Dim TargetColumn As Range
 
 Dim ws_sourcecount As Integer
 
 Dim ws_targetcount As Integer
 ws_sourcecount = SourceWkb.Worksheets.Count
 ws_targetcount = TargetWkb.Worksheets.Count
 
 Dim s As Integer
 Dim t As Integer
 
 'Begin loop
    For s = 7 To ws_sourcecount
        For t = 7 To ws_targetcount
            Set SourceColumn = SourceWkb.Worksheets(s).Columns("e:k")
            Set TargetColumn = TargetWkb.Worksheets(t).Columns("f:l")
            'Copy from first workbook
            SourceColumn.Copy
            'Paste formulas into second workbook
            TargetColumn.PasteSpecial Paste:=xlPasteFormulas
        Next
    Next
End Sub
 
Upvote 0
See if this fixes it
Code: 
Sub CopyaColumnToWorkbook2()
 Dim SourceWkb As Excel.Workbook
 Dim TargetWkb As Excel.Workbook
 Set SourceWkb = Workbooks.Open("I:\Documents\Transactional FX\Michael Kyte Projects\VBA.xlsm")
 Set TargetWkb = Workbooks.Open("I:\Documents\Transactional FX\Michael Kyte Projects\VBA1.xlsm")
 Dim SourceColumn As Range
 [COLOR=#daa520]Dim TargetColumn As Long[/COLOR]
 Dim ws_sourcecount As Integer
 Dim ws_targetcount As Integer
 ws_sourcecount = SourceWkb.Worksheets.Count
 ws_targetcount = TargetWkb.Worksheets.Count
 Dim s As Integer
 Dim t As Integer
 'Begin loop
 [COLOR=#daa520]TargetColumn = 4[/COLOR]
    For s = 7 To ws_sourcecount
        For t = 7 To ws_targetcount
            Set SourceColumn = SourceWkb.Worksheets(s).Columns("e:g")
            'SourceWkb.Worksheets(s).Select 'just select the sheet
            'Copy from first workbook
            SourceColumn.Copy
            'TargetWkb.Worksheets(t).Select 'just select the sheet
            'Paste formulas into second workbook
            'Offset Paste (Offset 3 cell to the right)
            If[COLOR=#daa520] TargetWkb.Worksheets(t).Columns(SourceColumn) = "" Then
                TargetWkb.Worksheets(t).Cells(1, SourceColumn).PasteSpecial xlPasteFormulas
            Else
                TargetWkb.Worksheets(t).Cells(1, Columns.Count).End(xlToLeft).PasteSpecial xlPasteFormulas
            End If[/COLOR]
        Next
    Next
End Sub

The original code does not advance the value of the target column, causing all subsequent copies to overwrite the previous.
 
Last edited:
Upvote 0
Code: 
Sub CopySetColumnsToWorkbook() Dim SourceWkb As Excel.Workbook
 Dim TargetWkb As Excel.Workbook
 
 Set SourceWkb = Workbooks("VBA.xlsm")
 Set TargetWkb = Workbooks("VBA1.xlsm")
 
 Dim SourceColumn As Range
 Dim TargetColumn As Long
 
 Dim ws_sourcecount As Integer
 
 Dim ws_targetcount As Integer
 ws_sourcecount = SourceWkb.Worksheets.Count
 ws_targetcount = TargetWkb.Worksheets.Count
 
 Dim s As Integer
 Dim t As Integer
 
 'Begin loop
  TargetColumn = 4
    For s = 7 To ws_sourcecount
        For t = 7 To ws_targetcount
            Set SourceColumn = SourceWkb.Worksheets(s).Columns("e:k")
                
                'Copy from first workbook
            SourceColumn.Copy
                'Paste formulas into second workbook
             If TargetWkb.Worksheets(t).Columns(SourceColumn) = "" Then
                TargetWkb.Worksheets(t).Cells(1, SourceColumn).PasteSpecial xlPasteFormulas
            Else
                TargetWkb.Worksheets(t).Cells(1, Columns.Count).End(xlToLeft).PasteSpecial xlPasteFormulas
            End If
        Next
    Next
End Sub

Run time error 13 Type mismatch
 
Upvote 0
I've changed it back, because the worksheet no longer needs to shift existing columns.
Just copy e-k in the space f-l.
But it pastes the last worksheet columns from Source in f-l on all the target. The lop is not working correctly.
Thank you for the help. Today is my 3rd day using vba

Sub CopySetColumnsToWorkbook()
Dim SourceWkb As Excel.Workbook
Dim TargetWkb As Excel.Workbook

Set SourceWkb = Workbooks("VBA.xlsm")
Set TargetWkb = Workbooks("VBA1.xlsm")

Dim SourceColumn As Range
Dim TargetColumn As Range

Dim ws_sourcecount As Integer

Dim ws_targetcount As Integer
ws_sourcecount = SourceWkb.Worksheets.Count
ws_targetcount = TargetWkb.Worksheets.Count

Dim s As Integer
Dim t As Integer

'Begin loop


For s = 7 To ws_sourcecount
For t = 7 To ws_targetcount
Set SourceColumn = SourceWkb.Worksheets(s).Columns("e:k")
Set TargetColumn = TargetWkb.Worksheets(t).Columns("f:l")

'Copy from first workbook
SourceColumn.Copy
'Paste formulas into second workbook
TargetColumn.PasteSpecial Paste:=xlPasteFormulas
Next
Next
End Sub
 
Upvote 0
Sub CopySetColumnsToWorkbook() Dim SourceWkb As Excel.Workbook
Dim TargetWkb As Excel.Workbook

Set SourceWkb = Workbooks("VBA.xlsm")
Set TargetWkb = Workbooks("VBA1.xlsm")

Dim SourceColumn As Range
Dim TargetColumn As Range

Dim ws_sourcecount As Integer

Dim ws_targetcount As Integer
ws_sourcecount = SourceWkb.Worksheets.Count
ws_targetcount = TargetWkb.Worksheets.Count

Dim s As Integer
Dim t As Integer

'Begin loop


For s = 7 To ws_sourcecount
For t = 7 To ws_targetcount
Set SourceColumn = SourceWkb.Worksheets(s).Columns("e:k")
Set TargetColumn = TargetWkb.Worksheets(t).Columns("f:l")

'Copy from first workbook
SourceColumn.Copy
'Paste formulas into second workbook
TargetColumn.PasteSpecial Paste:=xlPasteFormulas
Next
Next
End Sub
Click to expand...

Code Form
 
Upvote 0
My error should be
Code: 
SourceColumn.Copy
                'Paste formulas into second workbook
             If TargetWkb.Worksheets(t).Columns([COLOR=#b22222]TargetColumn[/COLOR]) = "" Then
                TargetWkb.Worksheets(t).Cells(1, T[COLOR=#b22222]argetColumn).[/COLOR]PasteSpecial xlPasteFormulas
            Else

The type mismatch error occurs when a variable is used that is a different data type than the process is looking for. Can be that the varable is declared improperly in the Dim statements or the wrong variable is used (as in the case above) in the statement.
 
Last edited:
Upvote 0
Code: 
Sub CopySetColumnsToWorkbook()Dim SourceWkb As Excel.Workbook
Dim TargetWkb As Excel.Workbook


Set SourceWkb = Workbooks("VBA.xlsm")
Set TargetWkb = Workbooks("VBA1.xlsm")


Dim SourceColumn As Range
Dim TargetColumn As Range


 Dim ws_sourcecount As Integer
 Dim ws_targetcount As Integer
 
    ws_sourcecount = SourceWkb.Worksheets.Count
    ws_targetcount = TargetWkb.Worksheets.Count


Dim s As Integer




 'Begin loop


    For s = 7 To ws_sourcecount
          
            Set SourceColumn = SourceWkb.Worksheets(s).Columns("e:k")
            Set TargetColumn = TargetWkb.Worksheets([B]s[/B]).Columns("f:l")
        
                'Copy from first workbook
            SourceColumn.Copy
                'Paste formulas into second workbook
          
            TargetColumn.PasteSpecial xlPasteFormulas
            TargetColumn.PasteSpecial xlPasteFormats
        Next


End Sub

I've added the edit, in case anyone ever finds this code helpful.
I removed the t loop and it worked. The positioning of the sheets for both spreadsheets were the same and it got my code to work.
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Solved
VBA Copy and Paste from one workbook to another, append to last column
Replies
4
Views
5K
Zot
Z
D
  • Question
Copying from last row column c from one workbook to another
Replies
3
Views
572
DanteAmor
DanteAmor
J
  • Question
VBA Copy and Paste Data from one workbook to another - specific columns
2
Replies
16
Views
1K
jayped
J
C
  • Question
Copying data from one workbook to another. Multiple columns and rows to copy and add to values already present...
Replies
1
Views
540
chipsworld
C
L
  • Question
copy sheet from one wb to another
Replies
4
Views
474
Fluff
Fluff

Forum statistics

Threads
1,214,574
Messages
6,120,329
Members
448,956
Latest member
Adamsxl

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top