Need macro for Copying data

Look at adapting this code to your specific situation

Sub CopyOpenItems() 
   ' 
   ' CopyOpenItems Macro 
   ' Copy open items to sheet. 
   ' 
   ' Keyboard Shortcut: Ctrl+Shift+O 
   ' 
   Dim wbTarget            As Workbook 'workbook where the data is to be pasted 
   Dim wbThis              As Workbook 'workbook from where the data is to copied 
   Dim strName             As String   'name of the source sheet/ target workbook 
    
   'set to the current active workbook (the source book) 
   Set wbThis = ActiveWorkbook 
    
   'get the active sheetname of the book 
   strName = ActiveSheet.Name 
    
   'open a workbook that has same name as the sheet name 
   Set wbTarget = Workbooks.Open("C:\filepath\" & strName & ".xlsx") 
    
   'select cell A1 on the target book 
   wbTarget.Range("A1").Select 
    
   'clear existing values form target book 
   wbTarget.Range("A1:M51").ClearContents 

   'activate the source book 
   wbThis.Activate 
    
   'clear any thing on clipboard to maximize available memory 
   Application.CutCopyMode = False 
    
   'copy the range from source book 
   wbThis.Range("A12:M62").Copy 
    
   'paste the data on the target book 
   wbTarget.Range("A1").PasteSpecial 
    
   'clear any thing on clipboard to maximize available memory 
   Application.CutCopyMode = False 
    
   'save the target book 
   wbTarget.Save 
    
   'close the workbook 
   wbTarget.Close 

   'activate the source book again 
   wbThis.Activate 
    
   'clear memory 
   Set wbTarget = Nothing 
   Set wbThis = Nothing 
    
End Sub

Range("A1").End(xlDown).Offset(1).Select

This will move the Activecell to the bottom of the range assuming there is data in every row cell in column A where you already have data. If not, there is some extra steps to make.

This will dynamically select everything except the headers from the from workbook.

Range("A1").CurrentRegion.Select

This will copy all the data but the header:

Range("A1").CurrentRegion.Select
Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1, Selection.Columns.Count).Copy

This will Copy the code in a dynamic region with the first row of data.

Range("A1").CurrentRegion.Copy

Thanks,

Gregg