How to copy all data from 1 sheet to another?

[danbates]

Hi,

Please can someone help me?

What I would like is to be able to copy all the data from the current week sheet and then copy it to my data sheet via VBA code.

I have 53 sheets on my workbook. 1 sheet for each week of the year and then my data sheet.

The data will not be the same amount each week. Some weeks it could be 100 rows and 10 columns and another week it could be 80 rows and 15 columns.

I have formulas in the week sheets so when it comes to pasting it I would like just the values to be pasted please.

Any help would be much appreciated.

Thanks

Dan

 

[DanteAmor]

I have some doubts:

Do you want to copy the 52 sheets on a destination sheet?
How will the copy be? sheet1 up, down sheet1 sheet2 and so on?
Which columns do you want to copy?
In which row should the copy start?

 

[danbates]

Hi,

I just want to copy 1 sheet per week and the sheet number depends on which week number it is. eg this week is week 39 so it would be sheet39.

The copy needs to start from A1 and then copy all the data on the sheet and then paste it to the data sheet. The amount of rows and columns will vary each week.

I have managed to put the following code together so far but I can't figure out how to find the last column of data. In my code I have just set it to column M.

Option Explicit

Sub COPY_ROW_COLUMN()


Dim LastRow As Long
Dim LastCol As Integer


Sheets("DATA").Cells.ClearContents


With ActiveSheet
    
        LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Select


[COLOR=#ff0000]        LastRow = .Cells(.Rows.Count, "M").End(xlUp).Select[/COLOR]


        Range(Cells(1, 1), ActiveCell).Select


        Selection.Copy


        Range("A1").Select


End With


    Sheets("DATA").Select


    Range("A1").Select


    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False


    Range("A1").Select


End Sub

If you could help me with that then I'd appreciate it.

Thanks

Dan

 

[DanteAmor]

You didn't answer some of my questions, so I'm going to try to guess, if you have 39 sheets, then you want to copy sheet1, sheet2 to sheet39 on the "data" sheet.
Try this:

Sub copy_all_data()
  Dim sh As Worksheet, s As Worksheet
  Application.ScreenUpdating = False
  Set sh = Sheets("[COLOR=#0000ff]Data[/COLOR]")
  sh.Cells.ClearContents
  For Each s In Sheets
    If s.Name <> sh.Name Then
      s.Rows("1:" & s.UsedRange.Rows(s.UsedRange.Rows.Count).Row).Copy
      sh.Range("A" & sh.UsedRange.Rows(sh.UsedRange.Rows.Count).Row + 1).PasteSpecial xlPasteValues
    End If
  Next
  Application.ScreenUpdating = True
  MsgBox "Done"
End Sub

 

[danbates]

Hi,

Thank you for you code.

I apologise for not supplying you with all the info you wanted but your code is very close to what I needed and I'll be able to amend it to suit my needs.

Thank you for you help, it really is appreciated.

Thanks

Dan

 

[LlebKcir]

Hi,

Thank you for you code.

I apologise for not supplying you with all the info you wanted but your code is very close to what I needed and I'll be able to amend it to suit my needs.

Thank you for you help, it really is appreciated.

Thanks

Dan

Dan, if you can, please post your code so others might be able to use it as an example.

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.