Unqualified Error

[olegvolf]

Hello
I trying to copy a table from one workbook to other.
The Table can be different sizes but starts in the same place.
I am getting an "unqualified error message"
Please see the code below and 2 workbooks.
Please help to solve it:
12365123661236512366

Sub copy_data()Dim x As Workbook
Dim y As Workbook


'## Open both workbooks first:
Set x = Workbooks.Open("C:\Users\oleg_volfson\Desktop\test\HEAD1")
Set y = ThisWorkbook
'Set y = Workbooks.Open(" path to destination book ")


'Now, transfer values from x to y:
'y.Sheets("Data Sheet").Range("B4").Value = x.Sheets("TABLE").Range("A1")
'y.Sheets("Data Sheet").Range("B4").Value = x.Sheets("TABLE").Range("A1")
With x.Sheets("TABLE").Range("B5").Resize(.Rows.Count, .Columns.Count) = .Value

y.Sheets("SHEET1").Range ("B4")


End With
'Close x:
x.Close


End Sub

[Bob Phillips]

What is the range that you are trying to load with the value in B4? It looks like every row and column from B5 down and across.

[olegvolf]

thank you very much for your replay
I want to load into oleg1 file all the rows and columns from the head1 file starting with B5
and paste it into B4 in the oleg1 file.
Thanks

[Bob Phillips]

See if this helps


Sub copy_data()
Dim x As Workbook
Dim y As Workbook
Dim numrows As Long
Dim numcols As Long

'## Open both workbooks first:
Set x = Workbooks.Open("C:\Users\oleg_volfson\Desktop\test\HEAD1")
Set y = ThisWorkbook
'Set y = Workbooks.Open(" path to destination book ")

'Now, transfer values from x to y:
'y.Sheets("Data Sheet").Range("B4").Value = x.Sheets("TABLE").Range("A1")
'y.Sheets("Data Sheet").Range("B4").Value = x.Sheets("TABLE").Range("A1")
With x.Worksheets("TABLE")

numrows = .Cells(.Rows.Count, "B").End(xlUp).Row - 4
numcols = .Cells(2, .Columns.Count).End(xlToLeft).Column - 1
.Range("B5").Resize(numrows, numcols).Copy y.Sheets("SHEET1").Range("B4")
End With

x.Close
End Sub

[olegvolf]

Thank you it works 2 more questions:
1.Why the macro is copying till column "P"?
2. Instead of making a path to the source file is it possible to have a browse for choosing?

Thank you

[olegvolf]

Hi
I am sorry please ignor the 1st question.
Only the second one

Thanks.

[Bob Phillips]

Sub copy_data()
Dim x As Workbook
Dim y As Workbook
Dim numrows As Long
Dim numcols As Long

'## Open both workbooks first:
With Application.FileDialog(msoFileDialogOpen)

.AllowMultiSelect = False
.InitialFileName = "*.xls*"
If .Show = -1 Then

Set x = Workbooks.Open(.SelectedItems(1))
Set y = ThisWorkbook
'Set y = Workbooks.Open(" path to destination book ")

'Now, transfer values from x to y:
'y.Sheets("Data Sheet").Range("B4").Value = x.Sheets("TABLE").Range("A1")
'y.Sheets("Data Sheet").Range("B4").Value = x.Sheets("TABLE").Range("A1")
With x.Worksheets("TABLE")

numrows = .Cells(.Rows.Count, "B").End(xlUp).Row - 4
numcols = .Cells(2, .Columns.Count).End(xlToLeft).Column - 1
.Range("B5").Resize(numrows, numcols).Copy y.Sheets("SHEET1").Range("B4")
End With

x.Close
End If
End With
End Sub

[olegvolf]

Sub copy_data()
Dim x As Workbook
Dim y As Workbook
Dim numrows As Long
Dim numcols As Long

'## Open both workbooks first:
With Application.FileDialog(msoFileDialogOpen)

.AllowMultiSelect = False
.InitialFileName = "*.xls*"
If .Show = -1 Then

Set x = Workbooks.Open(.SelectedItems(1))
Set y = ThisWorkbook
'Set y = Workbooks.Open(" path to destination book ")

'Now, transfer values from x to y:
'y.Sheets("Data Sheet").Range("B4").Value = x.Sheets("TABLE").Range("A1")
'y.Sheets("Data Sheet").Range("B4").Value = x.Sheets("TABLE").Range("A1")
With x.Worksheets("TABLE")

numrows = .Cells(.Rows.Count, "B").End(xlUp).Row - 4
numcols = .Cells(2, .Columns.Count).End(xlToLeft).Column - 1
.Range("B5").Resize(numrows, numcols).Copy y.Sheets("SHEET1").Range("B4")
End With

x.Close
End If
End With
End Sub



Thank you very mich