gringo287
06-26-2012, 11:51 AM
Hi,
I have a sheet that contains a mixture of columns that contain mostly dynamic data validation based cells with some that are just cells that the user manually types data into.
I have a macro that works really well when i want to copy a set range to another sheet, but i have no idea how to adapt this to copy the used range. Or even if its possible to copy a mixture of different types of cells in the same row.
Private Sub UpDate_Raw_Click()
Dim SourceRange As Range
Dim DestRange As Range
Dim DestWB As Workbook
Dim DestSh As Worksheet
Dim Lr As Long
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
If bIsBookOpen_RB("file name") Then
Set DestWB = Workbooks("file name")
Else
Set DestWB = Workbooks.Open("file name")
End If
Set SourceRange = ThisWorkbook.Sheets("Raw Data").Range("B4:J10000")
Set DestSh = DestWB.Worksheets("master")
Lr = DestSh.Cells(Rows.Count, "b").End(xlUp).Row
Set DestRange = DestSh.Range("b" & Lr + 1)
With SourceRange
Set DestRange = DestRange.Resize(.Rows.Count, .Columns.Count)
End With
SourceRange.Copy DestRange
DestWB.Activate
DestWB.Close savechanges:=True
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub
I have a sheet that contains a mixture of columns that contain mostly dynamic data validation based cells with some that are just cells that the user manually types data into.
I have a macro that works really well when i want to copy a set range to another sheet, but i have no idea how to adapt this to copy the used range. Or even if its possible to copy a mixture of different types of cells in the same row.
Private Sub UpDate_Raw_Click()
Dim SourceRange As Range
Dim DestRange As Range
Dim DestWB As Workbook
Dim DestSh As Worksheet
Dim Lr As Long
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
If bIsBookOpen_RB("file name") Then
Set DestWB = Workbooks("file name")
Else
Set DestWB = Workbooks.Open("file name")
End If
Set SourceRange = ThisWorkbook.Sheets("Raw Data").Range("B4:J10000")
Set DestSh = DestWB.Worksheets("master")
Lr = DestSh.Cells(Rows.Count, "b").End(xlUp).Row
Set DestRange = DestSh.Range("b" & Lr + 1)
With SourceRange
Set DestRange = DestRange.Resize(.Rows.Count, .Columns.Count)
End With
SourceRange.Copy DestRange
DestWB.Activate
DestWB.Close savechanges:=True
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub