Data transfer from Activeworkbook to "FileToOpen" workbook as master file

G0dsreaper

New Member
Joined
Jan 14, 2021
Messages
6
Office Version
  1. 2016
  2. 2013
Platform
  1. Windows
I need to transfer data from Activeworkbook.Sheets("Raw Data").Ranges (r1(G6:L6),r2(G12:L12),r3(G18:L18),r4(G24:L24),r5(G30:L30) to another "FileToOpen" workbook. Each range needs to transfer to the next open row of a different sheet in the filetoOpen workbook starting in column "B". i.e. R1 transfers to FileToOpen.sheets("sheet 1")Column B:F. Range 2 transfer to FileToOpen.sheets("sheet2")Column B:F and so on. In Column A I would like the date stamped in when the transfer took place.

The following code is what I have working for Copying and Pasting R1 to sheet 1 of the fileToOpen workbook. Although this just overwrites the first row with new data every time.


VBA Code:
Sub Export_Data_To_File()
Dim FileToOpen As Variant
Dim OpenBook As Workbook
Dim lastRow As Long

Application.ScreenUpdating = False

FileToOpen = "S:\Process Engineering\Team Documents\010 Alpha\Alpha Laser Calibration\Raw Data\Raycus
Laser Decay Comparison.xlsx"
'lastRow = OpenBook.Cells(Rows.Count, "B").End(xlUp).Row + 1

If FileToOpen <> False Then

    Set OpenBook = Application.Workbooks.Open(FileToOpen)
    ThisWorkbook.Worksheets("Raw Data").Range("G6:L6").Copy
    OpenBook.Worksheets("100% Duty Cycle").Range("B3:G3").PasteSpecial xlPasteValues
    OpenBook.Worksheets("100% Duty Cycle").Range("A3") = Format(Now(), "MM-DD-YYYY")
    OpenBook.Save
    OpenBook.Close False
Application.ScreenUpdating = True
  End If
End Sub
 

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

ER_Neha

Board Regular
Joined
Jul 25, 2020
Messages
130
Office Version
  1. 365
Platform
  1. Windows
I modified your code, please try and let me know if it works well now.

VBA Code:
Sub Export_Data_To_File()
Dim FileToOpen As Variant
Dim OpenBook As Workbook
Dim lastRow As Long

Application.ScreenUpdating = False

FileToOpen = "S:\Process Engineering\Team Documents\010 Alpha\Alpha Laser Calibration\Raw Data\Raycus
Laser Decay Comparison.xlsx"
'lastRow = OpenBook.Cells(Rows.Count, "B").End(xlUp).Row + 1

If FileToOpen <> False Then

    Set OpenBook = Application.Workbooks.Open(FileToOpen)
    ThisWorkbook.Worksheets("Raw Data").Range("G6:L6").Copy
    OpenBook.Worksheets("100% Duty Cycle").Range("B" & Cells(Rows.Count, 1).End(xlUp).row : "G"  & Cells(Rows.Count, 1).End(xlUp).row).PasteSpecial xlPasteValues
    OpenBook.Worksheets("100% Duty Cycle").Range("A3") = Format(Now(), "MM-DD-YYYY")
    OpenBook.Save
    OpenBook.Close False
Application.ScreenUpdating = True
  End If
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,127,107
Messages
5,622,780
Members
415,927
Latest member
vedasinternational

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