Pasting Cells Using an Offset Command based on Variable Cell Data

[SysEng]

Hey Everyone,

So heres my dilemma. Im trying to paste data into cells that are offset from specific data in my excel sheet.

The code ive got so far is:

Please Login or Register  to view this content.

What im aiming for is to search my Columns G and H that contain the values 1 & 1. Then from the cells that contain those variables, move 2 row up and 7 columns to the left and then paste on that active cell.

Im completely new to VBA so im sure there plenty wrong with this VBA script but so far ive managed to paste the data but it just pastes across the whole row instead of just the cells ive copied.. now i just get errors on the script.

Any suggestions?

Thanks

[alansidman]

Code Tags Added
Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between

Please Login or Register  to view this content.

tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found at http://www.excelforum.com/forum-rule...rum-rules.html



(Because you are new to the forum, I have added them for you today. Please take a few minutes to read all Forum Rules and comply in the future.)

[alansidman]

Do the values have to be in Columns G and H or only one of the columns on the same row to be copied.

It would be helpful to see an example (sample spreadsheet).

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[SysEng]

Hello Alan,

Apologies for not putting in the Code tags, ill do that from now on.

Ive attached the example sheet im using. It will have to be within columns G and H because im trying to seperate individual tables (Expansion Modules) so the modules ascend from module 1 - channel 1, Module 2 - Channel 1 and so on.
Sample Data.xlsx

[alansidman]

Using the information you provided in the first thread and your attached worksheet, you would only have one case of a 1 in both G and H. Row 21. If you are to copy that and paste it up two rows and to the left 7 columns, you would be off the spreadsheet as it is only 6 columns to the left to get to column A. Please clarify.

[SysEng]

Yes, there will be only one instance of 1 in both G and H, but what im ultimately going to do it modify the macro to then scan for a value of 2 in G and 1 in H and once again paste the title bars into the two line gap in between the modules. Ill attach the same sheet with the pasted section in.

I assumed that the macro would search for the 1 in G and H and then move from (in this case) H21 and move (-2,-7) to A19. However i cannot specify the actual cell in the macro because the data within the table will vary and may be another row in some instances. If however the cell the macro selects is infact column G, then yes the macro would need to only move (-2,-6). So long as the active cell to paste the A1:H2 Range is within the two row gap i have put in and pastes in column a that would be fine.