VBA Copy range

edgarsrom

New Member
Joined
May 7, 2014
Messages
34
Office Version
  1. 2013
Hello,

I require some help on modifying below code to be able to copy range from one workbook into another workbook only into blank cells:

Code:
Sub CopyRangeToRange()
    Dim CpyFrom As Range
    Dim Cell As Range
    
    Set CpyFrom = ActiveSheet.Range("A:A")


    For Each Cell In CpyFrom
        If Cell.Value <> vbNullString Then
            Cell.Offset(0, 2).Value = Cell.Value
        End If
    Next Cell
End Sub

So this is working if I need to paste range in the same worksheet - into column C, but I require to paste it into different workbook and for Range("A:O").

Any help much appreciated.
 

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,975
Office Version
  1. 365
Platform
  1. Windows
Your question is a little vague. Can you walk us through an actual example?
Explain what you data looks like, what we are copying, and how we can determine exactly where to copy it to.
 

edgarsrom

New Member
Joined
May 7, 2014
Messages
34
Office Version
  1. 2013
Thanks for your reply Joe4,

for example I have some data in workbook Book1 - worksheet Log, which is gathered from a user through userform. So in my range A:O I have fields like Vehicle reg, make, model, year, Fuel type etc.etc. This Log is static and is filled through userform only. Next thing I am trying to do is to copy all data(Range A:O) from Log into new workbook i.e. Book2 - worksheet Analysis(I will need to perform this task every week to get fresh information). Finally, where I am stuck, while I am copying data from Log into Analysis it will overwrite data obviously and paste fresh data into same range. On Analysis sheet tab I require to fill some data manually(missing information from Log) for example on Log sheet I have Make+Model fields completed and Year+Fuel type left blank. So I would manually fill in Year & Fuel type fields, but next time when I paste my data from Log it would overwrite information on my Analysis tab.

I am trying to copy data from Log and paste it into Analysis tab, paste only into blank fields.

Above code I have attached to my thread will do that but for column C in the same worksheet, but I need it in Book2 unfortunately.
 
Last edited:

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,975
Office Version
  1. 365
Platform
  1. Windows
So let's see if understand this:

You want to copy ALL of the data from Book1 to the bottom of the existing data in Book2. Is that correct?
If so, a few details:
- Does the data in Book1 include a header/title row in row 1 that you do NOT want to copy over to Book2?
- For rows with data, will column A always be populated? If so, I think we can use column A on Book2 to find where the existing data ends, so we know where we need to paste the new data to.
 

edgarsrom

New Member
Joined
May 7, 2014
Messages
34
Office Version
  1. 2013

ADVERTISEMENT

Should have explained that particular part better for you, apologies. Book1, worksheet Log range is A:O and has Header row with fields such as Part number, Vehicle Reg, Make, Model, Year, VIN number etc., Book2, worksheet Analysis is same as Log - with same range, same header fields. I would like to copy entire range A:O from Log into Analysis, but the problem is that if I have updated some cells manually within that range in sheet Analysis(which do not exist is Log tab), obviously they will be replaced with blank cells after I paste data.

If I would have a better knowledge of VBA, I could do it other way i.e. add aditional column with ID on both spread sheets and force VBA code to count/find last copied/pasted entry on Analysis tab and let it understand from which ID on Log sheet tab it needs to copy next time. Another problem could appear that way if someone deleted one row, it would mess it up as well.
 
Last edited:

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,975
Office Version
  1. 365
Platform
  1. Windows
Still not entirely clear, especially this part:
I would like to copy entire range A:O from Log into Analysis, but the problem is that if I have updated some cells manually within that range in sheet Analysis(which do not exist is Log tab), obviously they will be replaced with blank cells after I paste data
If we are copying over all the data from Log to Analysis, but pasting it underneath all the data that already exists in Analysis, how would it replace anything (we are just adding to the bottom, not overwriting anything).

Are you trying to do matching form one to the other?

Somehow, I think this would make a lot more sense if we could see what you were trying to do (before and after images). You cannot upload files to this site. But there are tools you can use to post screen images. They are listed in Section B of this link here: http://www.mrexcel.com/forum/board-a...forum-use.html. Also, there is a Test Here forum on this board that you can use to test out these tools to make sure they are working correctly before using them in your question.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,975
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Copied from PM:
Hello, with regards to my thread posted "VBA copy range". Sorry for confusion(even my IT dept. did not get it until 3rd attempt and could not resolve my issue). What I am trying to do is: if you imagine Book1 - sheet Log, Book2 - sheet Analysis:

Book1 - Log: Book2 - Analysis:

