G
GEdwards
I am using excel 2003.
I have numerous ranges on 1 worksheet that I want to copy over to a new
workbook and worksheet. Is it possible to have multiple source ranges and
multiple destination ranges coded within a few lines of code? I am referring
to the lines in the code below of ,
"Set sourceRange = ..." and "Set destRange = DestSH ..."
I have coded a working macro to do this in numerous iterations to complete
the destination worksheet, but the macro is very long and code is duplicated
many times. I would like a simpler way to do this. I am new to all this
stuff and attempted something different (please hold the laughter).
Here is the part of the code I have attempted to which I receive "Run-time
error '13', Type mismatch":
Set sourceRange = ThisWorkbook.Sheets("Estimate").Range("B3:B7") &
Range("B20:B20") & Range("I5:I5") & Range("K4:K4")
'Sheet name of the destination, Invoice worksheet
Set DestSH = DestWB.Worksheets("Invoice")
DestWB.Activate
Set destRange = DestSH.Range("B3:B7") & DestSH.Range("B20:B20") &
DestSH.Range("I5:I5") & DestSH.Range("K4:K4")
With sourceRange
Set destRange = destRange.Resize(.Rows.Count, .Columns.Count)
End With
destRange.Value = sourceRange.Value '<== Write the new values to the
destination sheet
Suggestions?
I have numerous ranges on 1 worksheet that I want to copy over to a new
workbook and worksheet. Is it possible to have multiple source ranges and
multiple destination ranges coded within a few lines of code? I am referring
to the lines in the code below of ,
"Set sourceRange = ..." and "Set destRange = DestSH ..."
I have coded a working macro to do this in numerous iterations to complete
the destination worksheet, but the macro is very long and code is duplicated
many times. I would like a simpler way to do this. I am new to all this
stuff and attempted something different (please hold the laughter).
Here is the part of the code I have attempted to which I receive "Run-time
error '13', Type mismatch":
Set sourceRange = ThisWorkbook.Sheets("Estimate").Range("B3:B7") &
Range("B20:B20") & Range("I5:I5") & Range("K4:K4")
'Sheet name of the destination, Invoice worksheet
Set DestSH = DestWB.Worksheets("Invoice")
DestWB.Activate
Set destRange = DestSH.Range("B3:B7") & DestSH.Range("B20:B20") &
DestSH.Range("I5:I5") & DestSH.Range("K4:K4")
With sourceRange
Set destRange = destRange.Resize(.Rows.Count, .Columns.Count)
End With
destRange.Value = sourceRange.Value '<== Write the new values to the
destination sheet
Suggestions?