Record Macro to Copy / Paste Dynamic Ranges

Hi Steve,

If everything is manually entered, on both HIST and WIP you could hide row 2, blank with no data but with the same formatting as the rows below 2, and then setup both HIST and WIP with macros to insert new rows below row 2 for you to enter your data into for each new record. Then, you create a named range for each file, A2 through 1 row past the last row with data through Column O. Use those named ranges in your code instead of cell ranges.

As long as you don't delete the row below the last row of data, the ranges will stay in place and automatically grow and shrink.

Your code could look something like this, which isn't much different at all:

Sub GetData()
'
' GetData Macro
'

'
    Range("WIP[WC]").Select
    ChDir "C:\Users\Steve\Desktop\Test folder"
    Workbooks.Open Filename:="C:\Users\Steve\Desktop\Test folder\HIST.xlsx"
 '   
    Range("HistRange").Select
 '   
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Windows("Morning Prod Calc (v1.5a).xlsx").Activate
    ActiveSheet.Paste
    Workbooks.Open Filename:="C:\Users\Steve\Desktop\Test folder\WIP.xlsx"
    Range(Selection, Cells(ActiveCell.Row, 1)).Select
 '   
    Range("WipRange").Select
 '   
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Morning Prod Calc (v1.5a).xlsx").Activate
    Range("A2").Select
    Selection.End(xlDown).Select
    Range("A12").Select
    ActiveSheet.Paste
    Cells.Select
    Cells.EntireColumn.AutoFit
    Range("A2").Select
End Sub

Hi Steve,

Ok, so they aren't being populated manually... How are they being populated from your main database?

The 2nd row being hidden allows you to use it as the beginning of your range but is never used other than to be able to insert new records under it so the range is dynamic and includes all rows that are added. You have to also include one row under the last row with data for it to remain dynamic.

Hi Steve N

The HIST File you posted has Data in Rows 2 through 6, empty Rows 7 through 15 and more Data in Rows 16 through 20.

Is this what your actual file looks like? Data then empty Rows then more Data?

Hi Steve

Essentially you need to Resize the Table...this Code does that and appears to do as you require

Option Explicit

Sub GetData()
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim LR As Long, LR1 As Long
    Set wb = ThisWorkbook
    Set ws = wb.Sheets("NALCOMIS_Data")

    With ws
        LR = .Range("A" & .Rows.Count).End(xlUp).Row
        .Range("A2:O" & LR).ClearContents
        If LR > 2 Then
            With .Range("P3:U" & LR)
                .ClearContents
                .Interior.TintAndShade = 0
                .Interior.Pattern = xlNone
            End With
        End If
    End With

    With ws.ListObjects("WIP")
        .Resize Range("$A$1:$U$2")
    End With
    Application.ScreenUpdating = False
    '    ChDir "C:\Documents and Settings\Administrator\Desktop\Steve N"
    ChDir "C:\Users\Steve\Desktop\Test folder"

    '    Workbooks.Open Filename:="C:\Documents and Settings\Administrator\Desktop\Steve N\HIST.xlsx"
    Workbooks.Open Filename:="C:\Users\Steve\Desktop\Test folder\HIST.xlsx"

    With Sheets("NALCOMIS_Data")
        LR1 = .Cells.Find("*", .Cells(.Rows.Count, .Columns.Count), SearchOrder:=xlByRows, _
                SearchDirection:=xlPrevious).Row
        .Range("A2:O" & LR1).Copy

        ws.Range("A2").PasteSpecial
    End With
    ActiveWorkbook.Close , False

    '    Workbooks.Open Filename:="C:\Documents and Settings\Administrator\Desktop\Steve N\WIP.xlsx"
    Workbooks.Open Filename:="C:\Users\Steve\Desktop\Test folder\WIP.xlsx"

    With Sheets("NALCOMIS_Data")
        LR1 = .Cells.Find("*", .Cells(.Rows.Count, .Columns.Count), SearchOrder:=xlByRows, _
                SearchDirection:=xlPrevious).Row
        .Range("A2:O" & LR1).Copy
        With ws
            LR = .Range("A" & .Rows.Count).End(xlUp).Row + 1
            ws.Range("A" & LR).PasteSpecial
        End With
    End With
    ActiveWorkbook.Close , False

    With ws
        .Cells.EntireColumn.AutoFit
        .Range("A2").Select
    End With
    Application.ScreenUpdating = True
