0

The macro below works for me most of the time, but making the clear contents and copy/paste ranges dynamic instead of stagnant would be ideal. I tried to accomplish this using a named range although couldn't get it to work. Can anyone please help me figure out how to update the A10:MH1000 and A1:MH1009 to a dynamic range? Any help would be greatly appreciated!


Sub Update_Tabs()

Dim w As Workbook
Dim x As Workbook

Dim vals As Variant

Set w = Workbooks.Open("C:\Workbookw.xlsx")
Set x = Workbooks.Open("G:\Workbookx.xlsx")


w.Sheets("Data").Range("A10:MH1000").ClearContents
vals = x.Sheets("Data").Range("A1:MH1009").Value
w.Sheets("Data").Range("A10:MH1000").Value = vals

w.Close
x.Close

End Sub
Bobby
  • 1
  • 1
    Is `1009` the last row with data on the Data sheet? – BigBen Jul 10 '23 at 16:14
  • 1
    See [HERE](https://stackoverflow.com/questions/11169445/find-last-used-cell-in-excel-vba), for examples on how to find the last row with data and how to use that information. – Scott Craner Jul 10 '23 at 16:16
  • The data is usually between 600-800 rows, so I did 1009 just to be safe. In the future these ranges will likely be significantly different and I won't know how many rows the data sheet will have. – Bobby Jul 10 '23 at 16:17
  • 1
    `With w.Worksheets("Data")`,`Dim last Row As Long`, `lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row`, `.Range("A10:MH" & lastRow).ClearContents`, `End With`, and similarly for the Data sheet in `x`. – BigBen Jul 10 '23 at 16:19

0 Answers0