VBA Loop Copy and Paste with offset

[taugdog21]

I have been having trouble trying to get this macro to run I am not very familiar with Visual Basic Syntax can anyone help me. I'm guessing that my declarations are wrong because I get runtime errors with my declared ranges.


Sub Macro5()
'
' Macro5 Macro
'
Dim i As Range, j As Range, k As Range
Dim x As Range, y As Range
Dim Num As Integer

Num = 94
Set y = Range("A4:A9")
Set x = Range("B1:G10")
Set j = Range("C4")
Set i = Range("B4")
Set k = Range("A4")

Do
Num = Num - 1
Sheets("Sum Data").Select
x.Select
Selection.Copy
Sheets("PNA Physical Needs Summary Data").Activate
j.Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
x = x.Offset(10, 0)
j = j.Offset(5, 0)

Range("P3:P8").Select
Application.CutCopyMode = False
Selection.Copy
i.Select
Selection.Paste
i = i.Offset(5, 0)

Sheets("PNA Project Data").Select
k.Select
Application.CutCopyMode = False
Selection.Copy
Sheets("PNA Physical Needs Data").Select
k.Select
ActiveSheet.Paste
k = k.Offset(1, 0)

Application.CutCopyMode = False
Selection.AutoFill Destination:=y, Type:=xlFillDefault
y.Select
y = y.Offset(5, 0)
Loop Until Num = 0
End Sub

 

[Asala42]

I think you need to reference your sheet names when you define your ranges.

eg Set j = Sheets("PNA etc").Range("C4") instead of Set j = Range("C4").

 

[taugdog21]

Thanks I am just having trouble now with getting the increment right when it loops I used offsets in the loop but it is not copying from different cells a I just get a bunch of blanks when i loop it more than once


Dim i As Range, j As Range, k As Range
Dim x As Range, y As Range
Dim Num As Integer, rownum As Integer

Num = 94

Set x = Sheets("Sum Data").Range("B1:G10")
Set j = Sheets("PNA Physical Needs Summary Data").Range("C4:L9")
Set i = Sheets("PNA Physical Needs Summary Data").Range("B4:B9")
Set k = Sheets("Sum Data").Range("A1")
Set p = Sheets("PNA Physical Needs Summary Data").Range("P3:P8")
Set e = Sheets("PNA Physical Needs Summary Data").Range("A4:A9")

Do
x.Copy
Sheets("PNA Physical Needs Summary Data").Select
j.Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True

p.Copy i

k.Copy e

Num = Num - 1

x = x.Offset(10,0)
j = j.Offset(6,0)
i = i.Offset(6,0)
k = k.Offset(10,0)
e = e.Offset(6,0)

Loop Until Num = 0
End Sub

 

[Asala42]

You should check your other active threads.

http://www.mrexcel.com/forum/showthread.php?t=603564