Solved: copy only used rows to another sheet

[gringo287]

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.


[VBA]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[/VBA]

[p45cal]

perhaps change:
[VBA]Set SourceRange = ThisWorkbook.Sheets("Raw Data").Range("B4:J10000")
[/VBA]to:
[VBA]With ThisWorkbook.sheets("Raw Data")
Set SourceRange = Intersect(.UsedRange, .Range(Range("B4"), .Cells(Rows.Count, "J")))
End With[/VBA]
I don't think you need:
[VBA]With SourceRange
Set DestRange = DestRange.Resize(.Rows.Count, .Columns.Count)
End With [/VBA]

[gringo287]

Thanks p45cal, I'll have a look at that tmo. I have to be honest and admit that I am have a terrible habit of panicking what I come across a problem and then figuring it out after posting a plee on here. saying that though, although I've sussed my initial query, it has opened up another really annoying issue with warning messages (around 5 different ones) that come up when sending the data to the other workbook. It works fine on my home pc but the warnings come up when trying to use it at work. It's most annoying because it does work once I've "ok'd" on the warning msg's. I'm have a look at your suggestions tmo as I'm sure they will at least speed my macro up and I will try and post the warning msg's tmo to get an idea for what they mean.

[p45cal]

whilst reading your reply I note I made an error, the line of code should read (two red dots added):[vba]Set SourceRange = Intersect(.UsedRange, .Range(.Range("B4"), .Cells(.Rows.Count, "J")))[/vba]

[gringo287]

ok, ive establised that the warnings are happening because my macro is copying the data validation over to the new sheet and thus replicating the formulas (or more importantly the "defined names" for the formulas). as both sheets end up with duplicate named ranges, it throws out these warnings. Can anyone advise me how to edit the macro above to prevent the dv being copied.

[p45cal]

instead of:
[VBA]SourceRange.Copy DestRange[/VBA]
try:
[VBA]SourceRange.copy
DestRange.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False 'copies values and number formats only.
'DestRange.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False 'include if you want to copy cell formats too.[/VBA]

If it's pure values only (no formatting of any kind) then keep your:
[VBA]With SourceRange
Set DestRange = DestRange.Resize(.Rows.Count, .Columns.Count)
End With[/VBA]
and have after it:
[VBA]DestRange.value = SourceRange.value[/VBA]
it will be faster.

[gringo287]

P45cal, you are a gentleman. worked a treat, thank you.

[Aussiebear]

P45cal, you are a gentleman.

We sort of knew that!