End Sub

Hi Steve

Now, when I put this into the master file on another computer with different folder names, should I be changing any of the code to make it work?

Probably...if you wish it to work.

Hi Steve

Good for you...need help let me know...

Hi Steve

In this Code I've indicated the Lines of Code I believe need to be changed in order for the Code to run in your environment. ALL of the Code should be in a General Module. I can't test this as I'm not in Your Environment.

Copy the Code then Alt + F11 to get to VBA --> Insert --> Module --> Paste the Code in the Right Hand Window ---> Save the File

Option Explicit

Sub GetData()
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim LR As Long, LR1 As Long
    Set wb = ThisWorkbook
    Set ws = wb.Sheets("NALCOMIS_Data")

    With ws
        LR = .Range("A" & .Rows.Count).End(xlUp).Row
        .Range("A2:O" & LR).ClearContents
        If LR > 2 Then
            With .Range("P3:U" & LR)
                .ClearContents
                .Interior.TintAndShade = 0
                .Interior.Pattern = xlNone
            End With
        End If
    End With

    With ws.ListObjects("WIP")
        .Resize Range("$A$1:$U$2")
    End With
    Application.ScreenUpdating = False
    
    '##### This is the Testing Environment #####
    '    ChDir "C:\Documents and Settings\Administrator\Desktop\Steve N" '<---This is MY Environment for MY Testing
'    ChDir "C:\Users\Steve\Desktop\Test folder" '<---This is the Environment of your Sample File per the Code
    '##### --------------------------------####
    
    'Based on what you've posted you'll probably need to change the above Testing Environment to your Actual Environment
    ChDir "S:\WHDB\FRCNW\020 PC\Morning Production Report\2013 Morning Production Report"
       
    
    '##### This is the Testing Environment #####
    '    Workbooks.Open Filename:="C:\Documents and Settings\Administrator\Desktop\Steve N\HIST.xlsx" '<---This is MY Environment for MY Testing
'    Workbooks.Open Filename:="C:\Users\Steve\Desktop\Test folder\HIST.xlsx" '<---This is the Environment of your Sample File per the Code
    '##### --------------------------------####
    
    'Based on what you've posted you'll probably need to change the above Testing Environment to your Actual Environment
    Workbooks.Open Filename:="S:\WHDB\FRCNW\020 PC\Morning Production Report\2013 Morning Production Report\HIST.xlsx"
    
    With Sheets("NALCOMIS_Data")
        LR1 = .Cells.Find("*", .Cells(.Rows.Count, .Columns.Count), SearchOrder:=xlByRows, _
                SearchDirection:=xlPrevious).Row
        .Range("A2:O" & LR1).Copy

        ws.Range("A2").PasteSpecial
    End With
    ActiveWorkbook.Close , False

    '##### This is the Testing Environment #####
    '    Workbooks.Open Filename:="C:\Documents and Settings\Administrator\Desktop\Steve N\WIP.xlsx" '<---This is MY Environment for MY Testing
'    Workbooks.Open Filename:="C:\Users\Steve\Desktop\Test folder\WIP.xlsx" '<---This is the Environment of your Sample File per the Code
     '##### --------------------------------####
     
     'Based on what you've posted you'll probably need to change the above Testing Environment to your Actual Environment
    Workbooks.Open Filename:="S:\WHDB\FRCNW\020 PC\Morning Production Report\2013 Morning Production Report\WIP.xlsx"

    With Sheets("NALCOMIS_Data")
        LR1 = .Cells.Find("*", .Cells(.Rows.Count, .Columns.Count), SearchOrder:=xlByRows, _
                SearchDirection:=xlPrevious).Row
        .Range("A2:O" & LR1).Copy
        With ws
            LR = .Range("A" & .Rows.Count).End(xlUp).Row + 1
            ws.Range("A" & LR).PasteSpecial
        End With
    End With
    ActiveWorkbook.Close , False

    With ws
        .Cells.EntireColumn.AutoFit
        .Range("A2").Select
    End With
    Application.ScreenUpdating = True
End Sub

Regarding this

I copied and pasted the code into a module in the VBE of the wb I'm using. When I attempt to run it it stops at ~line 20

Is the the Table Structure of the Workbook your using the SAME STRUCTURE as that of your Sample File Table? If yes then I can't troubleshoot this issue without seeing the actual Workbook you're using.