Copy Paste Data

[cotech_10]

Hi All,

I am looking to copy contents from one spreadsheet into another.

In this example I have a folder location DATA_1 which contains a number of spreadsheets which have filename system that is date related. As per
the image below

The spreadsheets in the DATA_1 folder is the source of Data for transfer into my main Data spreadsheet named Main.xlsx

What I want to achieve is to select a Date from a listing in Main.xlsx located in the TAB "Main" as per below :

So in this example I have selected the Date 01/01/2023 to transfer data from the Date Selection Listing :


2.

In each of the data files there is a TAB labelled Sheet0 which contains the data to be copied in which occupies cells references below

34 columns wide	49 Rows deep
Cells A49 - AH49

The contents are then Pasted into the spreadsheet MAIN.xlsx in the TAB labelled DATATRANSFER



I look forward to hearing back from someone..




Regards

 

[Wookiee]

This will require some customization I imagine, but it did the trick for me.

Sub CoTech10()
'Crafted by Wookiee at MrExcel.com


Dim strImport As String
Dim rngCopy As Range
Dim rngPaste As Range
Dim wkbImport As Workbook
Dim wkbMain As Workbook
Dim wksCopy As Worksheet
Dim wksMain As Worksheet
Dim wksPaste As Worksheet


Const STRPATH As String = "D:\DATA_1\"

Set wkbMain = ThisWorkbook
Set wksMain = wkbMain.Sheets("Main")
Set wksPaste = wkbMain.Sheets("DATATRANSFER")

'Get File Date From Active Cell
With wkbMain

  With wksMain
  
    With ActiveCell

      strImport = VBA.Format(.Value, "MM_DD_YYYY") & ".xlsx"

    End With

  End With

  'Set Paste Range
  With wksPaste
      
    Set rngPaste = .Range("C3")

  End With

End With

'Open Dated File
Set wkbImport = Workbooks.Open _
  (Filename:=STRPATH & strImport, ReadOnly:=True)

'Get Data Range To Copy
Set wksCopy = wkbImport.Sheets("Sheet0")
Set rngCopy = wksCopy.Range("A1:AH49")

'Paste To Main File And Close Imported File
rngCopy.Copy rngPaste

wkbImport.Close SaveChanges:=False


End Sub

 

[cotech_10]

Sub CoTech10() 'Crafted by Wookiee at MrExcel.com Dim strImport As String Dim rngCopy As Range Dim rngPaste As Range Dim wkbImport As Workbook Dim wkbMain As Workbook Dim wksCopy As Worksheet Dim wksMain As Worksheet Dim wksPaste As Worksheet Const STRPATH As String = "D:\DATA_1\" Set wkbMain = ThisWorkbook Set wksMain = wkbMain.Sheets("Main") Set wksPaste = wkbMain.Sheets("DATATRANSFER") 'Get File Date From Active Cell With wkbMain With wksMain With ActiveCell strImport = VBA.Format(.Value, "MM_DD_YYYY") & ".xlsx" End With End With 'Set Paste Range With wksPaste Set rngPaste = .Range("C3") End With End With 'Open Dated File Set wkbImport = Workbooks.Open _ (Filename:=STRPATH & strImport, ReadOnly:=True) 'Get Data Range To Copy Set wksCopy = wkbImport.Sheets("Sheet0") Set rngCopy = wksCopy.Range("A1:AH49") 'Paste To Main File And Close Imported File rngCopy.Copy rngPaste wkbImport.Close SaveChanges:=False End Sub

Hi Wookiee,

Thank you for your response, it is greatly appreciated.

The code you have provided certainly does what I want it to do....