Copy range between open workbooks

[Shadkng]

Hi, I am trying to copy data between 2 open workbooks. In the code below, the target workbook has the path with the named file. Is it possible to have the "set wb2" refer to the target file in the same directory as the source file? I am asking because we have several users on different computers using this macro so it can't refer to a specific user in the path. If not, the other option is not to refer to a specific target file and just refer to the 2nd open workbook. This would be fine too.

Instead of specifying the range as "C24:I500", can you revise it to C24 to last row with data? If the last row part requires that I copy each column on it's own that would be fine. I will be duplicating the copy and paste for many ranges in the sheet once I get this working.

Also, is the below code the written the best way? I guess we are using the clipboard to copy and paste but I guess it would be better to copy and paste directly. But I do need to keep the formatting from the source to the target.

Thanks

Sub copyrange4()
Dim wb1 As Workbook
    Dim wb2 As Workbook
    Set wb1 = ActiveWorkbook

    'Copy what you want from workbook 1.
    wb1.Worksheets("QUOTE").Range("C24:I500").Copy 'Change worksheet

    'Open workbook 2
    Set wb2 = Workbooks.Open("C:\Users\Steve\Desktop\Dropbox\Quotes Steve\QuoteProgram2")
    
    'Paste to worksheet in workbook2:
    Application.DisplayAlerts = False
    wb2.Sheets("QUOTE").Range("C24").PasteSpecial
    Application.CutCopyMode = False
    Range("A1").Select

    Application.DisplayAlerts = True

End Sub

 

[Snakehips]

@Shadkng Not tested but try along these lines.

Sub copyrange4()
Dim wb1 As Workbook
    Dim wb2 As Workbook
    Dim Pth As String
    Dim lr As Long
    
    
    Set wb1 = ActiveWorkbook
    Pth = wb1.Path  'common path
    
'Open workbook 2
    Set wb2 = Workbooks.Open(Pth & "QuoteProgram2")
    
'last row ?  if determinable from column C  ?
lr = wb1.Sheets("QUOTE").Range("C" & Rows.Count).End(xlUp).Row

    'Copy what you want from workbook 1.
    wb1.Worksheets("QUOTE").Range("C24:I" & lr).Copy 'Change worksheet
    
    'Paste to worksheet in workbook2:
    Application.DisplayAlerts = False
    wb2.Sheets("QUOTE").Range("C24").PasteSpecial
    Application.CutCopyMode = False
    Range("A1").Select

    Application.DisplayAlerts = True

End Sub

Hope that helps

 

[Shadkng]

@Shadkng Not tested but try along these lines.

Sub copyrange4()
Dim wb1 As Workbook
    Dim wb2 As Workbook
    Dim Pth As String
    Dim lr As Long
   
   
    Set wb1 = ActiveWorkbook
    Pth = wb1.Path  'common path
   
'Open workbook 2
    Set wb2 = Workbooks.Open(Pth & "QuoteProgram2")
   
'last row ?  if determinable from column C  ?
lr = wb1.Sheets("QUOTE").Range("C" & Rows.Count).End(xlUp).Row

    'Copy what you want from workbook 1.
    wb1.Worksheets("QUOTE").Range("C24:I" & lr).Copy 'Change worksheet
   
    'Paste to worksheet in workbook2:
    Application.DisplayAlerts = False
    wb2.Sheets("QUOTE").Range("C24").PasteSpecial
    Application.CutCopyMode = False
    Range("A1").Select

    Application.DisplayAlerts = True

End Sub

Hope that helps

I'm not clear about this line
Set wb2 = Workbooks.Open(Pth & "QuoteProgram2")

I dont want to specify a path that has my username. Can it point to the 2nd open workbook without specifying a filename?

 

[Shadkng]

@Shadkng Not tested but try along these lines.

Sub copyrange4()
Dim wb1 As Workbook
    Dim wb2 As Workbook
    Dim Pth As String
    Dim lr As Long
   
   
    Set wb1 = ActiveWorkbook
    Pth = wb1.Path  'common path
   
'Open workbook 2
    Set wb2 = Workbooks.Open(Pth & "QuoteProgram2")
   
'last row ?  if determinable from column C  ?
lr = wb1.Sheets("QUOTE").Range("C" & Rows.Count).End(xlUp).Row

    'Copy what you want from workbook 1.
    wb1.Worksheets("QUOTE").Range("C24:I" & lr).Copy 'Change worksheet
   
    'Paste to worksheet in workbook2:
    Application.DisplayAlerts = False
    wb2.Sheets("QUOTE").Range("C24").PasteSpecial
    Application.CutCopyMode = False
    Range("A1").Select

    Application.DisplayAlerts = True

End Sub

Hope that helps

Hi, disregard my previous reply. I tested the code and I got an error that it couldn't find "quoteprogram2" in that location, so I guess the path part is not working.

 

[Shadkng]

Hi, disregard my previous reply. I tested the code and I got an error that it couldn't find "quoteprogram2" in that location, so I guess the path part is not working.

Hi Snakehips, did you get a chance to see my reply?