.usedrange problem

mplees

Active Member
Joined
Feb 6, 2006
Messages
351
Hi,

I am using the following code to copy data from a number of worksheets & then paste into another:

Set SS1data = Worksheets("Cable accessories").UsedRange
Set SS2data = Worksheets("Signal cable").UsedRange
Set Results = Worksheets("Picking list").Range("$A$1")

SS1data.Copy
Results.Cells(1, 1).PasteSpecial

k = SS1data.Rows.Count
MsgBox k
k = k + 1
SS2data.Copy
Results.Cells(k, 1).PasteSpecial

...which does what I need with one problem. On each sheet that I copy from, the first row always contains header information that only needs to be copied once from the first sheet. On subsequent sheets I don't want to copy this data - effectively I want the copied data to start from row 2. Is there a way of defining the usedrange in this instance? Also, how could I handle it if there were no data to copy?

Hope someone can help,

Regards,

Mark
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

venkat1926

Well-known Member
Joined
Aug 21, 2005
Messages
4,824
I I have undersood your query correctly this is the suggestion

quote
Set Results = Worksheets("Picking list").Range("$A$1")
unquote

instead try to use this
Code:
Set Results = Worksheets("Picking list").cells(rows.count,"a").end(xlup).offset(1,0)

the statements will be like this
Code:
SS1data.Copy
Set Results = Worksheets("Picking list").cells(rows.count,"a").end(xlup).offset(1,0)
results. pastespecial
ss2data.copy
Set Results = Worksheets("Picking list").cells(rows.count,"a").end(xlup).offset(1,0)
results.pastespecial


venkat
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Hi,

try something like this
Code:
    With Worksheets("Cable accessories").UsedRange
    Set SS1data = .Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count)
    End With
or
Code:
    With Worksheets("Cable accessories")
    Set SS1data = Intersect(.UsedRange, .Rows("2:" & Rows.Count))
    End With

if you have multiple sheets to process (and even with only two, talking about clarity of code), you would better put the first row in your results and then loop through all other sheets with the same code

kind regards,
Erik
 

mplees

Active Member
Joined
Feb 6, 2006
Messages
351
Hi Venkat,

thanks for your reply. I'm afraid that I didn't explain myself properly (I really should be more specific sometimes!). What I meant to say is that I don't want to copy the first row on the source sheets (i.e "Cable accessories", "Signal cable", etc), because this is header information that is not required. I need to effectively copy the data on these sheets from row 2, & ignore row 1.

Other than that, your code example works really well - any thoughts on how I could do this? also, one other thing that comes to mind. If I wanted to exclude certain rows in my source data (by adding a column that contained either 0 or 1), is it possible to select a non-contiguous range & copy across, or not??

As always, everyones help IS greatly appreciated.

Regards,

Mark
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832

ADVERTISEMENT

when defining the range, add
Code:
.SpecialCells(xlCellTypeVisible)
 

mplees

Active Member
Joined
Feb 6, 2006
Messages
351
Hi erik,

Thank you for the suggestions....I'll try them out now. just got back from Belgium actually (Tielt & Herentals) - it was a good trip!

regards,

Mark
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832

ADVERTISEMENT

Herentals is at 25km from here
you're always welcome :)
 

mplees

Active Member
Joined
Feb 6, 2006
Messages
351
OK,

thanks for all the help so far...I now have my merged list! I still need to carry out one further operation. From the data on my 'Picking list' worksheet, I have a column (J) that contains a value of 0 or 1. What I now need to do is copy only the rows that have a value of 1 in column J to another worksheet ("BOM"), but also I only need the contents of the first 4 cells in each row (A:D). These rows then need to be pasted in the BOM worksheet starting from say cell A10.

Anyone point me in the right direction with this?

Many thanks again,

Mark
 

mplees

Active Member
Joined
Feb 6, 2006
Messages
351
Anyone any thoughts? I was wondering whether this could be achieved with a formula (maybe a LOOKUP?) rather than using VBA, but I just can't see how to approach this.

Any ideas anyone?

Mark
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Hi,

you can use autofilter for this
did you try that ?

filtered list will only copy the visible rows

best regards,
Erik
 

Forum statistics

Threads
1,136,616
Messages
5,676,831
Members
419,653
Latest member
analyticalchemist94

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top