Copy a range, paste to another sheet, go to the next column

[Ghostmyst]

Hello,
I am very new to VBA, I was wondering if anyone can help me with this issue I have.

I want to copy a range (A2:A6) from sheet1 and paste it to a sheet in which the name of it is the same as A1 or sheet1. I want the macro to come back to sheet 1 and repeats on to the next column until it hits a blank cell in row1.

Thanks in advance,
Evelyn

 

[Domski]

Hi,

This do anything like what you want?

Sub Copy_Stuff()

Dim myRange As Range, mySheet As String

Application.ScreenUpdating = False

For Each myRange In Sheets("Sheet1").Range([A1], [A1].End(xlToRight))

    If myRange.Value = "" Then Exit Sub

    mySheet = myRange.Value

    Range(myRange.Offset(1, 0), myRange.Offset(5, 0)).Copy

    Sheets(mySheet).Range("A1").PasteSpecial Paste:=xlValues, Operation:=xlNone _
        , SkipBlanks:=False, Transpose:=False

Next myRange

Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub

Change the destination range as required.

Dom

 

[Ghostmyst]

Thank you!

Just one more question, my range might varies from month to month, so it might not be (A2:A6), it might be (A2:A31). Is there a way to do that?

Thank you so much,
Evelyn

 

[Dylan]

Evelyn,

You can add below code in Dom's code.

Application.ScreenUpdating = False
lastRow = range("A65536").end(xlup).row

For Each myRange In Sheets("Sheet1").Range([A1], [A1].End(xlToRight))

If myRange.Value = "" Then Exit Sub

mySheet = myRange.Value

Range(myRange.Offset(1, 0), myRange.Offset(lastRow-1, 0)).Copy

 

[Ghostmyst]

Thanks so much!