PDA

View Full Version : [SOLVED] Unqualified Error



olegvolf
10-06-2014, 08:37 PM
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
10-07-2014, 12:34 AM
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
10-07-2014, 12:40 AM
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
10-07-2014, 12:58 AM
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
10-07-2014, 01:07 AM
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
10-07-2014, 01:57 AM
Hi
I am sorry please ignor the 1st question.
Only the second one

Thanks.

Bob Phillips
10-07-2014, 03:49 AM
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
10-07-2014, 05:15 AM
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