Little code alteration

[oggy3000]

Hey guys,
I'm not very good at VBA so I am asking for your help.
I want to run the following code everytime my UserForm is loaded.
I know where to put it but I am somehow not able to change it in a way that the cells are copied to the sheet "Data".

Hope you can help me with that.
Following the code:

Option Explicit
Private Sub Worksheet_Activate()
Dim ws As Worksheet
Dim lngLast As Long
Dim rngClear As Range
 
Set rngClear = Me.UsedRange
rngClear.Offset(1, 0).Resize(rngClear.Rows.Count - 1, rngClear.Columns.Count).Clear
Set rngClear = Nothing
 
For Each ws In Worksheets(Array("Inbound", "Outbound"))
   lngLast = ws.Cells(Rows.Count, 1).End(xlUp).Row
   With Me.Cells(Rows.Count, 1).End(xlUp)
      ws.Range("D2:J" & lngLast).Copy Destination:=.Offset(1, 0)
      .Offset(1, 0).Resize(lngLast - 1, 24).Value = _
         ws.Range("D2:J" & lngLast).Value
   End With
Next ws
End Sub

Cheers
Jan

 

[oggy3000]

No one who can help me?

Jan

 

[RSXchin]

try reasking your question in a different way. I don't fully understand what you want changed.

 

[oggy3000]

Hi rsxchin,
at the moment I have the code I posted in the VBA Module of the "Data"-Sheet. So whenever the sheet is activated, the macro is run and the data from the 2 sheets "Inbound" and "Outbound" is copied to the sheet without the headlines.
Now I want to run the macro from a UserForm. But I am not able to change the "Me" stuff to the matching "Data" stuff. That's what I need help with. Hope that answered your question and you can help me

Cheers
Jan

 

[RSXchin]

change your "me"s into

sheets("Data")

tell me where that gets you.

 

[oggy3000]

Hey rsxchin,
it works perfectly! I just dont understand why it didn't work 2 days ago when I was trying it with the same thing...

Anyway, thank you very much

Cheers
Jan

 

[RSXchin]

hooray!!!

 

[oggy3000]

Hey RSXchin,
I have another small problem with the code, hope you can help me out again

The copying works but somehow there are about 200 rows missing. I can only imagine that this happens because some cells in column A are merged. How do I have to change the code in order to make it count from column F and then copy it from D2:F (last row) on sheets "Inbound" and "Outbound" to sheet "Data" A2:G?

Cheers
Jan

 

[RSXchin]

sorry, I don't have a clue. I hate merged cells for that reason.