Offset Query

Ampleford

Ampleford

Active Member
Joined
Mar 26, 2002
Messages
380
I would like to copy a range of data, (Range A1:T1) then paste that data a set number of rows down within a table on another tab. That number of rows is found in cell A2

so far I have :-

dim OS as Integer
OS = Sheets("1").Range("A2").Value
Sheets("1").Activate
Range("A1:T1").Copy
Sheets("2").Activate
Range("A1",offset(0,OS)
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False


would this work ?
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
Sort by date Sort by votes
Hi,

First off, whenever somebody asks "would this work ?" the obvious response is "Have you tried it ?" :wink:

You were on the right track with the Offset method - you just needed to check the VBE Help files regarding its implementation. Its also worth noting that Excel works in (Row,Column) order with its various operations, so you needed to have the OS variable as the first argument (its also worth getting into the habit of declaring your row variables as Longs - Integers will not always be adequate).
Code: 
Sub Test()
    Dim OS As Long
    
    With Sheet1
        OS = .Range("A2").Value
        .Range("A1:T1").Copy
        Sheet2.Range("A1").Offset(OS, 0).PasteSpecial _
            Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    End With
    
End Sub
HTH
 
Upvote 0
i was writing the code as i was writing the query if you get my drift....

i tried it and it didn't work, but it's up and running now, thanks for your help. btw - what's a "Long" - i understand integer and string - but that's about it ....

cheers


trevor
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question
VBA to select partial filename and go back and forth between two files to copy paste
Replies
1
Views
166
shknbk2
S
P
  • Solved
Counter in For Each loop
Replies
2
Views
197
Possum17
P
A
  • Question
Macro to update list to last row on another sheet copy source from multiple sheets
Replies
0
Views
309
Azamamir
A
N
  • Question
My code is suddenly really slow
Replies
1
Views
135
RobP
R
C
  • Question
Calculations require application restart
Replies
2
Views
150
cpupartsplz
C

Forum statistics

Threads
1,214,988
Messages
6,122,620
Members
449,092
Latest member
amyap

Share this page

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
Back
Top