Col A: Col B: Col C: Col A: Col B: Col C:
Make: Model: Year: Make: Model: Year:
Audi A6 2010 Audi A6 2010
VW Bora VW Bora 2009(ADDED MANUALLY BY MYSELF)
BMW M3 2011 BMW M3 2011
Fiat Punto 2015

So on Log 2009 was missing, but all other data is copied, then I added in book2 2009 manually, as during inquiry my colleague did not get year of VW Bora for example(but I found it by VIN number lets say). So for this example I used range A:C on Log sheet and analysis sheet as well. For example by next week he will add another 15 inquiries through userform into Log sheet. Is there a way I can copy again range A:C but keep 2009(what I addded manually)?

As I mentioned in one of my posts, I could add a column at the front with ID and then search what is last ID on Analysis i.e. in this case would be 3(as there are 3 records only), then find on Log ID 3 and copy everything after it, so would be ID 4 in this case. I had this idea in my mind but I am not clever enough ti write VBA code for it. Or is there any other option?

Thank you very much in advance!!!
Please keep the discussion in this thread, and not via PM (per rule 4 here: https://www.mrexcel.com/forum/board-announcements/99490-forum-rules.html).

So it sounds like you may be making updates on your Analysis sheet, and don't want those overwritten.
So why not just copy over the NEW records from the Log sheet each time, and leave the old entries alone (so you don't overwrite anything)?
Whatever process you have that writes the records to the Log file, can it also write them to the Analysis file at the same time?
 

edgarsrom

New Member
Joined
May 7, 2014
Messages
34
Office Version
  1. 2013
I did not want to copy manually as spreadsheet will be used by multiple people, so I would like to place a button to import everything from Log sheet automatically. I think I found an answer to my own question in another thread:

https://www.mrexcel.com/forum/excel-questions/599369-vba-copy-paste-last-row.html

I will try to set up sheet "Analysis" to count till last used row in column A and then somehow use that variable to copy from sheet "Log" i.e. if in sheet "Analysis" last used row in column A is 20 then(I think) I need offset(1) to move on A21 and somehow use it to copy from "Log" A21.

If you could help me to write VBA code for it, your help will be much appreaciated. I think that something like this to start with will work:
Code:
 Dim lr As Long
 lr = Range("A" & Rows.Count).End(xlUp).Row
Thanks for help!
 

edgarsrom

New Member
Joined
May 7, 2014
Messages
34
Office Version
  1. 2013
Another option, if I add ID column on both worksheets, is there a way to go through each ID in one worksheet and loop through in another sheet, then copy entire row if not found?

i.e. if I have in the first Sheet(Log), column A with ID numbers: 1,2,3,4,5 and on second sheet I have ID numbers: 1,2,3 then copy entire rows with ID's 4&5 and paste into first blank cell
 
Last edited:

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,975
Office Version
  1. 365
Platform
  1. Windows
OK. If you put this code in your Analysis workbook, I think it will do what you want:
Code:
Sub GetNewData()

    Dim anWB As Workbook
    Dim logWB As Workbook
    Dim anlr As Long
    Dim loglr As Long
    Dim anmax As Long
    Dim logmax As Long
    Dim logstart As Long
    
    Application.ScreenUpdating = False
    
'   Capture activework (Analysis workbook)
    Set anWB = ActiveWorkbook
    
'   Find last row in column A of Analysis workbook
    anlr = Cells(Rows.Count, "A").End(xlUp).Row
    
'   Find maximum ID in column A of Analysis workbook
    anmax = Application.WorksheetFunction.Max(Range("A:A"))
    
'   Go to Log workbook
    Windows("Log.xlsx").Activate

'   Capture activeworkbook (Log workbook)
    Set logWB = ActiveWorkbook
    
'   Find last row in column A of Analysis workbook
    loglr = Cells(Rows.Count, "A").End(xlUp).Row
    
'   Find maximum ID in column A of Analysis workbook
    logmax = Application.WorksheetFunction.Max(Range("A:A"))

'   Find row to start copy from
    logstart = Columns("A:A").Find(What:=anmax, After:=Range("A1"), LookIn:=xlValues, LookAt:= _
        xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False).Row + 1

'   Copy data from Log to Analysis
    If logstart <= loglr Then
        Range(Cells(logstart, "A"), Cells(loglr, "O")).Copy
        anWB.Activate
        Cells(anlr + 1, "A").Select
        ActiveSheet.Paste
        Application.CutCopyMode = False
    Else
        MsgBox "No new rows to copy"
    End If
    
    Application.ScreenUpdating = True
    
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,123,525
Messages
5,602,177
Members
414,510
Latest member
mande358

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
Top