Copying between sheets using VBA

M

Mike54

Active Member
Joined
Apr 17, 2002
Messages
258
Hi I'm strugling to write some VBA code, it's a bit untidy at present but it works untill I try to import values from sheet 2 to sheet1, I get an object error, but it dosen't mean anything to me.

Any help greatfully recieved.

Basically this code looks at sheet2 if the data is the same it overwrites it with upto date data, else it will add new unique enteries
to the last row.

Many thanks
Mike

Sub Importer()
Dim y As Integer
Dim z As Integer
Dim R As Long
'database
Sheets("Sheet1").Select
Cells(1, 1).Select
Range(Selection, Selection.End(xlDown)).Select
R = Selection.Rows.Count
'daily data
Sheets("Sheet2").Select
Cells(1, 1).Select
Dim Amax As Integer
'this is the number of rows in the first unique column ie Daily List, created
'by selection
Dim Bmax As Integer
'this is the number of rows in the second unique column ie Our in database List, created
'by selection
Range(Selection, Selection.End(xlDown)).Select
Bmax = Selection.Rows.Count
Cells(1, 1).Select

Sheets("Sheet1").Select
Cells(1, 1).Select
Range(Selection, Selection.End(xlDown)).Select
Amax = Selection.Rows.Count

Cells(2, 4).Select
Application.ScreenUpdating = False
'Range(Selection, Selection.End(xlDown)).Select
'R = Selection.Rows.Count
z = 2
y = 2
For z = 2 To Amax
For y = 2 To Bmax
If Sheet2.Cells(y, 4) = Sheet1.Cells(z, 4) Then

'NEXT LINE WON'T WORK

Sheet1.Range(Cells(z, 1), Cells(z, 18)).Value = Sheet2.Range(Cells(y, 1), Cells(y, 18)).Value


'Sheets("Sheet2").Select
'Cells(y, 14).EntireRow.Copy

'Selection.Copy
'database
Sheets("Sheet1").Select
Sheet1.Cells(y, 14).Select
Sheets("Sheet1").Paste
z = z + 1
y = 2



'daily
Sheets("Sheet2").Select
Else: Sheet2.Cells(z, 14).Select
'Range(Selection, Selection.End(xlToRight)).Select
Cells(z, 14).EntireRow.Copy
'Selection.Copy
R = R + 1
'inportlist
Sheets("Sheet1").Select
Sheet1.Cells(R, 14).Select
Cells(R, 14) = "QQQ"
End If
Next y
Next z
Application.ScreenUpdating = True


End Sub
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Sort by date Sort by votes
Hi Mike

Part of your problem is due to unqulaified range (Cells) references - unless you specify which sheet the Cells apply to eg Sheet1.Cells(), then the Cells applies to whatever is the Activesheet. Unfortunately, if you have Sheet1.Range(Cells(),Cells()), the Range applies to Sheet1 but the Cells apply to whatever sheet is active - which can result in a mismatch.

However, you were also attempting to compare arrays with the following line, rather than a discrete single value:

Code: 
Sheet1.Range(Cells(z, 1), Cells(z, 18)).Value = Sheet2.Range(Cells(y, 1), Cells(y, 18)).Value

Quite apart from the aforementioned unqulaified range refs issue, you are comparing the range of values encompassed by Range(Cells(z, 1), Cells(z, 18)) to the range of values encompassed by Range(Cells(y, 1), Cells(y, 18)) which means you will get a type mismatch. You need to loop thru the values in these arrays testing each against the other in turn.
 
Upvote 0
The two argument form of Range is not a property.

Code: 
Range(Sheet1.Cells(z, 1), Sheet1.Cells(z, 18)).Value = Range(Sheet2.Cells(y, 1), Sheet2.Cells(y, 18)).Value

Or

Code: 
With Range(Sheet1.Cells(z,1), Sheet1.Cells(z,18))
    .Value = Sheet2.Range(.Address).Offset(y-z,0).Value
End With
 
Upvote 0
Thanks for that Richard

Perhaps it's a little more complicated than I thought, no wonder it's hurting my head.

I was hoping to compare the contents of "one" cell in each row and based based on that outcome paste all or part of that row to a designated location.

I didn't realise that I had trespassed into, arrays.

Can you see anyway forward for me?

Mike
 
Upvote 0
Which cell is it that you want to compare from sheet1 to sheet2? Will they be in the same column on each sheet (and always a static column eg column A or will the column change depending on ....what)?

What range do you want to copy if the conditions match and to where?
 
Upvote 0
Well in the final version the unique data that i'll be checking will always be in the 14th column ie "N"

It will be the same on both sheets.

If the unique number matches it will copy the first 18 columns of the row and overwrite the first 18 columns.

If it dosen't match it adds it to the database in the last row.

Both sheets have the same headers and all columns share same type data (sheets very similar) ie one is used to update the other and I end up with one sheet of upto date unique info.

Hope that helps, hanks for your time.
 
Upvote 0
Hi Mikerickson

your line

With Range(Sheet1.Cells(z,1), Sheet1.Cells(z,18))
.Value = Sheet2.Range(.Address).Offset(y-z,0).Value
End With

Seems to work very well, I'm not sure I fully understand it bu I'll play with it and see if I can get my head around it.

Thanks very much, for your help, I may be back it's a big project and i'm a novice.
 
Upvote 0
Your data is going from sheet2 row y to Sheet1 row z. That generates the .Offset value.

Code: 
With oneRange
    Sheet3.Range(.Address)
End With
returns the same range as oneRange, but on Sheet3.
 
Upvote 0
Hi there Mike 54!
I wonder if it would be possible to post the final code as I think it could answer all my problems!
I too would like to "overwrite specific columns if another column matches else paste a new record/entire row if no match is found" as stated in thread:
http://www.mrexcel.com/forum/showthread.php?t=325316
However, I am not able to fully understand the replies and code corrections enough to tweak your original code to my situation.
I would be deeply grateful for this if you possibly can!
Thanks in advance!
Ruth
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Question
VBA Questions - Copying dynamic data from one tab to another and deleting filtered rows
Replies
1
Views
184
DanteAmor
DanteAmor
C
  • Question
Daily Run data (copy data in loop by daily in worksheet)
Replies
3
Views
158
dermie_72
D
J
  • Question
VBA to select partial filename and go back and forth between two files to copy paste
Replies
1
Views
166
shknbk2
S
B
  • Solved
Getting Macros to run automatically
Replies
3
Views
207
mumps
mumps
L
  • Solved
VBA remove leading zeros
2
Replies
14
Views
961
Joe4
Joe4

Forum statistics

Threads
1,214,918
Messages
6,122,257
Members
449,075
Latest member
staticfluids

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