PDA

View Full Version : Solved: copy only used rows to another sheet



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

p45cal
06-29-2012, 02:28 PM
perhaps change:
Set SourceRange = ThisWorkbook.Sheets("Raw Data").Range("B4:J10000")
to:
With ThisWorkbook.sheets("Raw Data")
Set SourceRange = Intersect(.UsedRange, .Range(Range("B4"), .Cells(Rows.Count, "J")))
End With
I don't think you need:
With SourceRange
Set DestRange = DestRange.Resize(.Rows.Count, .Columns.Count)
End With

gringo287
06-29-2012, 03:03 PM
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
06-29-2012, 03:18 PM
whilst reading your reply I note I made an error, the line of code should read (two red dots added):Set SourceRange = Intersect(.UsedRange, .Range(.Range("B4"), .Cells(.Rows.Count, "J")))

gringo287
06-30-2012, 09:58 AM
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
06-30-2012, 01:36 PM
instead of:
SourceRange.Copy DestRange
try:
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.

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

gringo287
07-01-2012, 02:23 AM
P45cal, you are a gentleman. worked a treat, thank you.

Aussiebear
07-01-2012, 03:15 PM
P45cal, you are a gentleman.

:devil2: We sort of knew that!